Thursday, November 24, 2005 4:45 PM
Another Case for SET NOCOUNT ON
Dan Wahlin just posted a message to one of the lists on aspadvice about some interesting behavior that he noticed in VS 2005 - executing a sproc (against SQL Server 2000) from within VS 2005 wasn't returning the entire result set that he was expecting. He could execute the sproc in QA, and it worked fine. Executing in VS 2005 would show him how many rows should be there (i.e. x rows affected), but wouldn't show the rows.
Intrigued, I fired up my own little test. The first sproc I tested worked perfectly - everything came back as expected. Then I took Dan's sproc, modified it a little bit to pull data from the local systables as 'filler' instead of pulling from the tables Dan was pulling from and tested it. Sure enough - I saw the behavior he posted.
A quick scan of the differences instantly showed that my working sproc did a SET NOCOUNT ON - his didn't. A quick tweak, and ensuing test, and his sproc was working as expected. SET NOCOUNT ON is used to tell SQL Server to be less chatty - i.e. stop telling the client the step by step, and blow by blow execution of every line of code. It also has the added benefit of allowing multiple result sets to be returned to clients that tend to BAIL as soon as they a result returned from the server - such as ADO 2.x and apparently the client used by VS 2005.