message queues

I having a fight with what I think is the message queue system when
running stored procedures. If I have this as a sp
print 'Handling First File'
(do some processing on the first file)
print 'Handling Second File'
(do some processing on the second file)
print 'Handling Third File'
(do some processing on the third file)
print 'Done'
I want to use the SQLMDO 'ExecuteWithResultsAndMessages' and
'ServerMessage' functions to use print commands as part of my front end
but what happens is that while all the processing is done my dialog
filled by 'ServerMessage.Message' is blank and then all my print
commands come together - London bus style. (Query Analyser does the
same).
This presumably is down to Message Queuing ? One help file I saw says
something like "when you create a Stored Procedure (with queuing
enabled)...." suggesting that you can create a sp with queing
disabled? but I cant find anything in the syntax to stipulate that.
Is there a way round this so that messages are forwarded by the agent
as they are reached in the script and not queued.?
Thanks
GlennIt really has nothing to do with queuing per say it is that the packet does
not get sent to the client until the batch is done or the buffer is full.
In a nutshell since the packets are around 4K in size it doesn't waste many
round trips each time a little bit of info is placed in the buffer. It
waits until it is full until it sends it or when the batch is done. Here is
an example to show this. Comment out the replicate statement and you will
see it doesn't print until the batch is done.
DECLARE @.X INT
SET @.X = 1
WHILE @.X < 100
BEGIN
PRINT CAST(@.X AS VARCHAR(20))
PRINT REPLICATE(' ',8000)
SET @.X = @.X + 1
waitfor delay '00:00:01'
END
Andrew J. Kelly SQL MVP
<glenn.hughes@.luk.net> wrote in message
news:1140543654.666118.191320@.g44g2000cwa.googlegroups.com...
>I having a fight with what I think is the message queue system when
> running stored procedures. If I have this as a sp
> print 'Handling First File'
> (do some processing on the first file)
> print 'Handling Second File'
> (do some processing on the second file)
> print 'Handling Third File'
> (do some processing on the third file)
> print 'Done'
> I want to use the SQLMDO 'ExecuteWithResultsAndMessages' and
> 'ServerMessage' functions to use print commands as part of my front end
> but what happens is that while all the processing is done my dialog
> filled by 'ServerMessage.Message' is blank and then all my print
> commands come together - London bus style. (Query Analyser does the
> same).
> This presumably is down to Message Queuing ? One help file I saw says
> something like "when you create a Stored Procedure (with queuing
> enabled)...." suggesting that you can create a sp with queing
> disabled? but I cant find anything in the syntax to stipulate that.
> Is there a way round this so that messages are forwarded by the agent
> as they are reached in the script and not queued.?
> Thanks
> Glenn
>|||You can 'cheat' the messages out faster by raising low-level errors...
RAISERROR('Hack!!!',0,1) WITH NOWAIT
HTH,
Ben
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SqYsHxNGHA.1676@.TK2MSFTNGP09.phx.gbl...
> It really has nothing to do with queuing per say it is that the packet
> does not get sent to the client until the batch is done or the buffer is
> full. In a nutshell since the packets are around 4K in size it doesn't
> waste many round trips each time a little bit of info is placed in the
> buffer. It waits until it is full until it sends it or when the batch is
> done. Here is an example to show this. Comment out the replicate
> statement and you will see it doesn't print until the batch is done.
> DECLARE @.X INT
> SET @.X = 1
> WHILE @.X < 100
> BEGIN
> PRINT CAST(@.X AS VARCHAR(20))
> PRINT REPLICATE(' ',8000)
> SET @.X = @.X + 1
> waitfor delay '00:00:01'
> END
>
> --
> Andrew J. Kelly SQL MVP
>
> <glenn.hughes@.luk.net> wrote in message
> news:1140543654.666118.191320@.g44g2000cwa.googlegroups.com...
>|||Thanks to both of you. Ben, I just love that word "Cheat" its what
programming is all about. The error level idea sounds good. I'll try
that
Cheers
Glenn