Is Job Running?
-
Posted on March 13, 2010 by Derek Dieter
-
0
The following SQL Server script works to determine if a particular job is currently running. This feature is nice to have if you do not want jobs to step over one another. I frequently will use this script as the first step of the job. If it is currently running, then the step will raise an error and not proceed to the next step.
[cc lang=”sql”]
sp_configure ‘Ad Hoc Distributed Queries’,1
RECONFIGURE WITH OVERRIDE
GO
DECLARE @ExecutionStatus INT
DECLARE @JobName varchar(100) = ‘%yourjobnamehere%’
SELECT name, current_execution_status, job_id
INTO #Jobs
FROM OPENROWSET(‘SQLNCLI’, ‘server=(local);trusted_connection=yes’,
‘set fmtonly off exec msdb.dbo.sp_help_job’)
SELECT @ExecutionStatus = current_execution_status
FROM #Jobs
WHERE job_id = (SELECT sj.job_id
FROM msdb.dbo.sysjobs sj
WHERE name like @JobName)
IF @ExecutionStatus != 1
BEGIN
RAISERROR (‘Job Is Not Running’,
16,
1
)
END
[/cc]
The only caveat to this method, which is the only one I know, is that you need to enable ‘Ad Hoc Distributed Queries’. While I don’t consider this a harmful change, it may not be allowed unless you have dbo permissions on the database.
- Comments (RSS)
- Trackback
- Permalink
This solution runs beautifully on a normal MSSQL server. But now I’m using RDS from Amazon, and the security is too limited. I’ve not been able to even execute “select * FROM msdb.dbo.sysjobactivity ” with a login with the “most complete security profile available”.
Do you have a way to retrieve on a table/variable the status of a job (i.e. if it is running or not)) on RDS for a mortal user?
