I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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

Print | posted on Monday, October 01, 2007 6:22 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

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

I like it! Very, very useful! Great stuff, Mladen
10/1/2007 8:34 PM | Jeff
Gravatar

# 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 :)
10/8/2007 6:32 AM | Bart Czernicki
Gravatar

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

spot on!
10/8/2007 7:48 AM | Mladen
Gravatar

# 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?
10/9/2007 5:31 AM | Ben
Gravatar

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

there's an idea for a new post :)
10/9/2007 10:55 AM | Mladen
Gravatar

# 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/
10/10/2007 9:46 PM | Dave
Gravatar

# 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.
10/10/2007 9:50 PM | Mladen
Gravatar

# 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.
10/11/2007 1:08 AM | Dave
Gravatar

# 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.
10/11/2007 10:31 AM | Mladen
Gravatar

# 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.
10/30/2007 8:26 AM | V
Gravatar

# 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?
10/30/2007 11:17 AM | Mladen
Gravatar

# 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.
3/29/2010 9:40 PM | Jon H
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET