Saturday, 14 March 2009

System Views, Stored Procedures and SET NOCOUNT

Problems with data providers and SET NOCOUNT

Its standard good practise to always use SET NOCOUNT ON in your stored procedures to prevent system messages about the number of rows affected by DML statements from being returned. However as well as being good practise I have found that it can actually cause issues in certain cases depending on the data access provider used to connect to your SQL database from your front end application. If you have read my article about migrating from 32 bit to 64 bit applications you will see that I mention certain issues there. I have experienced problems when moving from MDAC to SQLOLEDB when stored procedures contain multiple DML statements and SET NOCOUNT has not been set as it seems these system messages are being returned as a recordset and whereas MDAC will ignore them SQLOLEDB doesn't. Therefore you may experience errors such as "Operation not allowed when the object is closed" or "item or ordinal does not exist in collection" because these system messages are being returned or accessed before your intended recordset is.


Solution - Add SET NOCOUNT ON

Therefore the solution is simple, make sure all stored procedures have this command set at the top of them. However if you have a large database containing hundreds of stored procedures then this would be quite a task to carry out manually. Therefore I came up with a way to automate this task when upgrading large database systems by using the system views available in SQL Server.

The idea is pretty simple.

  • Find out which stored procedures do not contain the SET NOCOUNT statement.
  • Script out those stored procedures and update them to contain the statement.
  • Run the script to ALTER the stored procs and update the database
  • Use standard functions and pure TSQL to accomplish the task.

Syscomments and Sysobjects

In SQL 2000 and 2005 all stored procedure and user defined functions have their source code available for viewing from the syscomments system view. The system views cannot be updated directly so its a case of using them to generate ALTER statements to run if anything requires changing.

You can view all the stored procedures without the SET NOCOUNT statement with the following SQL.


SELECT c.*,o.*
FROM sys.syscomments as c
JOIN sys.sysobjects as o
ON o.id = c.id
WHERE xtype='P' --stored procs
AND Name LIKE 'usp_%' --my prefix
AND LOWER(text) NOT LIKE '%set nocount on%'
ORDER BY o.Name
One of the things you should be aware of is that the text column which contains the code is nvarchar(4000) and procedures or functions that have a code length greater than 4000 will be split up over multiple rows. The id column relates to the system object_id which can be used to join to sysobjects and the colid will contain the subsection of code. Therefore you will notice that the previous SQL is not 100% correct as a procedure that is split over 4 rows maybe returned even if it does have SET NOCOUNT ON set because only the first section with a colid of 1 will not match the filter.


Solution to the problem

The final script I used to solve this solution can be accessed here:

Download SQL script to insert missing SET NOCOUNT ON statements to stored procedures

I have split it into two separate loops mainly to get round limitations of displaying large strings from variables in query analyser and the system stored procedure I have used to return the complete code from syscomments which is called sp_helpText.

There are multiple ways that this task could have been achieved but I wanted to keep it a purely TSQL solution and although its not the most elegant piece of code ever writen it has done the job it was designed to do which was to update 100+ stored procedures so that I didn't have to do it by hand.

To view more solutions to common database problems that I have solved by using the system views please see

9 comments:

Anonymous said...

Excellent job, placing automatically set nocount ons to sps was the thing I've been searching for.We will make this script run for nearly 6000 sps !
Thus it was very usefull.Thanks a lot.

Anonymous said...

Hi again, I'm writing to you for the 2nd time today.I found a bug in the script.You have to insert an extra NL after the command:

INSERT #PROC EXEC sp_helpText @ProcName

otherwise, in same cases, it removes the last line of the original script.

Take care.

Anonymous said...

I too experienced the last line being omitted. (SQL 2008)

Other than that excellent code!

R Reid said...

I have updated the script to include an extra new line insert to prevent this bug from happening.

Anonymous said...

Need to add this to ensure the last line is not omitted:

SELECT
@ROWS = @@ROWCOUNT
, @MaxRowNo = MAX(RowNo) + 1
FROM
#PROC

Link to code still not showing this!

Cheers
GSC

R Reid said...

Surely I don't need to add that line now that I have added the extra New Line insert statement above it as requested which means that MAX(RowNo) is always going to be the last row in the temp table containing just a newline command.

INSERT #PROC EXEC sp_helpText @ProcName

-- fix bug that misses out last line sometimes
INSERT INTO #PROC
(ProcLine)
VALUES
(@NL)

SELECT @ROWS = @@ROWCOUNT, @MaxRowNo = MAX(RowNo)
FROM #PROC

You could remove that code I added in and then add MAX(RowNo)+1 if you want. Or you could do both and have a blank line as the last line in the output.

As the downloadable code currently stands it works perfectly for the systems I have tested it on.

Anonymous said...

This is the code I am using: modified slightly from yours!
This works in my environment (W7 x64, SQL Server 2008 R2 x64)

-- =============================================================================================
-- Author: Rob Reid
-- Create date: 12-Mar-2009
-- Description: SQL script to correct all stored procedures that do not contain SET NOCOUNT ON.
-- The script will add the relevant code in and then output the necessary ALTER PROCEDURE
-- statements to be run to correct the problem as system views cannot be updated directly.
-- =============================================================================================
SET NOCOUNT ON
DECLARE @Prefix VARCHAR(10) = 'admin_'

DECLARE
@ProcName NVARCHAR(255)
, @ProcCode NVARCHAR(MAX)
, @Line NVARCHAR(4000)
, @RowNo INT
, @MaxRowNo INT
, @MinRowNo INT
, @Inserted INT
, @Append BIT
, @Rows INT
, @NL CHAR(2)

SELECT
@NL = CHAR(13) + CHAR(10)
, @MinRowNo = 0
, @MaxRowNo = 0

-- Create table to hold text from stored procs

SELECT
@ProcCode = ''
IF OBJECT_ID('tempdb..#PROC') IS NOT NULL
DROP TABLE #PROC

CREATE TABLE #PROC
(
[RowNo] INT IDENTITY(1, 1)
, [ProcLine] NVARCHAR(1000)
)

DECLARE PROCS CURSOR LOCAL FAST_FORWARD
FOR
-- Select all stored procs that do not mention set nocount on
-- as syscomments holds procs over multiple rows the set nocount could appear
-- in any row not just where colid=1
SELECT DISTINCT
NAME
FROM
sys.syscomments AS c
JOIN sys.sysobjects AS o
ON o.id = c.id
WHERE
xtype = 'P'
AND Name LIKE @Prefix + '%' --I prefix all my procs with usp_
AND c.id NOT IN (
SELECT DISTINCT
c.id
FROM
sys.syscomments AS c
JOIN sys.sysobjects AS o
ON o.id = c.id
WHERE
xtype = 'P' --stored procs
AND Name LIKE @Prefix + '%' --my prefix
AND LOWER(text) LIKE '%set nocount on%' ) --contain set nocount on
ORDER BY
Name FOR READ ONLY
OPEN PROCS
WHILE ( 1 = 1 )
BEGIN
FETCH NEXT
FROM PROCS
INTO @ProcName
IF @@FETCH_STATUS <> 0
BREAK

-- Insert starting code to replicate SQL MS script behaviour
INSERT INTO #PROC
( ProcLine )
VALUES
( 'SET ANSI_NULLS ON' )
,( @NL )
,( 'GO' )
,( @NL )
,( 'SET QUOTED_IDENTIFIER ON' )
,( @NL )
,( 'GO' )
,( @NL )

INSERT #PROC
EXEC sp_helpText @ProcName
SELECT
@ROWS = @@ROWCOUNT
, @MaxRowNo = MAX(RowNo) + 1
FROM
#PROC
--Find line in proc that contains the AS keyword after parameter declaration
SELECT
@RowNo = RowNo
FROM
#PROC
WHERE
RowNo BETWEEN @MinRowNo AND @MaxRowNo -- work with current proc only
AND ( ProcLine LIKE 'AS[^A-Z]%'
OR RIGHT(ProcLine, 5) = ' AS' + @NL )
--Update row with SET NOCOUNT ON
UPDATE #PROC
SET ProcLine = ProcLine + @NL + 'SET NOCOUNT ON' + @NL
WHERE RowNo = @RowNo
-- Change CREATE to ALTER
UPDATE
#PROC
SET
ProcLine = REPLACE(ProcLine, 'CREATE PROCEDURE',
'ALTER PROCEDURE')
WHERE
RowNo BETWEEN @MinRowNo AND @MaxRowNo

-- Set marker for next proc
SELECT
@MinRowNo = @MaxRowNo

END
CLOSE PROCS
DEALLOCATE PROCS

--Everything below is the same

Gazza said...

In response to RReid @ 30 March 2011 15:44 :-
SELECT
@ROWS = @@ROWCOUNT
, @MaxRowNo = MAX(RowNo) + 1
FROM
#PROC


The + 1 actually produces an extra line that is NOT required

Thanks very much for the code - saved me hours of work!

R Reid said...

which is what my last comment was trying to get at. The +1 just creates an extra blank line at the end which isn't needed now the extra new line was added.

That other code someone posted is fine for SQL 2008 but 2005 or below it won't work. But thanks anyway