Stored Procedure Status

[kutoose]

How will I know the status of a stored procedure whether it has been completed or not, or it is executing, at any point of time ?
The issue is a stored procedure is run at a specific time and will take some time to run. I need to run reports after the stored procedure is run. As of now I cannot figure out the stored procedure has finished execution or not.

Thanks...

 

[osjohnm]

Hi

You mentioned that the stored procedure is executed at a specific time. I assume that this is automated and is executed through a sql job. If so go to the job in enterprise manager, right click, properties, notifications.
Then if SQL Agent Mail is configured then get the job to email on completion or success or get the job to net send on completion or success.

I'm not sure if there is a way to see the current status of a procedure.

Hope this helps

John

 

[kutoose]

Thanks....
I was looking to automate the report process. An email will solve the problem if I am manually running the report, but I dont intent to do so.
Is there any system stored procedure which will give the status of any stored procedure, with an input parameter for the system stored procedure be the stored procedure name ?

Just wondering...

Thanks !!!

 

[osjohnm]

There isn't a system proc that does what you need that I know of. I had a brief look through BOL but haven't come across anything.

If the reports are also going to be automated will they also be kicked off by a job?
If so you can add the proc that the reports are waiting for and the one or sql script that starts off the reports into the same jobs different steps. Step 1 would be the original procedure, step 2 would be the reports. On completion of step 1 successfully you can get the job to move to step 2. If Step 1 fails it won't execute step 2 and then the job can notify you that the job failed.

Does this make sense?

Hope this helps

John

 

[miq]

hi,
there can be different techniques to achieve desired results.

use a job with steps. step 1 would be to run sp. Process next step on completion.

use return(output) parameter within sp. set 0(zero) as the output value when their is no problem with sp. anythng other could be treated as error value.

stored procedure last line could be the one which activates job.

There are always a way and for you there are many more not discussed here.

bye
miq