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.
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 CREATE PROCEDURE JobInvokeProc AS BEGIN SET NOCOUNT ON WAITFOR DELAY '00:00:15'; END GONow 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: GONow 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 GONow 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
Comments
Post a Comment