SQL Server Agent SQL Server Agent The SQL Server Agent is a separate process, which is tasked with running database jobs (including jobs setup from a database maintenance plan), raising alerts and notifying operators. Alerts Alerts can be created to flag the occurrence of an error, for example a database log becoming full. These can be raised on an individual error number or on a severity level (different types of errors have different severity levels, for example 017 includes all alerts for insufficient resources). As a minimum alerts should be created to monitor severity levels 019 – 025 for all databases. It is also advisable to setup an alert to monitor error number 9002 for all databases. It is also advisable to setup an alert to monitor severity level 017, which includes all alerts for insufficient resources. All alerts should be configured to notify the relevant operator by email, within their response section. Operators Jobs
Using Stored Procedures with a Command Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code. This helps guard against "SQL injection" attacks, in which an attacker inserts a command that compromises security on the server into an SQL statement. Parameterized commands can also improve query execution performance, because they help the database server accurately match the incoming command with a proper cached query plan. The syntax for parameter placeholders depends on the data source. The .NET Framework data provider type of a Parameter object is inferred from the .NET Framework type of the Value of the Parameter object, or from the DbType of the Parameter object. Parameters can also be derived from a stored procedure using the DbCommandBuilder class. CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5) SELECT * FROM Customers WHERE CustomerID = ?
SQL pie chart My other half says I'm losing it. But I think that as an enthusiast kernel developer she doesn't have the right to criticize people. ("I like user space better!" - she exclaims upon reading this). Shown below is a (single query) SQL-generated pie chart. Requirements We need a generic query, which returns at least these two columns: name_column and value_column. SELECT name AS name_column, value AS value_column FROM sample_values2; +-------------+--------------+ | name_column | value_column | +-------------+--------------+ | red | 1 | | blue | 2 | | orange | 3 | | white | 4 | +-------------+--------------+ Find sample data in pie_data.sql. Part 1: expanding the original query We're going to need to take the above query's results and expand them: how much is the ratio from total, per value? Next, we calculate ratio of accumulating value, and present it both in [0..1] range, as well as in [0..2*PI] (radians): The radians value will help us decide where in the pie chart lies each value. Showcase
Tek-Tips Welcome to SQLDBATIPS How to Insert Values into an Identity Column in SQL Server By Bill Graziano on 6 August 2007 | 6 Comments | Tags: Identity, INSERT Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. First we'll need a table to work with. use tempdb GO IF OBJECT_ID('IdentityTable') IS NOT NULL DROP TABLE IdentityTable GO CREATE TABLE IdentityTable ( TheIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY, TheValue NVARCHAR(20) NOT NULL ) GO Simply trying to INSERT a value into the identity column generates an error: INSERT IdentityTable(TheIdentity, TheValue) VALUES (1, 'First Row') GO Msg 544, Level 16, State 1, Line 3 Cannot insert explicit value for identity column in table 'IdentityTable' when IDENTITY_INSERT is set to OFF. The trick is to enable IDENTITY_INSERT for the table. SET IDENTITY_INSERT IdentityTable ON INSERT IdentityTable(TheIdentity, TheValue) VALUES (3, 'First Row') SET IDENTITY_INSERT IdentityTable OFF Here are some key points about IDENTITY_INSERT
Microsoft SQL Server - Using COALESCE to Build Comma-Delimited String By Garth Wells on 1 February 2001 | 27 Comments | Tags: Application Design Garth is back with another article. This one talks about building a comma-separated value string for use in HTML SELECT tags. I was reading the newsgroups a couple of days ago and came across a solution posted by Itzik Ben-Gan I thought was really smart. For those of you with limited experience working with HTML, I need to explain that the values selected in a multi-select list box are concatenated in a comma-delimited string. <SELECT name="RetailCategory" multiple><OPTION value=1>Shoes <OPTION value=2>Sporting Goods <OPTION value=3>Restaurant <OPTION value=4>Women's Clothes <OPTION value=5>Toys </SELECT> If a user selects more than one option the value associated with RetailCategory is a comma-delimited string. Now that we have covered the Add part, let's take a look at what happens when a user wants to Edit a row. I say final, because the actual HTML is built on-the-fly using VBScript. --Results--
SQL Server Journey with SQL Authority Best approach to remove time part of datetime in SQL Server ASP.NET.4GuysFromRolla.com: Summarizing Data with ROLLUP Published: Wednesday, July 30, 2003 By Dave Long Introduction Oftentimes, when building Web-based reporting tools we need to show totals and sub-totals for information stored in the database. A common way of providing such summarized data is the running total method. In this article, however, we will examine one alternative to the running total method: the WITH ROLLUP method. Understanding WITH ROLLUP The WITH ROLLUP syntax, as we'll see shortly, can only be used when a SQL query contains a GROUP BY clause. This would return results like: Category UnitPrice UnitsinStock Beverages 455.75 559 Condiments 276.75 507 Confections 327.08 386 These results show the sum of prices for each product and the sum of Units in Stock for each product that exists in the category the product belongs to. In examining the results of the above SQL query, your first instinct may be to say, "That's exactly what I need for displaying summarized data." The SQL Books Online describes the ROLLUP operator as follows:
Confusing High Availability with Disaster Preparedness When considering a proactive approach to disaster recovery, it’s only natural to look to high availability solutions as a way to cope with system or data-center outages. However, although high availability solutions are a fantastic complement to any disaster recovery plan, substituting high availability solutions for disaster recovery can be fatal. Related: Don't Confuse High Availability with Disaster Recovery Highly Available Bad Data High availability isn’t a replacement for a fully baked disaster recovery plan because high availability solutions just make data available—even if the data is disastrously bad. But imagine a scenario in which a software glitch requires a developer or DBA to manually set the inventory level of a certain, problematic product down to a quantity of zero. High availability systems only make your data available; they don’t control whether the data is correct. Non-Trivial Implementation High availability solutions impose an additional layer of complexity.
Caché - World's fastest high performance object database Unified Multi-Model Architecture Within Caché, data can be modeled and stored as tables, objects, or multidimensional arrays (hierarchies). Different models can seamlessly access data – without the need for performance-killing mapping between models. Built-in support for dynamic data objects (such as XML and JSON) enables easy interoperability and rapid development of Web applications. High-Performance Queries SQL is the lingua franca for querying Caché data across all data models. Distributed Systems InterSystems Enterprise Cache Protocol allows a network of many servers to behave as a single data store, dramatically enhancing the scalability and performance of distributed applications. High Availability through Database Mirroring Caché Database Mirrors do not require large investments in hardware, support, operating system licenses, or storage. Security Model Secure data-at-rest and data-in-motion while minimizing the burden on application performance. Application Development
Instant SQL Formatter General What's instant sql formatter? Instant SQL Formatter is a free online sql tidy tool, actually, it not only can beautify your sql but also can turn your formatted sql into html code, so you can post coloured sql code in your blog, forum,wiki and any website easily. How does this sql beautifier work? Before the SQL is reformatted, the SQL formatter first checks the syntax of the input SQL, then creates a parse tree for each SQL. What can I do when my SQL is not formatted? 1. 2. 3. 4. Does this formatter support unicode? No. Why copy to clipboard button doesn't work in firefox? Mozilla based browsers such as firefox will ask the user for permission before allowing instant sql formatter to access the clipboard, so you need to enable javascript copy to clipboard function like this: In the address bar, type "about:config" and press Enter. May I setup this sql formatter on my site? Yes. I don't want to post my sql on internet, how do I tidy my sql? Output of SQL formatter Html output XML output