SQLExamples Wiki Rss Feed

UPDATED WIKI: WikiGuidelines

ArnieRowland — Wed, 24 Jun 2009 17:42:45 GMT

MSDN Support Wiki Contributor Guidelines

As much as possible, Wiki pages should demonstrate Best Practices in Problem and Objective definition, Naming conventions, Formatting, and Descriptive Comments, and always teach by example. Recognizing that we are all busy and sometimes in our haste we make mistakes and even overlook the obvious, please freely edit and ‘clean up’ anything that that ‘just doesn’t look right.
Any Submission may be altered, edited, or even removed by any Contibutor. If there is substaintial change, the Original Contributor should be consulted, if possible. We all accept that someone may alter our contribution -it is a contributory Wiki, after all.
All WIKI Page Submissions will have at the bottom, the Original Contributor's Name and Creation Date. Anyone adding significant material to an existing page may add their name adjacent to the new material added.
NO Copyrighted Material should be posted without consent and attribution, including, if appropriate, a link to the Original Source. A Contributor may freely post his/her own material, and by so doing provides the material under the provisions of the Microsoft Public License (see [License] tab Above). It is ‘best’ to not duplicate material from other sources –but to describe that source and provide a link. For example, a BOL excerpt may change, and if so, then the excerpt would be out of sync. Exceptions ‘might’ include practices that are long standing and/or ANSI standards. We don't want to create a ‘project’ that will require a lot of ongoing maintenance.
It is agreed that this Wiki is NOT to be a 'portal' to send Viewers to Advertiser driven sites. Links to External Sites that require User information, including login accounts, should be carefully considered for the 'value' of the material, as well as to Source's Privacy and Advertising Policies.
Suggested Solutions would be in desending SQL Server Version order; SQL 2008, SQL 2005, SQL 2000 -if the version is applicable to a Solution.
Wiki pages will not be published in a incomplete state. If a page is incomplete and still in developemnt, it should be kept from Public view until complete.

How to Create a Wiki Page
Wiki Page Template
Guide for Wiki Mark-up

Test Environment -A location for Testing

Topic Suggestions (and Reservations)

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 13, 2008

UPDATED WIKI: WikiGuidelines

ArnieRowland — Wed, 24 Jun 2009 16:42:37 GMT

MSDN Support Wiki Contributor Guidelines

As much as possible, Wiki pages should demonstrate Best Practices in Problem and Objective definition, Naming conventions, Formatting, and Descriptive Comments, and always teach by example. Recognizing that we are all busy and sometimes in our haste we make mistakes and even overlook the obvious, please freely edit and ‘clean up’ anything that that ‘just doesn’t look right.
Any Submission may be altered, edited, or even removed by any Contibutor. If there is substaintial change, the Original Contributor should be consulted, if possible. We all accept that someone may alter our contribution -it is a contributory Wiki, after all.
All WIKI Page Submissions will have at the bottom, the Original Contributor's Name and Creation Date. Anyone adding significant material to an existing page may add their name adjacent to the new material added.
NO Copyrighted Material should be posted without consent and attribution, including, if appropriate, a link to the Original Source. A Contributor may freely post his/her own material, and by so doing provides the material under the provisions of the Microsoft Public License (see [License] tab Above). It is ‘best’ to not duplicate material from other sources –but to describe that source and provide a link. For example, a BOL excerpt may change, and if so, then the excerpt would be out of sync. Exceptions ‘might’ include practices that are long standing and/or ANSI standards. We don't want to create a ‘project’ that will require a lot of ongoing maintenance.
It is agreed that this Wiki is NOT to be a 'portal' to send Viewers to Advertiser driven sites. Links to External Sites that require User information, including login accounts, should be carefully considered for the 'value' of the material, as well as to Source's Privacy and Advertising Policies.
Suggested Solutions would be in desending SQL Server Version order; SQL 2008, SQL 2005, SQL 2000 -if the version is applicable to a Solution.
Wiki pages will not be published in a incomplete state. If a page is incomplete and still in developemnt, it should be kept from Public view until complete.

How to Create a Wiki Page
Wiki Page Template
Guide for Wiki Mark-up

Test Environment -A location for Testing

Topic Suggestions (and Reservations)

MSDN Forum Moderators Guide:Microsoft Forums Moderation Guide.aspx
___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 13, 2008

UPDATED WIKI: LastRanDBCCCHECKDBForAllDatabases

SankarReddy — Fri, 19 Jun 2009 05:49:38 GMT

CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS';
 
;WITH CHECKDB1 AS
(
    SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN ('dbi_dbname'))
    ,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN ('dbi_dbccLastKnownGood')
)      
SELECT CHECKDB1.Value AS DatabaseName
        , CHECKDB2.Value AS LastRanDBCCCHECKDB
FROM CHECKDB1 JOIN CHECKDB2
ON rn1 =rn2
 
DROP TABLE #temp

Acknowledgements: Ward Pond, Paul Randal, Jonathan Kehayias, Aaron Alton.
http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-When-did-DBCC-CHECKDB-last-run-successfully.aspx

UPDATED WIKI: LastRanDBCCCHECKDBForAllDatabases

SankarReddy — Tue, 16 Jun 2009 07:04:58 GMT

CREATE TABLE #temp (
       Id INT IDENTITY(1,1), 
       ParentObject VARCHAR(255),
       [Object] VARCHAR(255),
       Field VARCHAR(255),
       [Value] VARCHAR(255)
)
 
INSERT INTO #temp
EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS';
 
;WITH CHECKDB1 AS
(
    SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN ('dbi_dbname'))
    ,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN ('dbi_dbccLastKnownGood')
)      
SELECT CHECKDB1.Value AS DatabaseName
        , CHECKDB2.Value AS LastRanDBCCCHECKDB
FROM CHECKDB1 JOIN CHECKDB2
ON rn1 =rn2
 
DROP TABLE #temp

Acknowledgements: Ward Pond, Paul Randal, Jonathan Kehayias, Aaron Alton.
http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-When-did-DBCC-CHECKDB-last-run-successfully.aspx

UPDATED WIKI: Home

SankarReddy — Tue, 16 Jun 2009 06:59:27 GMT

Common Solutions for T-SQL Problems

Information presented in this WIKI represents the suggestions, ideas, and opinions of Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums. (Unless specifically stated otherwise, nothing should be construed to represent the official positions or opinions of Microsoft and/or its Employees.)

Please direct any additional Questions to the MSDN SQL Server Related Forums
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
How can I Prepare My Question to Increase the Possibility of Getting a Good Solution?
What are the Rules of Conduct expected on the Forums?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

How To Examples of Selected T-SQL Programming Constructs

How To Troubleshoot and Debug T-SQL Code

Troubleshoot Deadlocking in SQL Server.
Troubleshoot Deadlocking in SQL Server 2005 using Profiler.
Understanding the Anatomy of a Deadlock.
Using the SQL Server Profiler.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Writing Transact-SQL Statements Tutorial

http://msdn2.microsoft.com/en-us/library/ms365303.aspx
___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 12, 2008

UPDATED WIKI: LastRanDBCCCHECKDBForAllDatabases

SankarReddy — Tue, 16 Jun 2009 06:55:05 GMT

At the time of this writing, SQL Server doesn't store the last ran DBCC CHECKDB in the catalog views and there are 2 places from where this information can be pulled out. One is in the SQL Server error logs and the other is the boot page. Paul Randal showed in his blog how to pull this information by looking at the boot page for a given database and this script allows you to see the last ran clean DBCC CHECKDB information in all databases on an instance.

CREATE TABLE #temp (
Id INT IDENTITY(1,1),
ParentObject VARCHAR(255),
Object VARCHAR(255),
Field VARCHAR(255),
Value VARCHAR(255)
)

INSERT INTO #temp
EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS';

;WITH CHECKDB1 AS
(
SELECT Value,ROWNUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN ('dbidbname'))
,CHECKDB2 AS ( SELECT Value, ROWNUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN ('dbidbccLastKnownGood')
)
SELECT CHECKDB1.Value AS DatabaseName
, CHECKDB2.Value AS LastRanDBCCCHECKDB
FROM CHECKDB1 JOIN CHECKDB2
ON rn1 =rn2

DROP TABLE #temp

Acknowledgements: Ward Pond, Paul Randal, Jonathan Kehayias, Aaron Alton.
http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-When-did-DBCC-CHECKDB-last-run-successfully.aspx

UPDATED WIKI: LastRanDBCCCHECKDBForAllDatabases

SankarReddy — Tue, 16 Jun 2009 06:49:54 GMT

UPDATED WIKI: LastRanDBCCCHECKDBForAllDatabases

SankarReddy — Tue, 16 Jun 2009 06:47:33 GMT

UPDATED WIKI: Home

AaronAlton — Wed, 10 Dec 2008 04:58:28 GMT

Common Solutions for T-SQL Problems

Information presented in this WIKI represents the suggestions, ideas, and opinions of Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums. (Unless specifically stated otherwise, nothing should be construed to represent the official positions or opinions of Microsoft and/or its Employees.)

How To Examples of Selected T-SQL Programming Constructs

How To Troubleshoot and Debug T-SQL Code

Writing Transact-SQL Statements Tutorial

UPDATED WIKI: AlternativesToSQLCursors

AaronAlton — Tue, 09 Dec 2008 08:14:16 GMT

Alternatives To SQL Server Cursors

Aaron Alton, December 2008

Introduction

Anyone with more than a little exposure to SQL Server will soon come to the conclusion that the use of cursors is best avoided. SQL Server is a Relational Database Management System, and as such is very proficient at processing sets of data. The use of a cursor effectively repeats an operation once for each row being processed, thereby consuming far more server resources than would be necessitated by a set-based operation. In addition, since SQL cursors are processed much more slowly than set-based operations, any locks generated by the use of said cursor are held longer than would otherwise be necessary. In this way, the use of a cursor can affect not only the performance of a given batch of work, but also the performance of any other batches which need to make use of the same data resources.

In order to effectively avoid using a SQL cursor, one must know why a cursor is being used in the first place. The list below details the most common reasons why people use cursors, and some possible solutions for each problem.

Test Data
Problem: The Developer or Designer is new to SQL Server
Problem: Need to update a table with incrementing column values for records already in the table
Problem: Need to use a custom identity function to perform an insert
Problem: Need to compare a row to adjacent rows in a table

Test Data:

The following test data is used for each of the resolutions below.

USE AdventureWorks;
 
--Set up test data
IF (SELECT OBJECT_ID('HumanResources.EmployeeLogins')) IS NOT NULL
BEGIN
	DROP TABLE HumanResources.EmployeeLogins
END
 
CREATE TABLE HumanResources.EmployeeLogins 
	(RowKey		int NULL,
	LoginID		nvarchar(256) NOT NULL);
 
INSERT HumanResources.EmployeeLogins (LoginID)
SELECT LoginID FROM HumanResources.Employee
 
INSERT HumanResources.EmployeeLogins (LoginID)
SELECT 'Skunk-Works' + RIGHT(LoginID, LEN(LoginID) - CHARINDEX('\', LoginID) + 1)
FROM   HumanResources.Employee

This test data is somewhat contrived, however it is an accessible example for anyone who has AdventureWorks installed.

Problem: The Developer or Designer is New to SQL Server

By far, this is the most common reason for cursor usage. If a programmer is already well versed in an event-driven programming language such as Visual Basic, they will possess a great wealth of experience in dealing with data items one at a time. As such, their natural inclination when moving into SQL is to do the same. Unfortunately, what is appropriate in one language may be (and in this case is) entirely inappropriate in another.

Resolution

The primary weapon against cursor usage in this scenario is to develop a strong understanding of the basics of Transact-SQL. There are countless courses, books, and websites dedicated to the topic. In addition, you can review the examples below, which present set-based solutions to problems which may otherwise necessitate a cursor.
If you’re still struggling with finding a set-based solution, post a question in the MSDN Transact-SQL Forum. Before doing so, review Arnie Rowland’s article on how to prepare your question to increase the possibility of eliciting a suitable solution.

Problem: Need to update a table with incrementing column values for records already in the table

This problem is most often encountered when importing data from an external data source. The external data source may not have a key field, for instance, and for one of various reasons, an identity column cannot be used while importing.

Resolution

A number of methods may be employed to resolve this issue. The most efficient and straightforward method, available in SQL 2005 and greater, is to use the ROW_NUMBER() function.

Using the AdventureWorks database:

--Use ROW_NUMBER() to establish a LoginKey for each row
SELECT	LoginID, ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
FROM		HumanResources.EmployeeLogins
Once we have the ROW_NUMBER() query, we can use it in a Common Table Expression in order to update the RowKey in our HumanResources.EmployeeLogins table:
;WITH	RowKeys (LoginID, RowKey) AS 
		(SELECT	LoginID, 
				ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
		FROM	HumanResources.EmployeeLogins)
UPDATE	HumanResources.EmployeeLogins
SET		RowKey = rk.RowKey
FROM		HumanResources.EmployeeLogins el
JOIN		RowKeys rk ON el.LoginID = rk.LoginID

ROW_NUMBER() can also be used to number rows based upon logical groupings, or partitions in data, although this is less applicable to cursor replacement. For instance, the test data contains logins from multiple domains, and you may want to number the rows in the table based upon their position within a grouping of rows from the same domain.

--Use ROW_NUMBER in conjunction with PARTITION to create row numbers grouped by domain
SELECT	LoginID, 
		ROW_NUMBER() OVER 
			(PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1) 
			ORDER BY LoginID ASC) AS RowKey
FROM		HumanResources.EmployeeLogins

Also worth noting is that the sort order of the OVER clause does not have to match the sort order of the outermost query.
In the event that the numbering has to start at a number other than zero, that number can be added to ROW_NUMBER() in order to obtain the desired row numbers.
For instance:

SELECT	LoginID, 
		100 + ROW_NUMBER() OVER 
			(PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1) 
			ORDER BY LoginID ASC) AS RowKey
FROM		HumanResources.EmployeeLogins

The same function can be performed, albeit far less efficiently, in SQL 2000. The following solution uses an inline subquery in order to COUNT the number of rows on or before a given LoginID:

SELECT	el1.LoginID,
		(SELECT COUNT(LoginID)
		FROM	HumanResources.EmployeeLogins el2
		WHERE	el2.LoginID <= el1.LoginID) AS RowKey
FROM	HumanResources.EmployeeLogins el1

This solution is subject to error if duplicate values are present in the columns being used for comparison. If there were two identical LoginIDs, they would both be granted the same RowKey. When dealing with duplicate fields such as this, a local temporary table can be used in order to ensure unique row numbering:

CREATE TABLE #EmployeeLoginsTemp
	(RowKey int IDENTITY(1,1) NOT NULL,
	LoginID nvarchar(256))
 
INSERT #EmployeeLoginsTemp (LoginID)
SELECT LoginID FROM HumanResources.EmployeeLogins
 
SELECT * FROM #EmployeeLoginsTemp

Problem: Need to use a custom identity function to perform an insert

Many databases employ custom identity generation functions. There are VERY FEW good reasons for using custom identity functions in SQL Server, given the presence of IDENTITY columns and the UniqueIdentifier data type. That is not to say that you will never encounter such a situation - just that all possible alternatives should be vetted prior to deciding upon a custom identity solution.
A custom identity function commonly resembles the following:

CREATE TABLE Identities (TableName sysname PRIMARY KEY CLUSTERED,
						NextKey int NOT NULL)
 
CREATE PROC GetNextKey(	@TableName sysname,
						@NextKey int OUTPUT)
AS
BEGIN
	--Fetch the NextID, if there is a row for the passed-in TableName in the Identities.
	SELECT	@NextKey = NextKey
	FROM	Identities
	WHERE	TableName = @TableName
 
	--If there wasn't a row, @NextID will be NULL.
	IF @NextKey IS NULL
	BEGIN
		INSERT Identities (TableName, NextKey)
		SELECT @TableName, 1
 
		SELECT	@NextKey = NextKey
		FROM	Identities
		WHERE	TableName = @TableName
	END
 
	--Advance the NextID value in Identities
	UPDATE	Identities
	SET		NextKey = NextKey + 1
	WHERE	TableName = @TableName
END

Resolution

The biggest problem with custom identity functions is that they serialize inserts. While the following solution (for SQL 2000 and greater) does not use a cursor, it does use a WHILE loop to call the GetNextKey proc once for each row in HumanResources.EmployeeLogins:

WHILE EXISTS (SELECT 1 FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
BEGIN
	DECLARE @NextKey int
	EXEC dbo.GetNextKey 'HumanResources.EmployeeLogins', @NextKey OUTPUT
 
	UPDATE	HumanResources.EmployeeLogins
	SET		RowKey = @NextKey
	WHERE	LoginID = (SELECT TOP 1 LoginID FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
END

Problem: Need to compare a row to adjacent rows in a table

A typical example of this type of problem is “I need to know by how much the latest price increase changed the price of each item”. The programmer often resorts to a cursor because they cannot understand how to compare each row with the previous row for the same item, where the previous row’s price was not equal to the current row’s price.

Resolution

In this situation, an inline query can be very useful. The following solution works for SQL 2000 and higher, and uses a different AdventureWorks table for illustration. In AdventureWorks, the Production.ProductListPriceHistory table stores pricing history for each item. The current row is identified by a NULL EndDate, so we must compare the current row to the most recent row for the same item.

SELECT	plph1.ProductID,  plph1.ListPrice - 
		COALESCE((SELECT		TOP 1 ListPrice
				FROM		Production.ProductListPriceHistory plph2
				WHERE		EndDate IS NOT NULL
				AND			plph2.ProductID = plph1.ProductID
				ORDER BY	EndDate DESC), 
				plph1.ListPrice) AS LastPriceChangeAmount
FROM	Production.ProductListPriceHistory plph1
WHERE	plph1.EndDate IS NULL

Conclusion

This article has demonstrated a number of alternatives to the use of cursors in Transact-SQL. Please feel free to post any comments, questions, or suggestions regarding this article below. For further help avoiding SQL cursors, post a question in the MSDN Transact-SQL Forum. However, please read Arnie Rowland’s article on how to prepare your question to increase the possibility of eliciting a suitable solution prior to doing so.

Return to Top
___________________________________________________________________________________________________________________
Page Created By: Aaron Alton, December 9, 2008

UPDATED WIKI: Home

AaronAlton — Tue, 09 Dec 2008 08:12:53 GMT

Common Solutions for T-SQL Problems

Information presented in this WIKI represents the suggestions, ideas, and opinions of Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums. (Unless specifically stated otherwise, nothing should be construed to represent the official positions or opinions of Microsoft and/or its Employees.)

How To Examples of Selected T-SQL Programming Constructs

How To Troubleshoot and Debug T-SQL Code

Writing Transact-SQL Statements Tutorial

UPDATED WIKI: AlternativesToSQLCursors

AaronAlton — Tue, 09 Dec 2008 08:11:56 GMT

Alternatives To SQL Server Cursors

Aaron Alton, December 2008

Introduction

Test Data:

The following test data is used for each of the resolutions below.

USE AdventureWorks;
 
--Set up test data
IF (SELECT OBJECT_ID('HumanResources.EmployeeLogins')) IS NOT NULL
BEGIN
	DROP TABLE HumanResources.EmployeeLogins
END
 
CREATE TABLE HumanResources.EmployeeLogins 
	(RowKey		int NULL,
	LoginID		nvarchar(256) NOT NULL);
 
INSERT HumanResources.EmployeeLogins (LoginID)
SELECT LoginID FROM HumanResources.Employee
 
INSERT HumanResources.EmployeeLogins (LoginID)
SELECT 'Skunk-Works' + RIGHT(LoginID, LEN(LoginID) - CHARINDEX('\', LoginID) + 1)
FROM   HumanResources.Employee

This test data is somewhat contrived, however it is an accessible example for anyone who has AdventureWorks installed.

Problem: The Developer or Designer is New to SQL Server

Resolution

Problem: Need to update a table with incrementing column values for records already in the table

Resolution

--Use ROW_NUMBER() to establish a LoginKey for each row
SELECT	LoginID, ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
FROM		HumanResources.EmployeeLogins
Once we have the ROW_NUMBER() query, we can use it in a Common Table Expression in order to update the RowKey in our HumanResources.EmployeeLogins table:
;WITH	RowKeys (LoginID, RowKey) AS 
		(SELECT	LoginID, 
				ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
		FROM	HumanResources.EmployeeLogins)
UPDATE	HumanResources.EmployeeLogins
SET		RowKey = rk.RowKey
FROM		HumanResources.EmployeeLogins el
JOIN		RowKeys rk ON el.LoginID = rk.LoginID

--Use ROW_NUMBER in conjunction with PARTITION to create row numbers grouped by domain
SELECT	LoginID, 
		ROW_NUMBER() OVER 
			(PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1) 
			ORDER BY LoginID ASC) AS RowKey
FROM		HumanResources.EmployeeLogins

SELECT	LoginID, 
		100 + ROW_NUMBER() OVER 
			(PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1) 
			ORDER BY LoginID ASC) AS RowKey
FROM		HumanResources.EmployeeLogins

The same function can be performed, albeit far less efficiently, in SQL 2000. The following solution uses an inline subquery in order to COUNT the number of rows on or before a given LoginID:

SELECT	el1.LoginID,
		(SELECT COUNT(LoginID)
		FROM	HumanResources.EmployeeLogins el2
		WHERE	el2.LoginID <= el1.LoginID) AS RowKey
FROM	HumanResources.EmployeeLogins el1

CREATE TABLE #EmployeeLoginsTemp
	(RowKey int IDENTITY(1,1) NOT NULL,
	LoginID nvarchar(256))
 
INSERT #EmployeeLoginsTemp (LoginID)
SELECT LoginID FROM HumanResources.EmployeeLogins
 
SELECT * FROM #EmployeeLoginsTemp

Problem: Need to use a custom identity function to perform an insert

CREATE TABLE Identities (TableName sysname PRIMARY KEY CLUSTERED,
						NextKey int NOT NULL)
 
CREATE PROC GetNextKey(	@TableName sysname,
						@NextKey int OUTPUT)
AS
BEGIN
	--Fetch the NextID, if there is a row for the passed-in TableName in the Identities.
	SELECT	@NextKey = NextKey
	FROM	Identities
	WHERE	TableName = @TableName
 
	--If there wasn't a row, @NextID will be NULL.
	IF @NextKey IS NULL
	BEGIN
		INSERT Identities (TableName, NextKey)
		SELECT @TableName, 1
 
		SELECT	@NextKey = NextKey
		FROM	Identities
		WHERE	TableName = @TableName
	END
 
	--Advance the NextID value in Identities
	UPDATE	Identities
	SET		NextKey = NextKey + 1
	WHERE	TableName = @TableName
END

Resolution

WHILE EXISTS (SELECT 1 FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
BEGIN
	DECLARE @NextKey int
	EXEC dbo.GetNextKey 'HumanResources.EmployeeLogins', @NextKey OUTPUT
 
	UPDATE	HumanResources.EmployeeLogins
	SET		RowKey = @NextKey
	WHERE	LoginID = (SELECT TOP 1 LoginID FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
END

Problem: Need to compare a row to adjacent rows in a table

Resolution

SELECT	plph1.ProductID,  plph1.ListPrice - 
		COALESCE((SELECT		TOP 1 ListPrice
				FROM		Production.ProductListPriceHistory plph2
				WHERE		EndDate IS NOT NULL
				AND			plph2.ProductID = plph1.ProductID
				ORDER BY	EndDate DESC), 
				plph1.ListPrice) AS LastPriceChangeAmount
FROM	Production.ProductListPriceHistory plph1
WHERE	plph1.EndDate IS NULL

Conclusion

UPDATED WIKI: AlternativesToSQLCursors

AaronAlton — Tue, 09 Dec 2008 01:00:30 GMT

Alternatives To SQL Server Cursors
Anyone with more than a little exposure to SQL Server will soon come to the conclusion that the use of cursors is best avoided. SQL Server is a Relational Database Management System, and as such is very proficient at processing sets of data. The use of a cursor effectively repeats an operation once for each row being processed, thereby consuming far more server resources than would be necessitated by a set-based operation. In addition, since SQL cursors are processed much more slowly than set-based operations, any locks generated by the use of said cursor are held longer than would otherwise be necessary. In this way, the use of a cursor can affect not only the performance of a given batch of work, but also the performance of any other batches which need to make use of the same data resources.

In order to effectively avoid using a SQL cursor, one must first know why a cursor is being used in the first place. The list below details the most common reasons why people use cursors, and some possible solutions for each problem.

Test Data:
The following test data is used for each of the resolutions below.
USE AdventureWorks;

--Set up test data
IF (SELECT OBJECT_ID('HumanResources.EmployeeLogins')) IS NOT NULL
BEGIN
DROP TABLE HumanResources.EmployeeLogins
END

CREATE TABLE HumanResources.EmployeeLogins
(RowKey int NULL,
LoginID nvarchar(256) NOT NULL);

INSERT HumanResources.EmployeeLogins (LoginID)
SELECT LoginID FROM HumanResources.Employee

INSERT HumanResources.EmployeeLogins (LoginID)
SELECT 'Skunk-Works' + RIGHT(LoginID, LEN(LoginID) - CHARINDEX('\', LoginID) + 1)
FROM HumanResources.Employee
This test data is somewhat contrived, however it is an accessible example for anyone who has AdventureWorks installed.

Problem: The Developer or Designer is New to SQL Server
By far, this is the most common reason for cursor usage. If a programmer is already well versed in an event-driven programming language such as Visual Basic, they will possess a great wealth of experience in dealing with data items one at a time. As such, their natural inclination when moving into SQL is to do the same. Unfortunately, what is appropriate in one language may be (and in this case is) entirely inappropriate in another.
Resolution:
The primary weapon against cursor usage in this scenario is to develop a strong understanding of the basics of Transact-SQL. There are countless courses, books, and websites dedicated to the topic. In addition, you can review the examples below, which present set-based solutions to problems which may otherwise necessitate a cursor.
If you’re still struggling with finding a set-based solution, post a question in the MSDN Transact-SQL Forum. Before doing so, review Arnie Rowland’s article on how to prepare your question to increase the possibility of eliciting a suitable solution.

Problem: Need to update a table with incrementing column values for records already in the table
Resolution: This problem is most often encountered when importing data from an external data source. The external data source may not have a key field, for instance, and for one of various reasons, an identity column cannot be used while importing.
A number of methods may be employed to resolve this issue, however the most efficient and straightforward method, available in SQL 2005 and greater, is to use the ROW_NUMBER() function. Using the AdventureWorks database:
--Use ROW_NUMBER() to establish a LoginKey for each row
SELECT LoginID, ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
FROM HumanResources.EmployeeLogins
Once we have the ROW_NUMBER() query, we can use it in a Common Table Expression in order to update the RowKey in our HumanResources.EmployeeLogins table:
;WITH RowKeys (LoginID, RowKey) AS
(SELECT LoginID,
ROW_NUMBER() OVER (ORDER BY LoginID ASC) AS RowKey
FROM HumanResources.EmployeeLogins)
UPDATE HumanResources.EmployeeLogins
SET RowKey = rk.RowKey
FROM HumanResources.EmployeeLogins el
JOIN RowKeys rk ON el.LoginID = rk.LoginID
ROW_NUMBER() can also be used to number rows based upon logical groupings, or partitions in data, although this is less applicable to cursor replacement. For instance, the test data contains logins from multiple domains, and you may want to number the rows in the table based upon their position within a grouping of rows from the same domain.
--Use ROW_NUMBER in conjunction with PARTITION to create row numbers grouped by domain
SELECT LoginID,
ROW_NUMBER() OVER
(PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1)
ORDER BY LoginID ASC) AS RowKey
FROM HumanResources.EmployeeLogins
Also worth noting is that the sort order of the OVER clause does not have to match the sort order of the outermost query.
In the event that the numbering has to start at a number other than zero, that number can be added to ROW_NUMBER() in order to obtain the desired row numbers. For instance:
SELECT LoginID,
100 + ROW_NUMBER() OVER
(PARTITION BY LEFT(LoginID, CHARINDEX('\', LoginID) - 1)
ORDER BY LoginID ASC) AS RowKey
FROM HumanResources.EmployeeLogins
The same function can be performed, albeit far less efficiently, in SQL 2000. The following solution uses an inline subquery in order to COUNT the number of rows on or before a given LoginID:
SELECT el1.LoginID,
(SELECT COUNT(LoginID)
FROM HumanResources.EmployeeLogins el2
WHERE el2.LoginID <= el1.LoginID) AS RowKey
FROM HumanResources.EmployeeLogins el1
This solution is subject to error if duplicate values are present in the columns being used for comparison. If there were two identical LoginIDs, they would both be granted the same RowKey. When dealing with duplicate fields such as this, a local temporary table can be used in order to ensure unique row numbering:
CREATE TABLE #EmployeeLoginsTemp
(RowKey int IDENTITY(1,1) NOT NULL,
LoginID nvarchar(256))

INSERT #EmployeeLoginsTemp (LoginID)
SELECT LoginID FROM HumanResources.EmployeeLogins

SELECT * FROM #EmployeeLoginsTemp

Problem: Need to use a custom identity function to perform an insert
Resolution: Many databases employee custom identity generation functions. There are VERY FEW good reasons for using custom identity functions in SQL Server, given the presence of IDENTITY columns and the UniqueIdentifier data type. That is not to say that you will never encounter such a situation - just that all possible alternatives should be vetted prior to deciding upon a custom identity solution.
A custom identity function commonly resembles the following:
CREATE TABLE Identities (TableName sysname PRIMARY KEY CLUSTERED,
NextKey int NOT NULL)

CREATE PROC GetNextKey( @TableName sysname,
@NextKey int OUTPUT)
AS
BEGIN
--Fetch the NextID, if there is a row for the passed-in TableName in the Identities.
SELECT @NextKey = NextKey
FROM Identities
WHERE TableName = @TableName

--If there wasn't a row, @NextID will be NULL.
IF @NextKey IS NULL
BEGIN
INSERT Identities (TableName, NextKey)
SELECT @TableName, 1

SELECT @NextKey = NextKey
FROM Identities
WHERE TableName = @TableName
END

--Advance the NextID value in Identities
UPDATE Identities
SET NextKey = NextKey + 1
WHERE TableName = @TableName
END
The biggest problem with custom identity functions is that they serialize inserts. While the following solution (for SQL 2000 and greater) does not use a cursor, it does use a WHILE loop to call the GetNextKey proc once for each row in HumanResources.EmployeeLogins:
WHILE EXISTS (SELECT 1 FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
BEGIN
DECLARE @NextKey int
EXEC dbo.GetNextKey 'HumanResources.EmployeeLogins', @NextKey OUTPUT

UPDATE HumanResources.EmployeeLogins
SET RowKey = @NextKey
WHERE LoginID = (SELECT TOP 1 LoginID FROM HumanResources.EmployeeLogins WHERE RowKey IS NULL)
END

Problem: Need to compare a row to adjacent rows in a table
A typical example of this type of problem is “I need to know by how much the latest price increase changed the price of each item”. The programmer often resorts to a cursor because they cannot understand how to compare each row with the previous row for the same item, where the previous row’s price was not equal to the current row’s price.
In this situation, an inline query can be very useful. The following solution works for SQL 2000 and higher, and uses a different AdventureWorks table for illustration. In AdventureWorks, the Production.ProductListPriceHistory table stores pricing history for each item. The current row is identified by a NULL EndDate, so we must compare the current row to the most recent row for the same item.
SELECT plph1.ProductID, plph1.ListPrice -
COALESCE((SELECT TOP 1 ListPrice
FROM Production.ProductListPriceHistory plph2
WHERE EndDate IS NOT NULL
AND plph2.ProductID = plph1.ProductID
ORDER BY EndDate DESC),
plph1.ListPrice) AS LastPriceChangeAmount
FROM Production.ProductListPriceHistory plph1
WHERE plph1.EndDate IS NULL

Conclusion
This article has demonstrated a number of alternatives to the use of cursors in Transact-SQL. Please feel free to post any comments, questions, or suggestions regarding this article below. For help avoiding SQL cursors, please read Arnie Rowland’s article on how to prepare your question to increase the possibility of eliciting a suitable solution, and then post a question in the MSDN T-SQL Forums.

UPDATED WIKI: TestPage

AaronAlton — Tue, 09 Dec 2008 00:55:25 GMT

Test Environment

You may create links here to your new Wiki page in order to work on it and keep it out of Public view until you are ready to make it 'public'.
Then when you are ready to make your page 'Public', note the Wiki PageName, and use it for the Wiki link on the referring page.
After your page is complete, and you have 'published' it, you may wish to remove the link from this page.

WikiLink: [Words Seen by User|YourWikiPage]
___________________________________________________________________________________________________________________

Transfer Jobs and Logins using SSIS - Deepak

Upgrading to SQL Server 2005 - Deepak

Log file growth in SQL Server - Deepak

Alternatives to SQL Cursors - Aaron

Detaching A Database with SSMS - Video Tutorial

Find out all the primary key and foreign key constraints in a table - Suprotim Agarwal

Testing out analytics - Suprotim

SQL Server Best Practices - Development - Madhu

This concept 'could' work -requires a counter.cgi hosted somewhere...
(A bit tacky perhaps...)

Launch in another window

Contributors

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 16, 2008

UPDATED WIKI: Home

JonathanKehayias — Mon, 01 Dec 2008 21:56:06 GMT

Common Solutions for T-SQL Problems

Information presented in this WIKI represents the suggestions, ideas, and opinions of Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums. (Unless specifically stated otherwise, nothing should be construed to represent the official positions or opinions of Microsoft and/or its Employees.)

How To Examples of Selected T-SQL Programming Constructs

How To Troubleshoot and Debug T-SQL Code

Writing Transact-SQL Statements Tutorial

UPDATED WIKI: Home

JonathanKehayias — Mon, 01 Dec 2008 21:51:18 GMT

Common Solutions for T-SQL Problems

Information presented in this WIKI represents the suggestions, ideas, and opinions of Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums. (Unless specifically stated otherwise, nothing should be construed to represent the official positions or opinions of Microsoft and/or its Employees.)

How To Examples of Selected T-SQL Programming Constructs

How To Troubleshoot and Debug T-SQL Code

Writing Transact-SQL Statements Tutorial

UPDATED WIKI: TestPage

JonathanKehayias — Mon, 01 Dec 2008 21:50:43 GMT

Test Environment

You may create links here to your new Wiki page in order to work on it and keep it out of Public view until you are ready to make it 'public'.
Then when you are ready to make your page 'Public', note the Wiki PageName, and use it for the Wiki link on the referring page.
After your page is complete, and you have 'published' it, you may wish to remove the link from this page.

WikiLink: [Words Seen by User|YourWikiPage]
___________________________________________________________________________________________________________________

Transfer Jobs and Logins using SSIS - Deepak

Upgrading to SQL Server 2005 - Deepak

Log file growth in SQL Server - Deepak

Detaching A Database with SSMS - Video Tutorial

Find out all the primary key and foreign key constraints in a table - Suprotim Agarwal

Testing out analytics - Suprotim

SQL Server Best Practices - Development - Madhu

This concept 'could' work -requires a counter.cgi hosted somewhere...
(A bit tacky perhaps...)

Launch in another window

Contributors

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 16, 2008

UPDATED WIKI: AutomatedDBA

JonathanKehayias — Mon, 01 Dec 2008 21:49:54 GMT

How to Automate Common DBA Tasks

Jonathan Kehayias, Oct 21, 2008

Database Administrators often spend hours each day doing the same repetitive tasks; checking Error Logs, Backups, Drive Space, Agent History, Job Logs, and other common “checklist” items. It is fairly easy to automate these common tasks in SQL Server 2000, 2005 and 2008 using the tools that are already available in the Operating System and SQL Server. To do this, a combination of TSQL, VBScript, WMI, and the SQL Server Agent and Windows Task Scheduler can be used. This article will break the various items to monitor up into sub-articles to keep topics focused and separate. The sub-articles will be in two classifications:

TSQL monitoring through SQL Agent
VBScript/WMI monitoring through Windows Task Scheduler

TSQL monitoring through SQL Agent

Since DBA's monitor Database Servers, it is only natural that a majority of the monitoring that they do utilizes TSQL. Since this is the case, the natural way to automate these tasks is with the use of the SQL Server Agent. As a part of automation, the first item that will need to be setup is a method of providing notifications to the appropriate parties when an exception based event is found during the automated monitoring. SQL Server 2000 is somewhat limited in its native notification abilities. For security purposes the examples provided will not use SendMail which is native to SQL Server 2000, but instead a customized stored procedure based on the CDOSys stored procedure from Microsoft which does not require a IMAP client to be installed on the SQL Server. For SQL Server 2005 and 2008, Database Mail should be used as it is native and the best option available.

Once a method of providing notifications is configured it is possible to begin monitoring the SQL Server with TSQL scripts. Items that can be easily monitored with TSQL include:

Return to Top

VBScript/WMI monitoring through Windows Task Scheduler

Along with monitoring for events internal to SQL Server, it is also important for to monitor items external to SQL Server. Since Windows Server 2000, VBScript and WMI have been available, and have been used for automating various tasks for administrators. VBScript can use the CDO object to send email natively which makes it a very good way to provide notifications of exceptions found. Items that can be monitored with VBScript and WMI include:

Return to Top

For additional information please see:

DBA Checklist

http://blogs.msdn.com/buckwoody/archive/2008/03/28/real-world-dba-episode-5-the-dba-checklist.aspx

Automating DBA Processes (TechEd 2008)

http://mfile.akamai.com/14853/wmv/microsofttec.download.akamai.com/14853/TechEdOnline/Videos/08_NA_ITP_TEOPanel_69_low.asx

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Oct 21, 2008

UPDATED WIKI: ProcessInfoMon

JonathanKehayias — Mon, 01 Dec 2008 21:49:19 GMT

Monitor Running Process Information

Jonathan Kehayias, October 28, 2008

The following script will generate a report of information for the running SQL processes on a server in a text file output.

RunningProcessMonitor.vbs Script

 
on error resume next
 
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set oFSO = CreateObject("Scripting.FilesyStemObject")
Outputfile="RunningProcessInformation.txt"
InputFile="Serverlist.txt"
 
Set ifile = iFSO.OpenTextFile(inputfile)
Set ofile = ofso.createTextFile(OutputFile, True)
 
Do until ifile.AtEndOfLine
 
	strComputer = ifile.ReadLine
 
 
	Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
	Set colProcessList = objWMIService.ExecQuery ("Select * from Win32_Service WHERE Name LIKE '%SQL%'")
 
 
For Each objProcess in colProcessList
	ofile.writeline  "	Name: " & objProcess.Name
	ofile.writeline  "	Started:" & objProcess.Started 
	ofile.writeline  "	Status: " & objProcess.Status
	ofile.writeline  "	State: " & objProcess.State
	ofile.writeline  "	Process ID: " & objProcess.ProcessID 
	ofile.writeline  "	Thread Count: " & objProcess.ThreadCount 
	ofile.writeline  "	Page File Size: " & objProcess.PageFileUsage 
	ofile.writeline  "	Page Faults: " & objProcess.PageFaults 
	ofile.writeline  "	Working Set Size: " & objProcess.WorkingSetSize 
	ofile.writeline  ""
Next
 
Loop

Return to Top

You can find the sample ServerList.txt file ServerList.txt.

This is article is part of a series on Automating Common DBA tasks in SQL Server. To see the full series see: Automating Common DBA Tasks

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Oct 28, 2008

UPDATED WIKI: ErrorLogMon

JonathanKehayias — Mon, 01 Dec 2008 21:43:27 GMT

Monitor the SQL Server Error Log

Jonathan Kehayias, October 28, 2008

One of the most important things that a DBA could monitor is the SQL Server Error Log. SQL Server will log almost every event of interest to a DBA for early problem identification to the Error Log. This includes failed logins, Backup completion by database, and backup failures by database, the end of recovery after a restart, DBCC Events, reconfiguration of server or database options, non-yielding schedulers, stack dumps, autogrowth failures, delayed IO events, and trace flag output if the flags have been turned on. Developers can also have errors in code execution be logged to the Error Log by using the WITH LOG option of RAISERROR. The amount of information that a DBA can get from the Error Log can be quite overwhelming. For this reason, non-critical events and events of non-interest should be filtered from the log data when generating automated notifications.

The following stored procedure will read the Error Log. There are three parameters which control what log file is actually read, whether the output is to the Results Grid or to email notifications, and the number of minutes from the runtime to look backwards for events. The procedure can be called without any of the parameters since all of them have default values set for them, and it will generate email notifications for any event not filtered out for the last 5 minutes in the active Error Log.

GetErrorLogEvents Stored Procedure

 
CREATE PROCEDURE [dbo].[GetErrorLogEvents] (@LogNumber int = 0, @runlocal int = 0, @NumberOfMinutes int = 5)
AS
--  Test Variables
--declare @Lognumber smallint
--declare @runlocal bit
--set @Lognumber = 0
--set @runlocal = 0
--
 
declare @count  smallint,
        @SQL    varchar(1000),
        @output varchar(255),
		@runtime datetime,
		@sqlversion varchar(10)
 
set @count  = 0
set @SQL    = ''
set @output = ''
set @sqlversion = rtrim(ltrim(substring(@@version, 22, 5)))
set @runtime = getdate()
 
if @runlocal = 1
	set @runtime = getdate()-100
 
 
if object_id('tempdb..#errors') is not null
begin
   drop table #errors
end
 
CREATE TABLE #errors
(
	RowID int IDENTITY PRIMARY KEY,
	EntryTime datetime,
	source varchar(50),
	LogEntry varchar(4000)
)
 
if @@version like '%2000%'
begin
	if object_id('tempdb..#errorlog') is not null
	begin
	   drop table #errorlog
	end
 
	create table #errorlog
	(
	   log_id          int identity primary key, 
	   alert           char(5) default ' ',
	   logentry       varchar(4000),
	   continuationrow int
	)
 
	select @SQL = 'exec master..Sp_readerrorlog ' + convert(varchar(3),@lognumber)
	insert into #errorlog (logentry, continuationrow)
	execute (@SQL)
 
	declare @loop int
	declare @text varchar(3000)
 
	select @loop = max(log_id)
	from #errorlog
	where continuationrow = 1
 
	while @loop is not null
	begin
 
	select @text = null
 
	select @text = logentry
	from #errorlog
	where log_id = @loop
 
	update #errorlog
	set logentry = logentry + @text
	where log_id = @loop - 1
 
	delete #errorlog where log_id = @loop
 
	select @loop = max(log_id)
	from #errorlog
	where continuationrow = 1
	and log_id < @loop
 
	end
	   
	-- Mark interesting entries
	update #errorlog
	   set alert = '---->'
	 where (logentry like '%err%'
		or logentry like '%warn%'
		or logentry like '%kill%'
		or logentry like '%dead%'
		or logentry like '%cannot%'
		or logentry like '%could%'
		or logentry like '%fail%'
		or logentry like '%not%'
		or logentry like '%stop%'
		or logentry like '%terminate%'
		or logentry like '%bypass%'
		or logentry like '%roll%'
		or logentry like '%truncate%'
		or logentry like '%upgrade%'
		or logentry like '%victim%'
		or logentry like '%recover%'
		or logentry like '%IO requests taking longer than%')
		AND logentry not like '%errorlog%'
		AND logentry not like '%dbcc%'
 
	INSERT INTO #errors (entrytime, source, logentry)
	-- Show each interesting entry
	select
		   CASE WHEN left(logentry, 3) = '200' THEN convert(datetime, substring(logentry, 0, 23))
				ELSE NULL 
			END AS EntryTime,
		   CASE WHEN left(logentry, 3) = '200' THEN substring(logentry, 24, 10)
				ELSE NULL 
			END AS Source,
		   CASE WHEN left(logentry, 3) = '200' THEN substring(logentry, 34, 300)
				ELSE logentry 
			END AS LogEntry
	  from #errorlog
	where Alert = '---->'
 
	IF @RunLocal = 1
	BEGIN
		select * from #errorlog
	END
 
 
end -- SQL 2000
else -- SQL 2005
begin
 
	select @SQL = 'exec master..xp_readerrorlog ' + convert(varchar(3),@lognumber)
	insert into #errors (entrytime, source, logentry)
	execute (@SQL)
 
delete #errors 
where (logentry not like '%err%'
		AND logentry not like '%warn%'
		AND logentry not like '%kill%'
		AND logentry not like '%dead%'
		AND logentry not like '%cannot%'
		AND logentry not like '%could%'
		AND logentry not like '%fail%'
		AND logentry not like '%not%'
		AND logentry not like '%stop%'
		AND logentry not like '%terminate%'
		AND logentry not like '%bypass%'
		AND logentry not like '%roll%'
		AND logentry not like '%truncate%'
		AND logentry not like '%upgrade%'
		AND logentry not like '%victim%'
		AND logentry not like '%recover%'
		AND logentry not like '%IO requests taking longer than%')
		OR logentry like '%errorlog%'
		OR logentry like '%dbcc%'
 
END
 
DELETE #Errors 
WHERE EntryTime IS NULL OR EntryTime <  dateadd(mi, (-1*@NumberOfMinutes), @runtime)
 
-- If local only return the events table back to the Results Grid
IF @RunLocal = 1
BEGIN
	SELECT *
	FROM #errors
END
ELSE -- Insert the errors into the SystemEvents table to trigger notification.
BEGIN
 
	CREATE TABLE #Exceptions
	(
		RowID int identity Primary Key,
		LogEntry varchar(4000),
		EntryCount int,
		LastOccurence datetime
	)
 
 
	INSERT INTO #Exceptions (LogEntry, EntryCount, LastOccurence)
	SELECT LogEntry, count(*), max(EntryTime)
	FROM #Errors
	GROUP BY LogEntry
 
		DECLARE @Loop2 int
		DECLARE @Subject varchar(100)
		DECLARE @strMsg varchar(4000)
 
		SELECT @Subject = 'SQL Monitor Alert: ' + @@servername
 
		SELECT @Loop2 = min(RowID)
		FROM #Exceptions
 
		WHILE @Loop2 IS NOT NULL
		BEGIN
 
			SELECT 	@strMsg =  convert(char(15),'LogEntry:') + isnull(LogEntry, 'Unknown') + char(10) +
					convert(char(15),'EventTime:') + convert(varchar, LastOccurence) + char(10) +
					convert(char(15),'') + char(10) +
					convert(char(15),'') + 'This error has occured '+convert(varchar, EntryCount)+' times.'
			FROM #Exceptions
			WHERE RowID = @Loop2
 
			EXEC dbo.SendEmailNotification @Subject, @strMsg
 
			SELECT @Loop2 = min(RowID)
			FROM #Exceptions
			WHERE RowID > @Loop2
 
		END
 
END
 
DROP TABLE #Exceptions
DROP TABLE #Errors

Return to Top

Since SQL Server only starts a new log natively when the Service is restarted, the log files can grow to be very large in size, depending on the number of errors logged, and the time between service/server restarts. This can impact the performance of the above stored procedure if the log is large enough. To mitigate against this, the Error Log can be rolled over nightly by calling the following TSQL command in a SQL Agent Job.

Rollover the SQL Server Error Log with DBCC

 
USE [master]
GO
DBCC ERRORLOG
GO

Return to Top

This can also be accomplished with sp_cycle_errorlog

Rollover the SQL Server Error Log with sp_cycle_errorlog

 
USE [master]
GO
sp_cycle_errorlog
GO

Return to Top
However, this may cause the logs to flush off of the server too fast under the default log retention of six Error Logs. When performing a nightly rollover of the Error Logs, it is recommended that the default log retention be set to retain the needed number of Logs on the server to meet business and regulatory requirements. This can be done using the following TSQL command:

Change Error Log Retention Value

 
USE [master]
GO
DECLARE @NumberOfLogs int
SET @NumberOfLogs = 28
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, @NumberOfLogs
GO

Return to Top

This procedure uses the SendNotificationEmail wrapper procedure to create a standard set of code that can be used on all editions of SQL Server. This wrapper stored procedure can be found on one of the following articles:

This is article is part of a series on Automating Common DBA tasks in SQL Server. To see the full series see: Automating Common DBA Tasks

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Oct 28, 2008