tracking Execution ststus of stored procedure.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

tracking Execution ststus of stored procedure.

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-01-20 : 02:16:40

Hi,
I am having Sp which includes 3 query statement in it. how to track from application that 1st query execution is done, 2nd query execution is done likes that?

thanks in advance
subha

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 06:33:25

I know of no simple way to do this. A stored procedure is executed in one BATCH, which means, you are sending all the statements in the stored proc as one unit of work to SQL server and then, the server does its thing anyway it pleases as long as logically it is doing the right thing.

There may be metadata (DMV's etc.) that you can examine from a separate connection to see the progress; if there are simple ways to examine those reliably, hopefully someone on the forum will post it.

One option, if you are willing to modify the stored proc, and assuming it is even logically possible, is to make the stored proc into three separate stored procedures and call them one after the other from the client.

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 09:19:05

"how to track"

Can you clarify that?

Do you want something INSERTED into a "Logging table"? - INSERT INTO MyLoggingTable will do, but that is subject to ROLLBACK of the current transaction ...

Or just an output on your screen to show that Stage One has completed ? - This will do for that: RAISERROR(N'MyMessage', 10, 1) WITH NOWAIT

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-01-21 : 09:13:28

first query is for insert, next one is for select and last is for update.

is that raiserror statement will hit the application and run for the next statement also?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-21 : 09:38:23

quote:
Originally posted by subhaoviya

first query is for insert, next one is for select and last is for update.

is that raiserror statement will hit the application and run for the next statement also?

I doubt it will, because if it is seen as an exception by the client code, the client code will go to the exception handler if there is one, or unwind its way up the call stack.

I have not experimented with it, but easy enough to try if you have the client code that calls a stored proc. For example, you can try it with this stored procedure, where I am inserting a ten second delay between each select statement:

CREATE PROCEDURE dbo.TestProc
AS
BEGIN
	SELECT 1;
	RAISERROR('First command completed',10,1) WITH NOWAIT;

	WAITFOR DELAY '00:00:10';
	SELECT 2;
	RAISERROR('Second command completed',10,1) WITH NOWAIT;

	WAITFOR DELAY '00:00:10';
	SELECT 3;
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources