Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

SQL Server: Notify client of progress in a long running process

In long running scripts it's usefull to notify the client of the progress. Here's a simple solution.

 

Let's create a simple "long running" script:

– this will return all 3 results to the client after 20 seconds (full execution time)
SELECT 1
WAITFOR DELAY '00:00:10'
SELECT 2
WAITFOR DELAY '00:00:10'
SELECT 3

 

But what if you want to notify the client when each select happened?

RAISERROR to the rescue:

 

SELECT 1
– flush message to client
RAISERROR (N'After 1', – Message text
           10, – Severity
           1) – State 
      WITH NOWAIT
WAITFOR DELAY '00:00:10'
SELECT 1.5
WAITFOR DELAY '00:00:10'
SELECT 2
– flush waiting messages and resultset to client
– so this will send 1.5 and 2 to the client
RAISERROR (N'After 2', – Message text
           10, – Severity
           1) – State 
      WITH NOWAIT
WAITFOR DELAY '00:00:10'

SELECT 3

 

The thing to note here is the use of NOWAIT option.

This flushes all waiting resultsets and messages back to the client.

Also note the use of non fatal severity and state, since we don't want to cancel our script but only inform the user of the progress.

It comes in very handy when running script that can take a while indeed.

 

I've seen this mentioned before but i can never find it when i need it so if anyone knows the link post it in the comments.

 

kick it on DotNetKicks.com

Legacy Comments


Jeff
2007-10-01
re: SQL Server: Notify client of progress in a long running process
I like it! Very, very useful! Great stuff, Mladen

Bart Czernicki
2007-10-08
re: SQL Server: Notify client of progress in a long running process
Also state is very important here...pass a number high enough and it could kill ur connection or worse crash the server :)

Also this is a nice little trick to keep long processes alive in .NET. Sprocs that run for a long time over a few hours can timeout even if the SqlClient is set to not timeout. Having periodic info sent back to the client can keep the connection alive :)

Mladen
2007-10-08
re: SQL Server: Notify client of progress in a long running process
spot on!

Ben
2007-10-09
re: SQL Server: Notify client of progress in a long running process
How about showing us a way to consume the results on the client (SqlClient)? Possibly hooking it into a progress box?

Mladen
2007-10-09
re: SQL Server: Notify client of progress in a long running process
there's an idea for a new post :)

Dave
2007-10-10
re: SQL Server: Notify client of progress in a long running process
@Ben: This is a technique I've used to asynchronously consume that sort of progress information: http://encosia.com/2007/10/03/easy-incremental-status-updates-for-long-requests/

Mladen
2007-10-10
re: SQL Server: Notify client of progress in a long running process
@Dave:
this is pretty cool. however he meant how to get incremental results from a stored procedure task that takes a while.

There is a way but it's a bit flakey... doesn't always work. i'm trying to figure out when it does. when i do i'll post it.

Dave
2007-10-11
re: SQL Server: Notify client of progress in a long running process
If you used a DataReader, wouldn't the results stream in as they were returned? From there, you could use a while(DataReader.Read()) loop to emit the ProgressUpdate() calls used in my example.

Mladen
2007-10-11
re: SQL Server: Notify client of progress in a long running process
no, it's not that simple. i thought the same thing about the datareader, but it simply doesn't happen.

V
2007-10-30
re: SQL Server: Notify client of progress in a long running process
Actually reader behaves as it must. It reads for first resul, then you call NextResult and it waits for 10 seconds or so returning next resut set.

Any way look at this post http://geekswithblogs.net/mrnat/archive/2004/09/20/11431.aspx, I think it's better way to handle these messages.

Mladen
2007-10-30
re: SQL Server: Notify client of progress in a long running process
hey i've seen this post but his method simply doesn't work for me.
and i've tried changing the connection provider etc...
it works for insert, update and delte statements, but not for selects.

can you maybe provide code that i could try?

Jon H
2010-03-29
re: SQL Server: Notify client of progress in a long running process
This was helpful in flagging down an issue on a cryptic stored procedure.