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.
- Using OSQL
- Using DTS package
- 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.
- We did not want to change the schema (all of our batch processes were scheduled through SQL Server) of our Application.
- 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.
- 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.