Comments on Strictly Software: System Views, Stored Procedures and SET NOCOUNT

which is what my last comment was trying to get at...

2011-03-30T16:39:54.649+01:00

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

In response to RReid @ 30 March 2011 15:44 :- S...

2011-03-30T16:13:36.294+01:00
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!

This is the code I am using: modified slightly fro...

2011-03-30T16:04:24.069+01:00
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.
-

Surely I don't need to add that line now that ...

2011-03-30T15:44:05.540+01:00
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

Need to add this to ensure the last line is not om...

2011-03-30T15:20:32.833+01:00
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

I have updated the script to include an extra new ...

2011-03-29T14:38:08.355+01:00
I have updated the script to include an extra new line insert to prevent this bug from happening.

I too experienced the last line being omitted. (SQ...

2011-03-29T11:11:39.751+01:00
I too experienced the last line being omitted. (SQL 2008)

Other than that excellent code!

Hi again, I'm writing to you for the 2nd time ...

2009-12-29T17:45:43.517Z
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.

Excellent job, placing automatically set nocount o...

2009-12-29T11:01:35.810Z
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.