Feedback for users during stored procedure execution

Feedback for users during stored procedure execution

Post by Phil V » Fri, 20 Sep 2002 23:51:12



I have an application that uses Access 2000 as a front-end
and SQL 2000 as the server.  I have a process that updates
around 75,000 records.  The client calls a stored proc.
and all processing is done on the server.  Is there any
way to provide feedback such as estimated time remaining
or display a progress bar for the user.  Any help would be
appreciated.

Phil

 
 
 

Feedback for users during stored procedure execution

Post by Andrew J. Kell » Sat, 21 Sep 2002 00:08:29


Phil,

A stored procedure basically doesn't return anything until done.  There is
no real way to get hooks or events into or from a sp.  You might consider
breaking up the updates and calling the sp say 10 times from the front end
with a parameter on how much to update at a time and updating the progress
after each call.  Updating the rows in smaller batches may even be faster
than doing them all at once depending on your hardware and use of the data.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:> I have an application that uses Access 2000 as a front-end
> and SQL 2000 as the server.  I have a process that updates
> around 75,000 records.  The client calls a stored proc.
> and all processing is done on the server.  Is there any
> way to provide feedback such as estimated time remaining
> or display a progress bar for the user.  Any help would be
> appreciated.

> Phil


 
 
 

Feedback for users during stored procedure execution

Post by Dan Guzma » Sat, 21 Sep 2002 00:10:34


You can't really get progress of individual SQL statements.  The best
you can do is monitor progress on another database connection by reading
uncommitted data, which I don't recommend.

However, you can include RAISERROR ... NOWAIT statements provide
feedback in a multi-step proc.  With ADO, this requires that you to
handle the InfoMessage event to get the progress messages.  I don't know
much about Access so I can't provide a code example.  Sample proc:

CREATE PROC usp_UpdateStuff
AS
RAISERROR ('usp_UpdateStuff starting', 0, 1) WITH NOWAIT
RAISERROR ('Table1 update starting', 0, 1) WITH NOWAIT
UPDATE Table1 ...
RAISERROR ('Table1 update completed', 0, 1) WITH NOWAIT
RAISERROR ('Table2 update starting', 0, 1) WITH NOWAIT
UPDATE Table2 ...
RAISERROR ('Table2 update completed', 0, 1) WITH NOWAIT
RAISERROR ('usp_UpdateStuff completed', 0, 1) WITH NOWAIT
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------


Quote:> I have an application that uses Access 2000 as a front-end
> and SQL 2000 as the server.  I have a process that updates
> around 75,000 records.  The client calls a stored proc.
> and all processing is done on the server.  Is there any
> way to provide feedback such as estimated time remaining
> or display a progress bar for the user.  Any help would be
> appreciated.

> Phil