Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language

Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

set nocount on to suppress rows affected but keep prints

I am using T-SQL and use

set nocount on

to suppress:

x row(s) affected


but this also suppresses my print statements, which I do want to know about.

I tried shutting using

set nocount off and then
set nocount on

in the code near the print statement, like so:  

if @count%100 = 0
set nocount off
print @count
set nocount on

but this won't let me see @count in the messages.

I need to know the progress a stored procedure that takes a long time.

Andy in S. Jersey,

This has nothing to do with the settings of "set nocount". It is related to
the way SQL Server returns output parameters, return value, warnings, etc. to
the client application. Use "RAISERROR...WITH NOWAIT" instead.

if @count%100 = 0
   raiserror('bla bla bla', 10, 1) with nowait


"Andy in S. Jersey" wrote:

On Apr 23, 2:24 pm, Andy in S. Jersey

set nocount on doesn't suppress your prints, but what you're probably
experiencing is that SQL Server doesn't send anything back to a
connected client until a buffer is full (or the query that the client
has sent has completed), so your print message works but isn't large
enough to cause SQL Server to send the buffer. I'm not aware of any
way to force early sending of a buffer.

The following might give you some idea of how you can force a message
out - it's not pretty, but you can at least see your messages without
losing them in the clutter. When I run this on my 2000 box through QA,
I see "ABCD" immediately, and 30 seconds later see "B":

set nocount on
print 'ABCD'
declare @i int
set @i = 56
while @i > 0
        print ''
        set @i = @i - 1
print 'B'
WAITFOR DELAY '00:00:30'



Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc