You are here:Home » tsql » How to invoke Job thorugh SQL script - SQL Server

How to invoke Job thorugh SQL script - SQL Server

We are mostly creating the SQL job and schedule it to run automated. But sometime as per requirement, we need to invoke and run on demand basis, not on a schedule basis. I have done the same thing and the following is the smallest demo ready for you. Let's create one stored procedure and schedule in the job.
USE [Master]
GO

CREATE PROCEDURE JobInvokeProc
AS
BEGIN
SET NOCOUNT ON

WAITFOR DELAY '00:00:15';

END
GO
Now we will create the job for the same and execute this stored procedure in the job. The created script for the job is as follows,
USE [msdb]
GO

/****** Object:  Job [Start_JobInvokeProc]    Script Date: 05/05/2011 07:51:53 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/05/2011 07:51:53 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Start_JobInvokeProc', 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @notify_level_email=0, 
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'No description available.', 
  @category_name=N'[Uncategorized (Local)]', 
  @owner_login_name=N'paresh-PC\paresh', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step_JobInvokeProc]    Script Date: 05/05/2011 07:51:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step_JobInvokeProc', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'exec JobInvokeProc', 
  @database_name=N'master', 
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
Now I have one more we I get from MSDB database and customize it which is used to get the SQL job status information as running or not. This is the script to get the Job status.
USE [master]
GO

CREATE PROCEDURE GetJobStatus     
  @job_Name           varchar(max),     
  @status INT OUTPUT    
AS      
BEGIN      
  DECLARE @can_see_all_running_jobs INT ,    
  @job_type           VARCHAR(12)     ,  -- LOCAL or MULTI-SERVER      
  @owner_login_name   sysname         ,      
  @subsystem          NVARCHAR(40)    ,      
  @category_id        INT             ,      
  @enabled            TINYINT         ,      
  @execution_status   INT             ,  -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions      
  @date_comparator    CHAR(1)         ,  -- >, < or =      
  @date_created       DATETIME        ,      
  @date_last_modified DATETIME        ,      
  @description        NVARCHAR(512)   ,  -- We do a LIKE on this so it can include wildcards      
  @schedule_id        INT                 
     
  set @job_type           = NULL  -- LOCAL or MULTI-SERVER      
  set  @owner_login_name  = NULL      
  set @subsystem          = NULL      
  set @category_id        = NULL      
  set @enabled            = NULL      
  set @execution_status   = NULL  -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions      
  set @date_comparator    = NULL  -- >, < or =      
  set @date_created       = NULL      
  set @date_last_modified = NULL      
  set @description        = NULL  -- We do a LIKE on this so it can include wildcards      
  set @schedule_id        = NULL     
    
  DECLARE @job_owner   sysname      
      
  declare @job_id UNIQUEIDENTIFIER     
  set @job_id = (select job_id from msdb.dbo.sysjobs where name = @job_Name)    
    
  SET NOCOUNT ON      
      
  -- By 'composite' we mean a combination of sysjobs and xp_sqlagent_enum_jobs data.      
  -- This proc should only ever be called by sp_help_job, so we don't verify the      
  -- parameters (sp_help_job has already done this).      
      
  -- Step 1: Create intermediate work tables      
  DECLARE @job_execution_state TABLE (job_id                  UNIQUEIDENTIFIER NOT NULL,      
                                     date_started            INT              NOT NULL,      
                                     time_started            INT              NOT NULL,      
                                     execution_job_status    INT              NOT NULL,      
                                     execution_step_id       INT              NULL,      
                                     execution_step_name     sysname          COLLATE database_default NULL,      
                                     execution_retry_attempt INT              NOT NULL,      
                                     next_run_date           INT              NOT NULL,      
                                     next_run_time           INT              NOT NULL,      
                                     next_run_schedule_id    INT              NOT NULL)      
  DECLARE @filtered_jobs TABLE (job_id                   UNIQUEIDENTIFIER NOT NULL,      
                               date_created             DATETIME         NOT NULL,      
                               date_last_modified       DATETIME         NOT NULL,      
                               current_execution_status INT              NULL,      
                               current_execution_step   sysname          COLLATE database_default NULL,      
                               current_retry_attempt    INT              NULL,      
                               last_run_date            INT              NOT NULL,      
                               last_run_time            INT              NOT NULL,      
                               last_run_outcome         INT              NOT NULL,      
                               next_run_date            INT              NULL,      
                               next_run_time            INT              NULL,      
                               next_run_schedule_id     INT           NULL,      
                               type                     INT              NOT NULL)      
  DECLARE @xp_results TABLE (job_id                UNIQUEIDENTIFIER NOT NULL,      
                            last_run_date         INT              NOT NULL,      
                            last_run_time         INT              NOT NULL,      
                            next_run_date         INT              NOT NULL,      
                            next_run_time         INT              NOT NULL,      
                            next_run_schedule_id  INT              NOT NULL,      
                            requested_to_run      INT              NOT NULL, -- BOOL      
                            request_source        INT              NOT NULL,      
                            request_source_id     sysname          COLLATE database_default NULL,      
                            running               INT              NOT NULL, -- BOOL      
                            current_step          INT              NOT NULL,      
                            current_retry_attempt INT              NOT NULL,      
                            job_state             INT              NOT NULL)      
      
  -- Step 2: Capture job execution information (for local jobs only since that's all SQLServerAgent caches)      
  SELECT @can_see_all_running_jobs = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)      
  IF (@can_see_all_running_jobs = 0)      
  BEGIN      
    SELECT @can_see_all_running_jobs = ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0)      
  END      
  SELECT @job_owner = SUSER_SNAME()      
      
  IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater      
    INSERT INTO @xp_results      
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id      
  ELSE      
    INSERT INTO @xp_results      
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner      
      
  INSERT INTO @job_execution_state      
  SELECT xpr.job_id,      
         xpr.last_run_date,      
         xpr.last_run_time,      
         xpr.job_state,      
         sjs.step_id,      
         sjs.step_name,      
         xpr.current_retry_attempt,      
         xpr.next_run_date,      
         xpr.next_run_time,      
         xpr.next_run_schedule_id      
  FROM @xp_results                          xpr      
       LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),      
       msdb.dbo.sysjobs_view                sjv      
  WHERE (sjv.job_id = xpr.job_id)      
      
  -- Step 3: Filter on everything but dates and job_type      
  IF ((@subsystem        IS NULL) AND      
      (@owner_login_name IS NULL) AND      
      (@enabled          IS NULL) AND      
      (@category_id      IS NULL) AND      
      (@execution_status IS NULL) AND      
      (@description      IS NULL) AND      
      (@job_id           IS NULL))      
  BEGIN      
    -- Optimize for the frequently used case...      
    INSERT INTO @filtered_jobs      
    SELECT sjv.job_id,      
           sjv.date_created,      
           sjv.date_modified,      
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE)      
           CASE ISNULL(jes.execution_step_id, 0)      
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'      
           END,      
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)      
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)      
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)      
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0   -- type placeholder             (we'll fix it up in step 3.4)      
    FROM msdb.dbo.sysjobs_view                sjv      
         LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id)      
    WHERE ((@schedule_id IS NULL)      
      OR   (EXISTS(SELECT *       
                 FROM msdb.dbo.sysjobschedules as js      
                 WHERE (sjv.job_id = js.job_id)      
                   AND (js.schedule_id = @schedule_id))))      
  END      
  ELSE      
  BEGIN      
    INSERT INTO @filtered_jobs      
    SELECT DISTINCT      
           sjv.job_id,      
           sjv.date_created,      
           sjv.date_modified,      
           ISNULL(jes.execution_job_status, 4), -- Will be NULL if the job is non-local or is not in @job_execution_state (NOTE: 4 = STATE_IDLE)      
           CASE ISNULL(jes.execution_step_id, 0)      
             WHEN 0 THEN NULL                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
             ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'      
           END,      
           jes.execution_retry_attempt,         -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0,  -- last_run_date placeholder    (we'll fix it up in step 3.3)      
           0,  -- last_run_time placeholder    (we'll fix it up in step 3.3)      
           5,  -- last_run_outcome placeholder (we'll fix it up in step 3.3 - NOTE: We use 5 just in case there are no jobservers for the job)      
           jes.next_run_date,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_time,                   -- Will be NULL if the job is non-local or is not in @job_execution_state      
           jes.next_run_schedule_id,            -- Will be NULL if the job is non-local or is not in @job_execution_state      
           0   -- type placeholder             (we'll fix it up in step 3.4)      
    FROM msdb.dbo.sysjobs_view                sjv      
         LEFT OUTER JOIN @job_execution_state jes ON (sjv.job_id = jes.job_id)      
         LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON (sjv.job_id = sjs.job_id)      
    WHERE ((@subsystem        IS NULL) OR (sjs.subsystem            = @subsystem))      
      AND ((@owner_login_name IS NULL)       
          OR (sjv.owner_sid            = msdb.dbo.SQLAGENT_SUSER_SID(@owner_login_name)))--force case insensitive comparation for NT users      
      AND ((@enabled          IS NULL) OR (sjv.enabled              = @enabled))      
      AND ((@category_id      IS NULL) OR (sjv.category_id          = @category_id))      
      AND ((@execution_status IS NULL) OR ((@execution_status > 0) AND (jes.execution_job_status = @execution_status))      
                                       OR ((@execution_status = 0) AND (jes.execution_job_status <> 4) AND (jes.execution_job_status <> 5)))      
      AND ((@description      IS NULL) OR (sjv.description       LIKE @description))      
      AND ((@job_id           IS NULL) OR (sjv.job_id               = @job_id))      
      AND ((@schedule_id IS NULL)      
        OR (EXISTS(SELECT *       
                 FROM msdb.dbo.sysjobschedules as js      
                 WHERE (sjv.job_id = js.job_id)      
                   AND (js.schedule_id = @schedule_id))))      
  END      
      
    
    
  -- Step 3.1: Change the execution status of non-local jobs from 'Idle' to 'Unknown'      
  UPDATE @filtered_jobs      
  SET current_execution_status = NULL      
  WHERE (current_execution_status = 4)      
    AND (job_id IN (SELECT job_id      
                    FROM msdb.dbo.sysjobservers      
                    WHERE (server_id <> 0)))      
      
  -- Step 3.2: Check that if the user asked to see idle jobs that we still have some.      
  --           If we don't have any then the query should return no rows.      
  IF (@execution_status = 4) AND      
     (NOT EXISTS (SELECT *      
                  FROM @filtered_jobs      
                  WHERE (current_execution_status = 4)))      
  BEGIN      
    DELETE FROM @filtered_jobs      
  END      
      
  -- Step 3.3: Populate the last run date/time/outcome [this is a little tricky since for      
  --           multi-server jobs there are multiple last run details in sysjobservers, so      
  --           we simply choose the most recent].      
  IF (EXISTS (SELECT *      
              FROM msdb.dbo.systargetservers))      
  BEGIN      
    UPDATE @filtered_jobs      
    SET last_run_date = sjs.last_run_date,      
        last_run_time = sjs.last_run_time,      
        last_run_outcome = sjs.last_run_outcome      
    FROM @filtered_jobs fj,      
         msdb.dbo.sysjobservers sjs      
    WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =      
           (SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)      
            FROM msdb.dbo.sysjobservers      
            WHERE (job_id = sjs.job_id))      
      AND (fj.job_id = sjs.job_id)      
  END      
  ELSE      
  BEGIN      
    UPDATE @filtered_jobs      
    SET last_run_date = sjs.last_run_date,      
        last_run_time = sjs.last_run_time,      
        last_run_outcome = sjs.last_run_outcome      
    FROM @filtered_jobs         fj,      
         msdb.dbo.sysjobservers sjs      
    WHERE (fj.job_id = sjs.job_id)      
  END      
      
  -- Step 3.4 : Set the type of the job to local (1) or multi-server (2)      
  --            NOTE: If the job has no jobservers then it wil have a type of 0 meaning      
  --                  unknown.  This is marginally inconsistent with the behaviour of      
  --                  defaulting the category of a new job to [Uncategorized (Local)], but      
  --                  prevents incompletely defined jobs from erroneously showing up as valid      
  --                  local jobs.      
  UPDATE @filtered_jobs      
  SET type = 1 -- LOCAL      
  FROM @filtered_jobs         fj,      
       msdb.dbo.sysjobservers sjs      
  WHERE (fj.job_id = sjs.job_id)      
    AND (server_id = 0)      
  UPDATE @filtered_jobs      
  SET type = 2 -- MULTI-SERVER      
  FROM @filtered_jobs         fj,      
       msdb.dbo.sysjobservers sjs      
  WHERE (fj.job_id = sjs.job_id)      
    AND (server_id <> 0)      
      
  -- Step 4: Filter on job_type      
  IF (@job_type IS NOT NULL)      
  BEGIN      
    IF (UPPER(@job_type collate SQL_Latin1_General_CP1_CS_AS) = 'LOCAL')      
      DELETE FROM @filtered_jobs      
      WHERE (type <> 1) -- IE. Delete all the non-local jobs      
    IF (UPPER(@job_type collate SQL_Latin1_General_CP1_CS_AS) = 'MULTI-SERVER')      
      DELETE FROM @filtered_jobs      
      WHERE (type <> 2) -- IE. Delete all the non-multi-server jobs      
  END      
      
  -- Step 5: Filter on dates      
  IF (@date_comparator IS NOT NULL)      
  BEGIN      
    IF (@date_created IS NOT NULL)      
    BEGIN      
      IF (@date_comparator = '=')      
        DELETE FROM @filtered_jobs WHERE (date_created <> @date_created)      
      IF (@date_comparator = '>')      
        DELETE FROM @filtered_jobs WHERE (date_created <= @date_created)      
      IF (@date_comparator = '<')      
        DELETE FROM @filtered_jobs WHERE (date_created >= @date_created)      
    END      
    IF (@date_last_modified IS NOT NULL)      
    BEGIN      
      IF (@date_comparator = '=')      
        DELETE FROM @filtered_jobs WHERE (date_last_modified <> @date_last_modified)      
      IF (@date_comparator = '>')      
        DELETE FROM @filtered_jobs WHERE (date_last_modified <= @date_last_modified)      
      IF (@date_comparator = '<')      
        DELETE FROM @filtered_jobs WHERE (date_last_modified >= @date_last_modified)      
    END      
  END      
      
  -- Return the result set (NOTE: No filtering occurs here)      
  SELECT @status = ISNULL(fj.current_execution_status, 0)           -- This column will be NULL if the job is non-local      
            
  FROM @filtered_jobs fj      
       LEFT OUTER JOIN msdb.dbo.sysjobs_view  sjv ON (fj.job_id = sjv.job_id)      
       LEFT OUTER JOIN msdb.dbo.sysoperators  so1 ON (sjv.notify_email_operator_id = so1.id)      
       LEFT OUTER JOIN msdb.dbo.sysoperators  so2 ON (sjv.notify_netsend_operator_id = so2.id)      
       LEFT OUTER JOIN msdb.dbo.sysoperators  so3 ON (sjv.notify_page_operator_id = so3.id)      
       LEFT OUTER JOIN msdb.dbo.syscategories sc  ON (sjv.category_id = sc.category_id)      
  ORDER BY sjv.job_id      
      
return @status    
END      
  
GO  
Now will run the script to invoke the SQL job through the analyzer and will see the SQL job status. Let's run the below code and see the output,
USE [master]
GO

-- Get the job status before invoke the job 
DECLARE @status INT  
DECLARE @JobName VARCHAR(500)  

SET @status = 0 
set @JobName = 'Start_JobInvokeProc'

EXEC GetJobStatus
@JobName,  
@status OUTPUT  

SELECT @status AS JobStatus 
------------------------------------------
-- Run this query to invoke the job
EXEC msdb.dbo.Sp_start_job @JobName  
------------------------------------------
-- Get the job status after invoke the job
WAITFOR DELAY '00:00:3';

SET @status = 0 
set @JobName = 'Start_JobInvokeProc'

EXEC GetJobStatus  
@JobName,  
@status OUTPUT  

SELECT @status AS JobStatus 
GO

When you run the script again while executing the job, It will raise the error while invokes it again.

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Start_JobInvokeProc (from User paresh-PC\paresh) refused because the job is already running from a request by User paresh-PC\paresh.


So better option is we need to check the job status first, then we should invoke. The revised script is,
USE [master]
GO

/* Get the job status before invoke the job */
DECLARE @status INT  
DECLARE @JobName VARCHAR(500)  

SET @status = 0 
set @JobName = 'Start_JobInvokeProc'

EXEC GetJobStatus
@JobName,  
@status OUTPUT  

SELECT @status AS JobStatus 

-- Added condition here to check the job is already running or not.
IF (@status <> 1)
BEGIN
/* Run this query to invoke the job*/
EXEC msdb.dbo.Sp_start_job @JobName  
END
GO