This article describes about executing SQL server jobs from external Scheduler (e.g. Control-M). There are different ways from which one can execute a SQL batch process from external Scheduler.

1. Using OSQL
2. Using DTS package
3. Using SQL Server Jobs

All of the methods mentioned above can be used to execute batch process from any external batch scheduler and all of them are effective. We decided to use SQL Server job for execution of the batch process instead of using OSQL & DTS for the following reasons.

1. We did not want to change the schema (all of our batch processes were scheduled through SQL Server) of our Application.
2. The difficulty using OSQL was that some of the batch process we had, were using (xp_cmdshell & Xp_logevent). Only members of the sysadmin fixed server role can execute this extended stored procedure. In our case as Control-M was an external batch scheduler it was not provided with the sysadmin privileges.
3. DTS was not a feasible option because we did not want to change the schema of the batch process.

We could start the SQL server job from a batch file, but SQL server doesn’t have a procedure which informs as to whether a Batch has been executed successfully or not. So, I wrote the following procedure to trigger a SQL server job from external agent and return the Job status. The logic for the procedure is pretty simple. Firstly it starts the job and then queries the sysjobserver every definite configurable period to check if the job execution has been completed or not.

CREATE Procedure dbo.uspGetControlMjobstatus
*********************************************************************************
*    Name        : [dbo].[uspgetcontrolmjobstatus]
*    Purpose     : This stored procedure is used for starting and returning the status
*    Effects     : None.
*    Inputs      : The SQL job that has to be triggered and executed by ControlM
*    Returns     : The Run status of the SQL server Job 
/********************************************************************************
*/ ( @Jobname varchar (50), --Job that needs to be started 
   @Jobretstatus int Output --Returns the output status of the Job Called   
)

AS  
-- Declaration of Variables used in the Stored Procedure
Declare @Jobid  uniqueidentifier        --Hold the Job Unique identifier id
Declare @lastrundate  varchar(10)       --Hold the current date of runnning 
Declare @lastruntime  varchar(8)        --Hold the time before the job is started
Declare @reccnt  int                    --Hold the Record 
Declare @jobresult  int                 --Hold the Run Outcome of the triggered SQL server Job
Declare @retjobcode  int          
Declare @waitduration  int              --Hold the minimum wait duration of the SQL Job 
Declare @waitdelayduration  varchar(9)  --Hold the wait period for the Job
Declare @strmessage  varchar(500)       --Hold the Error Message in event of failure :Only used for Reference
Declare @interrcode  int                --Error Code for any errors 
Declare @strdbname  varchar(20)         --Hold the Database Name 

SET NOCOUNT OFF

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Begin
--Variable Initilization
Select @strdbname= db_name()   --Retrieve the Database Name 
Select @Jobretstatus=8         --Job has not yet been in the Run mode 
Select @reccnt= - 1            --No records obtained from sysjobserver
Select @jobresult=-1           --No Job processing result obtained
 
--Used to retrieve the Jobid of the running Job.
Select @Jobid=job_id
 from msdb.dbo.sysjobs (nolock)
 where name=@Jobname

select  @interrcode=@@error

if  @interrcode <>0 or (@Jobid IS NULL)
 Begin
   SET @Jobretstatus=2
   SET @strmessage='Job ' + @Jobname + ' does not exist in the SQL Server for Database ' + @strdbname 
   Goto Errhandler
end
 
-- Configurable Waitdelay period obtained based on the Minimum required running time of the Job
Select @waitduration= isnull(min( run_duration),0)
 from msdb.dbo.sysjobhistory (nolock)
 where job_id =@Jobid and run_status=1

select @interrcode=@@error
if  @interrcode <>0 
 Begin
  SET @Jobretstatus=3
  SET @strmessage='Job ' + @Jobname + ' does not exist in the SQL Server SysJobhistory for Database ' 
      +  @strdbname 
  Goto Errhandler
 End 

If (@@rowcount=0) or (@waitduration=0)
 -- This implies that the Job has never run & /never ended successfully 
 Begin 
  --Configuring the minimum waitdelay for 10 seconds 
  Set @waitdelayduration='000:00:10' 
 End
Else
 Begin
   If @waitduration>=60 
    Begin
      --Wait duration maximum has been set up for 59 seconds.
      set @waitduration=59 
      Set @waitdelayduration='000:00:59' 
    End
   Else
    Begin
     --Configure Waitdelay based on thw minimum wait duration. 
     Set @waitdelayduration='000:00:' +  RIGHT('00' + cast(@waitduration as varchar(2)),2)
    End
 End

Set @lastrundate= Replace(cast(convert(varchar(10), getdate(),121) as varchar(10)),'-','')
--Getting the Rundate
Set @lastruntime= Replace(cast(convert(char(8),getdate(),8) as varchar(8)),':','') 
--Getting the Runtime
--Instructs SQL Server Agent to execute a job immediately.
Exec @retjobcode =msdb.dbo.sp_start_job @Jobname

select @interrcode=@@error
If ( @interrcode<>0 ) or ( @retjobcode<>0)
 Begin
  SET @Jobretstatus=4
  SET @strmessage='Job ' + @Jobname + 'has failed to start for Database ' +  @strdbname  
      + ' Check SQL Event Log'
  Goto Errhandler
 End 

Checkifjobcomplete:

Begin
 Begin
   --Querying the Sysjobserver to check whether the job has been completed or not
   Select @reccnt ='1',@jobresult=b.last_run_outcome
    from msdb.dbo.sysjobservers b (nolock)
    where  b.job_id=@Jobid
    and convert(varchar(12),last_run_date,121)>=@lastrundate
    and last_run_time>=@lastruntime 
 End
End

select @interrcode=@@error
if @interrcode <>0 
 Begin
  SET @Jobretstatus=6
  SET @strmessage='Job ' + @Jobname + ' does not exist in the SQL Server sysserver for Database ' 
      +  @strdbname 
  Goto Errhandler
 End 

If (@reccnt= - 1) and (@jobresult=-1)
 Begin
  WAITFOR DELAY @waitdelayduration --Waiting for the specified wait duration specified  
  Goto Checkifjobcomplete
  --Requering the Sysjobserver to get the status of the Job
 End 

if @reccnt=1    ---Implies job has been completed 
 Begin
   if @jobresult = 1
    --Return Success code
    Set @Jobretstatus =0                  
   Else
    --Return Failure code
    Set @Jobretstatus = 1
 End 

Errhandler:

-- Throw the Custom Exception
SET  @strMessage  = @strMessage   + SPACE(2) + 'RetStat:' + SPACE(2) + CAST(@Jobretstatus AS varchar) 
--Write to the EventLog for any failure 
EXEC master..xp_logevent 60000, @strMessage, informational

Return 

ExitProc:

End

In the above procedure the waitdelayduration has been made configurable so that you can re-query the database based on the actual execution time of the job. The above stored procedure had been very useful and effective in triggering SQL Server jobs from external agents.

Lastly, I would like to thank my colleague Rajesh Khakhar and Sridhar aagamuri for their co-operation in

completing this.