Posted by David Portas on 04/06/06 19:57

jim_geissman@countrywide.com wrote:
> Re SET NOCOUNT
>
> My experience yesterday:
>
> a) default state is row count echo, e.g. "(1257343 row(s) affected)"
> b) set nocount on stops those -- e.g., during a script that does a lot
> of inserts
> c) while nocount is on, the echo is: "Command(s) completed
> successfully." Once for each block of commands rather than
> individually for each insert/update/etc query
> d) set nocount off should cause row counts to reappear -- a way to
> confirm queries are working
> e) "Command(s) completed successfully." persisted after set nocount
> off -- have to use other means to confirm what happened

That sounds strange. When I run the following script in SSMS:

SET NOCOUNT ON
SELECT 1
SET NOCOUNT OFF
SELECT 2

GO

I get:


-----------
1


-----------
2

(1 row(s) affected)


Exactly what I expected. I get the same result if I put that code in a
proc. Do you get the same output?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]