-- User Defined Function: dbo.fn_DMOJobStatus
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_DMOJobStatus]') 
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_DMOJobStatus]
GO

go
CREATE function dbo.fn_DMOJobStatus
 (@pJobId varchar(50) = null,
  @pJobName varchar(80) = null)
returns 
 @list table 
  ( JobId varchar(50)
  , JobName sysname
  , JobStatus int
  , JobStatusConstant varchar(50))
as
 begin
  /*
  -- return column big enough to hold character representation of a guid 
 
  -- test script
  -- tested with two jobs on server, "job 1" and "job 2"

  declare @jobName varchar(50)
  declare @JobStatus int
  declare @JobStatusConstant varchar(50)
  declare @SQL$ nvarchar(200)
  set @jobName = 'job 2'

  -- get a table of all jobs with status info
  select * from admin.dbo.fn_DMOJobStatus(null,null)

  -- get the decoded status of one job
  select JobStatusConstant from admin.dbo.fn_DMOJobStatus(null,@JobName)
  select @@rowcount as [rowcount incremented if job found]

  -- invalid parameter (correct datatype)
  select JobStatusConstant from admin.dbo.fn_DMOJobStatus('bogus parameter',null)
  select @@rowcount as [rowcount not incremented if bogus parameter]
 
  -- invalid parameter (wrong datatype)
  select JobStatusConstant from admin.dbo.fn_DMOJobStatus(null,0x123)
  select @@rowcount as [rowcount not incremented if bogus parameter]

  -- status of a job (with dynamic column header)
  set @SQL$ = 'select JobStatus as [' + @JobName + ' status] from   admin.dbo.fn_DMOJobStatus(null,@JobName)'
  exec sp_executesql @SQL$, N'@JobName varchar(50)', @JobName

  -- conditional branching by status
  if exists (select 1 
             from admin.dbo.fn_DMOJobStatus(null,@JobName) 
             where jobStatus not in (0,4))
   print 'job "' + @JobName + '" is active'
  else
   print 'job "' + @JobName + '" is not active'

  */

  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 COM/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 COM/DMO database connection 
  exec @hr = master.dbo.sp_OAMethod @DMOServer,'DisConnect'

  -- this may clobber any other jobs currently using OA environment 
  exec @hr = master.dbo.sp_OADestroy @DMOServer

  return
 end
GO