|
|
Posted by Erland Sommarskog on 10/07/51 11:52
Zvonko (zvonko_NOSPAM_@velkat.net) writes:
> I am wondering how to do this. Let's say that you execute a long
> running insert or whatever stored procedure from your application. It
> runs for a while (minute or two) and it runs on the SQL Server. So your
> application doesn't know it's status. Is it possible to get the status
> of the work and how long it will take, so you can inform your user by
> displaying a progressbar or something. The problem I have is to get an
> information about the status of procedure from the SQL Server and not
> how to show a progressBar.
In general terms it's not possible.
For a specific procedure it could be possible depending on what it does.
A simple case is a procedure that runs a cursor. In this case you could
send informative messages with RAISERROR WITH NOWAIT. (Not PRINT, because
PRINT gets buffered.) Then again, the end user is better served if you
skip the cursor and perform the processing directly.
A similar approach could be applied to procedures that performs several
updates, although chances are good that all the time is spent on a
specific statement.
And for a procedure which consists of a single statement it is about
impossible. Possibly, you could have a parallel thread which runs a
query with NOLOCK that determins how many rows have been updated/inserted.
But if what's takes time is to locate the rows, this is not a very
meaningful operation.
Add to this that a query may be delayed to blocking, in which case you
have no clue how long time it will take.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|