In this article, we will discuss different methods to execute SQL batch processes from an external scheduler, such as Control-M. While there are multiple ways to achieve this, we will focus on using SQL Server jobs and explain why it is the preferred method in certain scenarios.
Why Use SQL Server Jobs?
When deciding on the method to execute batch processes, we considered the following factors:
- We wanted to avoid changing the schema of our application, as all our batch processes were already scheduled through SQL Server.
- Using OSQL was not feasible because some of our batch processes relied on extended stored procedures (xp_cmdshell & Xp_logevent), which can only be executed by members of the sysadmin fixed server role. As Control-M was an external batch scheduler, it did not have sysadmin privileges.
- DTS (Data Transformation Services) was not a viable option either, as it would require modifying the schema of the batch processes.
Considering these factors, we decided to use SQL Server jobs for executing our batch processes.
Triggering a SQL Server Job from an External Agent
To trigger a SQL Server job from an external agent and retrieve the job status, we can use a stored procedure. Here is an example of such a procedure:
CREATE PROCEDURE dbo.uspGetControlMjobstatus
(
@Jobname varchar(50), -- Job that needs to be started
@Jobretstatus int OUTPUT -- Returns the output status of the Job
)
AS
BEGIN
-- Procedure logic goes here
END
The procedure starts the specified job and then queries the sysjobserver at regular intervals to check if the job execution has been completed or not. It also returns the run status of the SQL Server job.
The procedure includes configurable variables, such as wait duration and wait delay duration, which can be adjusted based on the specific requirements of the job.
Conclusion
The above stored procedure has proven to be effective in triggering SQL Server jobs from external agents. By using SQL Server jobs, we were able to execute batch processes without changing the schema of our application. We would like to acknowledge the contributions of our colleagues Rajesh Khakhar and Sridhar Aagamuri in developing this solution.
Thank you for reading!