PRINT only takes effect after T-SQL completion ?

[James]

Hello All,

I have a stored procedure which will act like a main/controller script
which will then invoke more stored procedures (~20). Basically, it
looks something like below:

-- start script
create procedure ...
print 'process started'
exec sp_1
exec sp_2
exec sp_3
....
print 'process ended'
-- end script
Looking at it, after running that procedure, immediately I would expect
the first PRINT statement to be printed but it won't. It seemed
that the print statements would only display the messages after
the whole processing completed.

What's the reason for this sort of behaviour. If so, then we would
not be able to print any progress reporting in our scripts.

Please comment.
Thanks in advance.

[Jesper]

Insert a "go" after each print and it sould work.

-- start script
create procedure ...
print 'process started'
go
exec sp_1
print 'sp_1 finished'
go
exec sp_2

print 'sp_2 finished'
go
exec sp_3

print 'sp_3 finished'
go
....
print 'process ended'
-- end script

[Andrew Young]

I believe that the messages will only appear once the implicit
transaction or proceedure has ended.

i.e

create procedure ...
print 'process started'
go
exec sp_1
exec sp_2
exec sp_3
...
print 'process ended'
go

It is too bad that you cannot use 'go' in a stored proceedure.

eh****@hotmail. com (James) wrote in message news:<46******* *************** ****@posting.go ogle.com>...

Hello All,

I have a stored procedure which will act like a main/controller script
which will then invoke more stored procedures (~20). Basically, it
looks something like below:

-- start script
create procedure ...
print 'process started'
exec sp_1
exec sp_2
exec sp_3
...
print 'process ended'
-- end script
Looking at it, after running that procedure, immediately I would expect
the first PRINT statement to be printed but it won't. It seemed
that the print statements would only display the messages after
the whole processing completed.

What's the reason for this sort of behaviour. If so, then we would
not be able to print any progress reporting in our scripts.

Please comment.
Thanks in advance.

[Mischa Sandberg]

If you're talking to SQLServer through an ODBC connection (what else?), the
ODBC protocol is really what's driving the batching of messages and result
sets.

PRINT statements, RAISERROR output, and system errors all reach the client
through ODBC SQLError(). Logically, all such messages are batched up until a
result set, or until the end of the RPC call (SQLExecute or SQLExecDirect).
The ODBC connection doesn't know anything about what procedures you're
calling. If you execute a PRINT command, then call a stored procedure that
creates a result set and then prints some more, then create a result set,
then from the CLIENT's point of view, it receives the first print command
and the procedure's result set together. The client then has to call
SQLMoreResults, then receives the procedure's print commands and the final
result set together.

So, if you want to receive 'PRINT' output in a synchronized way, I suggest
you use SELECT instead.

There are some fine points if you use ODBC 'asynch' calls, but in essence
it's the same deal. Having seen the implementaton of a whack of ODBC
drivers, I'd strongly suggest you not use ODBC asynch calls!

"Andrew Young" <at*******@yaho o.com> wrote in message
news:42******** *************** ***@posting.goo gle.com...

I believe that the messages will only appear once the implicit
transaction or proceedure has ended.

i.e

create procedure ...
print 'process started'
go
exec sp_1
exec sp_2
exec sp_3
...
print 'process ended'
go

It is too bad that you cannot use 'go' in a stored proceedure.

eh****@hotmail. com (James) wrote in message

news:<46******* *************** ****@posting.go ogle.com>...

Hello All,

I have a stored procedure which will act like a main/controller script
which will then invoke more stored procedures (~20). Basically, it
looks something like below:

-- start script
create procedure ...
print 'process started'
exec sp_1
exec sp_2
exec sp_3
...
print 'process ended'
-- end script
Looking at it, after running that procedure, immediately I would expect
the first PRINT statement to be printed but it won't. It seemed
that the print statements would only display the messages after
the whole processing completed.

What's the reason for this sort of behaviour. If so, then we would
not be able to print any progress reporting in our scripts.

Please comment.
Thanks in advance.

[Erland Sommarskog]

James (eh****@hotmail .com) writes:

I have a stored procedure which will act like a main/controller script
which will then invoke more stored procedures (~20). Basically, it
looks something like below:

-- start script
create procedure ...
print 'process started'
exec sp_1
exec sp_2
exec sp_3
...
print 'process ended'
-- end script
Looking at it, after running that procedure, immediately I would expect
the first PRINT statement to be printed but it won't. It seemed
that the print statements would only display the messages after
the whole processing completed.

The message are buffered, this is why don't see them at once. Replace
them with:

RAISERROR('Proc ess started', 0, 1) WITH NOWAIT

and your odds improve. That is, as long as the client library is doing
its job properly, the messages should appear promptly, but I know
some cases where the client incorrectly buffers the message.

As for the RAISERROR, note that the severity is 0. PRINT is just a
short-hand for RAISERROR with level 0...

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp