Posted on 2001-09-12
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...
Question by:wvandeneede
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

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
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...

Expert Comment

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.

Author Comment

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 ;)


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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…
Viewers will learn how the fundamental information of how to create a table.

622 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