HowTo: Execute a SQL Agent Job via a Stored Procedure

Here’s a way your application can kick off a long running SQL process that is wrapped up in a SQL Agent Job. Running a job via a stored procedure this way would allow your application not to block for the long running process and instead return a status message of the current job state immediately.

 
CREATE PROCEDURE dbo.ExecuteSqlAgentJob
(
  @sql_agent_job_name SYSNAME
)
AS
 
SET NOCOUNT ON
 
IF NOT EXISTS(     
  SELECT 1 
  FROM msdb.dbo.sysjobs_view AS job  
  JOIN msdb.dbo.sysjobactivity AS activity 
    ON job.job_id = activity.job_id 
  WHERE  
    activity.run_Requested_date IS NOT NULL 
    AND activity.stop_execution_date IS NULL
    AND job.name = @sql_agent_job_name 
) 
BEGIN
  EXEC msdb.dbo.sp_start_job @sql_agent_job_name; 
  SELECT 'Started job ''' + @sql_agent_job_name + ''''; 
END 
ELSE 
BEGIN 
  SELECT 'Job ''' + @sql_agent_job_name + ''' has already started '; 
END 
 
GO

Hope this helps!

This entry was posted in MS SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *