Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Executing SQL Server Jobs From An External Scheduler

By Sureshkumar Ramakrishnan,

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.

Total article views: 13726 | Views in the last 30 days: 18
 
Related Articles
FORUM

How to declare parameters inside "select"?

select asset.asset_name asset_name (varchar(100))?

FORUM

In store procedure where i need to declare "Declare @SQL1 varchar(800)" to access within Begin statement

I have a complex store procedure and I need to take print of output from store procedure that's why ...

FORUM

Different behaviour of SQL Server when setting varchar size

When declaring parameters of varchar, setting size gives different query results

FORUM

Difference between varchar(max) and varchar(8000)

Difference between varchar(max) and varchar(8000)

ARTICLE

Beginning SQL Server 2000 Programming

A review of Beginning SQL Server 2000 Programming

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones