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
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
Posts
9 comments:
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.
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.
I too experienced the last line being omitted. (SQL 2008)
Other than that excellent code!
I have updated the script to include an extra new line insert to prevent this bug from happening.
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
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.
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
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!
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
Post a Comment