adding SET NOCOUNT ON to large number of stored procedures [modified]
-
What's a good way to modify a large number of stored procedures? I took over a project that is not using SET NOCOUNT ON, i.e. the following query returns several hundred rows. SELECT Distinct SO.Name, SC.TEXT FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.colid=1 --multiple colid's are utilized when the procedures text is very long AND NOT UPPER(SC.TEXT) LIKE '%SET NOCOUNT ON%' ORDER BY SO.Name
modified on Friday, November 27, 2009 2:17 AM
hi Ted SET NOCOUNT ON is used if you dont want to show results. Lets say i have a Query that Updates a Table, at the end it will give me something like
100 Rows Affected
to make sure that the update does not show that message , you use SET NOCOUNT ON i have never seen this use in a query passed as a parameter, am not sure if this is correct because as i remember correctly you are not supposed to do that. mybe you can explain your challenge clearly and we can suggest another option
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
-
hi Ted SET NOCOUNT ON is used if you dont want to show results. Lets say i have a Query that Updates a Table, at the end it will give me something like
100 Rows Affected
to make sure that the update does not show that message , you use SET NOCOUNT ON i have never seen this use in a query passed as a parameter, am not sure if this is correct because as i remember correctly you are not supposed to do that. mybe you can explain your challenge clearly and we can suggest another option
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
I wanted to add SET NOCOUNT ON in the body of the stored procedure after AS. I am relatively new to SQL Server (I used Oracle before) and the articles I read on google suggested that you should always SET NOCOUNT ON before executing queries inside a stored procedure.
-
I wanted to add SET NOCOUNT ON in the body of the stored procedure after AS. I am relatively new to SQL Server (I used Oracle before) and the articles I read on google suggested that you should always SET NOCOUNT ON before executing queries inside a stored procedure.
then the correct way to do it is the Following
Create Proc myprc
asset nocount on
--Do what Ever and set it back to off
set nocount off
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
-
then the correct way to do it is the Following
Create Proc myprc
asset nocount on
--Do what Ever and set it back to off
set nocount off
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
-
That's not what was asked, he knows how to set nocount on. He wants to know if there is a way to easily modify all of his stored procedures to include this line.
I must have misunderstood him , No there is no way even SQL Compare wouldnt do that
Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa@its.co.za http://www.itsabacus.co.za/itsabacus/
-
What's a good way to modify a large number of stored procedures? I took over a project that is not using SET NOCOUNT ON, i.e. the following query returns several hundred rows. SELECT Distinct SO.Name, SC.TEXT FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.colid=1 --multiple colid's are utilized when the procedures text is very long AND NOT UPPER(SC.TEXT) LIKE '%SET NOCOUNT ON%' ORDER BY SO.Name
modified on Friday, November 27, 2009 2:17 AM
You could perhaps do this using a cursor with sysobjects and sp_helptext. You would use the cursor to step through "select name from sysobjects where xtype = 'P'". You could then get the stored procedure using sp_helptext and modify it and execute accordingly.
-
You could perhaps do this using a cursor with sysobjects and sp_helptext. You would use the cursor to step through "select name from sysobjects where xtype = 'P'". You could then get the stored procedure using sp_helptext and modify it and execute accordingly.
Or you could go to your version control system, check out the scripts for the stored procedures, modify them as required (using a little noddy program to automate the process), test them, check them back in again and then re-run them to drop and recreate the stored procedures in the target database. Oh, dear. No scripts.
-
Or you could go to your version control system, check out the scripts for the stored procedures, modify them as required (using a little noddy program to automate the process), test them, check them back in again and then re-run them to drop and recreate the stored procedures in the target database. Oh, dear. No scripts.
-
What's a good way to modify a large number of stored procedures? I took over a project that is not using SET NOCOUNT ON, i.e. the following query returns several hundred rows. SELECT Distinct SO.Name, SC.TEXT FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.colid=1 --multiple colid's are utilized when the procedures text is very long AND NOT UPPER(SC.TEXT) LIKE '%SET NOCOUNT ON%' ORDER BY SO.Name
modified on Friday, November 27, 2009 2:17 AM
You can use SMO to generate scripts add the statement you need and execute the script.
Giorgi Dalakishvili #region signature My Articles Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^] My blog #endregion
-
You could perhaps do this using a cursor with sysobjects and sp_helptext. You would use the cursor to step through "select name from sysobjects where xtype = 'P'". You could then get the stored procedure using sp_helptext and modify it and execute accordingly.
-
You can use SMO to generate scripts add the statement you need and execute the script.
Giorgi Dalakishvili #region signature My Articles Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^] My blog #endregion