Solved

SET NOCOUNT ON - OFF - ON - OFF....

Posted on 2001-09-12
4
1,344 Views
Last Modified: 2007-12-19
Not really a question here...
Well, it is.

When i go to MSDN there is an article that states
that when you want to use a temporary table created by a stored procedure with ADO...
You should place SET NOCOUNT ON before any action is taken, they don't mention SET NOCOUNT OFF.

But then in SQL Books Online is stated that you
always should place SET NOCOUNT OFF after the handling...

What do these commands actually really do etc...
0
Comment
Question by:wvandeneede
4 Comments
 
LVL 1

Accepted Solution

by:
deestuar earned 10 total points
ID: 6476650
I normally do both for good practise if you don't do this you can get problems when retrieving data with record sets
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6476670
I do actually only put it in front (SET NOCOUNT ON),
when my stored procedures are returning empty recordsets which are skipped that way when reading from VB+ADO.
When testing some scripts with some insert statments, i use it too, but for the rest i don't...
CHeers
0
 
LVL 6

Expert Comment

by:curtis591
ID: 6476830
Set nocount on stops the message indicating the number of rows affected by a sql statement from being returned.  If you go to query analyzer and run the statement you will not get the message 5 rows affected.  I have read a number of articles that claim that by setting nocount on in your procedures you can really get a performance boost but I have never really tested this theory.
0
 

Author Comment

by:wvandeneede
ID: 6482650
Since this wasn't really a question, i don't know
who to give the points to?

I hope everyone will be satisfied when i just give
them to the first reaction

measly little 10 points anyway ;)

CyA WeZ
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question