User Messages in a stored proc

Question

User Messages in a stored proc

danielfountain

SSCarpal Tunnel

Points: 4229
More actions
February 3, 2011 at 3:11 am

#388768

Hey all,
I have written a stored proc that goes through a list of items in a loop.
After each loop it uses PRINT to let the user know the result of the loop.
However in Management Studio - i am only seeing the PRINTS at the end of the stored proc. Not as they happen.
I have tried raising errors and get the same result.
Is there any way to get messages to appear during a stored proc - and not just all appear at the end.
TIA
Dan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

They are buffered and will squirt out when the buffer is full or the process terminates.

If you must have these "messages" received at critical points during a long running procedure you could write them to a service broker message queue where a receiving process on the other end can process them (or print them) as they happen.

The probability of survival is inversely proportional to the angle of arrival.

Lowell SSC Guru Points: 323518 More actions · Answer 2

there's an extra option for raiserror so you can get immediate feedback; with nowait, and that shows up immediately in SSMS:

here's an example:

--print error immediately in batch

declare @i int,

@err varchar(100)

--set @i=1

while 0=0

begin

SET @err = 'Progress So Far: Step ' + convert(varchar(30),ISNULL(@i,1)) + ' completed.'

raiserror (@err,0,1) with nowait

waitfor delay '00:00:02'

set @i=ISNULL(@i,1) + 1

end

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 3

If you use RAISERROR and add the WITH NOWAIT directive it'll return results at run time. For example:DECLARE @I INT

SET @i = 0

WHILE @i < 10

BEGIN

RAISERROR(N'Message = %i', 10, 1, @i) WITH NOWAIT

WAITFOR DELAY '00:00:05'

SET @i = @i + 1

END

EDIT: Too slow. 🙁

sturner SSC-Insane Points: 22411 More actions · Answer 4

excellent point about the nowait on raiserror fellas, I had forgotten about that feature.

The probability of survival is inversely proportional to the angle of arrival.

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 5

Thanks all - thats perfect.

I am giving a stored proc to run to 3-4 people (who wont need to run it often). I didnt just want them to have to sit there for the 20 mins or so waiting for it to run. They would probably think something had broken and end it.

Thanks again.

Dan

SQLSmasher Right there with Babe Points: 777 More actions · Answer 6

thanks Lamprey13 for refreshing brain cells with this nice feature of SQL Server

ghanshyam.kundu Hall of Fame Points: 3337 More actions · Answer 7

RAISERROR('Inside loop',0,1)WITH NOWAIT

follow the link to know more about raiserror

http://msdn.microsoft.com/en-us/library/ms178592.aspx