User Messages in a stored proc

Question

Post reply

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: 323482 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: 6190 More actions · Answer 3

Lamprey13

SSCertifiable

Points: 6190

February 3, 2011 at 12:34 pm

#1282100

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

sturner

SSC-Insane

Points: 22411

February 3, 2011 at 12:50 pm

#1282108

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

danielfountain

SSCarpal Tunnel

Points: 4229

February 4, 2011 at 2:34 am

#1282323

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