SQL jobs that run sp_start_job do not wait for MSSQL Agent job to complete

Products

• Autosys Workload Automation

Issue/Introduction

An AutoSys SQL or DBPROC job executes the sp_start_job stored procedure to start an MSSQL Agent job. However, the AutoSys job completes within a second or two and does not wait for the completion of the MSSQL Agent job.

 

Environment

Release : 11.3.6/12.0/12.0.01/12.1

Cause

The completion of an AutoSys SQL/DBPROC job that executes a stored procedure is only based on the completion of the stored procedure itself. The sp_start_job stored procedure simply instructs the MSSQL Agent to start a job immediately. It does not monitor the status and wait for the completion of the MSSQL Agent job to exit. It exits as soon as it gets an acknowledgement that the job was successfully started. Therefore, the AutoSys job will complete at that time as well.

Resolution

Starting with AutoSys 11.3.6 SP8, a new job type was introduced that provides a direct integration with MSSQL Agent jobs. This would allow an AutoSys job to launch an MSSQL Agent job directly and complete based on the completion of the MSSQL Agent job. Documentation for this job type can be found here...

https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/autosys-workload-automation/12-1/scheduling/ae-scheduling/sql-server-agent-jobs.html

This job type requires the WA Agent for Agent for Microsoft SQL Server