February 17, 2004 at 9:51 am
Anyone have any coding technigues that will let me know if a job is running. I want to start a job and then wait for it to end. So I need a signal that it is ended. How would I get that? It is avaialbe in sp_help_job but there are nested temporary tables that will not allow me to access the column called current_execution_status by creating a temporary table.
It would be really nice it there was a IsItRunning function that returns a bit that says yes or no. (but no temporary tables in functions).
October 5, 2004 at 2:31 pm
OK I wrote a function that returns the information. Thanks to my colleague the trick was OPENROWSET.
Here is the function
CREATE FUNCTION dbo.FN_JobCurrentExecutionStatus (@jobname sysname)
RETURNS int
/***********************************************************************************************************
Purpose: This function returns the current_execution_status
The results mean
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
Variables: @jobname - the name of the job
Author: Steve
Tested on: SQL Server 8.0, Service Pack 3a
Date created: October 5, 2005
Change History:
mm/dd/yyyy Narrative to describe why the change was made
**************************************************************************************************************/AS
BEGIN
DECLARE @currentstatus int
SELECT @currentstatus = current_execution_status FROM OPENROWSET(N'SQLOLEDB','SERVER=<servername>;UID=<userid>;PWD=<password>','set fmtonly off EXECUTE [msdb].[dbo].[sp_help_job] @job_name = ''<jobname>'', @job_aspect = ''JOB'' set fmtonly on')
RETURN (@currentstatus )
END
Any comments on this code would be appreciated.
Thanks
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply