use admin drop function dbo.fn_DMOJobStatus go CREATE function dbo.fn_DMOJobStatus (@pJobId varchar(50) = null, @pJobName varchar(80) = null) returns @list table (JobId varchar(50), -- big enough to hold character representation of a guid JobName sysname, JobStatus int, JobStatusConstant varchar(50)) as /******************************************************************************************************* * admin.dbo.fn_DMOJobStatus * Creator: Bill Wunder * * Date: 3-13-2003 * * Project: utility * * Description: get status info on a job using the automation stored procedures * * Usage: if (select JobStatusConstant from fn_DMOJobStatus (null,'test')) = 'SQLDMOJobExecution_Executing' print 'it''s running!' * Modifications: * Developer Name Date Brief Description * ------------------ -------- ------------------------------------------------------------ * ********************************************************************************************************/ begin declare @DMOServer int, @DMOCmd varchar(255), @hr int, @JobCount int, @CurJobNbr int, @JobId varchar(50), @JobName varchar(255), @CurrentRunStatus int, @ServerName varchar(15) -- open an in-process DMO connection to this server set @DMOCmd = 'SQLDMO.SQLServer' exec @hr = master.dbo.sp_OACreate @DMOCmd, @DMOServer OUT -- set the security context to integrated set @DMOCmd = 'loginSecure' exec @hr = master.dbo.sp_OASetProperty @DMOServer,@DMOCmd,1 -- connect to the specified server set @DMOCmd = 'Connect' -- parameter is a memory leak so make it as small as possible (Q293636) set @ServerName = cast(@@serverName as varchar(20)) exec @hr = master.dbo.sp_OAMethod @DMOServer,@DMOCmd,NULL,@ServerName -- Jobs select @DMOCmd = 'JobServer.Jobs.Count' exec @hr = master.dbo.sp_OAGetProperty @DMOServer, @DMOCmd, @JobCount OUT set @CurJobNbr = 1 while @CurJobNbr <= @JobCount begin select @DMOCmd = 'JobServer.Jobs.Item(' + cast(@CurJobNbr as varchar(10)) + ').JobId' exec @hr = master.dbo.sp_OAGetProperty @DMOServer, @DMOCmd, @JobId OUT select @DMOCmd = 'JobServer.Jobs.Item(' + cast(@CurJobNbr as varchar(10)) + ').Name' exec @hr = master.dbo.sp_OAGetProperty @DMOServer, @DMOCmd, @JobName OUT select @DMOCmd = 'JobServer.Jobs.Item(' + cast(@CurJobNbr as varchar(10)) + ').CurrentRunStatus' exec @hr = master.dbo.sp_OAGetProperty @DMOServer, @DMOCmd, @CurrentRunStatus OUT insert @list (JobId, JobName, JobStatus, JobStatusConstant) select '0x' + @JobId, @JobName, @CurrentRunStatus, case when @CurrentRunStatus = 1 then 'SQLDMOJobExecution_Executing' when @CurrentRunStatus = 2 then 'SQLDMOJobExecution_WaitingForWorkerThread' when @CurrentRunStatus = 3 then 'SQLDMOJobExecution_BetweenRetries' when @CurrentRunStatus = 4 then 'SQLDMOJobExecution_Idle' when @CurrentRunStatus = 5 then 'SQLDMOJobExecution_Suspended' when @CurrentRunStatus = 6 then 'SQLDMOJobExecution_WaitingForStepToFinish' when @CurrentRunStatus = 7 then 'SQLDMOJobExecution_PerformingCompletionActions' else 'SQLDMOJobExecution_Unknown' -- should only be 0 end where (@JobId = @pJobId or @pJobId is null) and (@JobName = @pJobName or @pJobName is null) select @CurJobNbr = @CurJobNbr + 1 end -- close and cleanup the database connection exec @hr = master.dbo.sp_OAMethod @DMOServer,'DisConnect' -- careful, this may clobber any other jobs currently using OA environment exec @hr = master.dbo.sp_OADestroy @DMOServer return end GO