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
