Start & monitor a job to completion using T-SQL?
-
Is there a way to start a SQL Server 2000 job and then monitor it to completion? Has some one already developed a similar method that I may use?
My company is implementing an open systems job scheduling product. I want to have this scheduling product start a SQL Server jobs and then have it report on wheather or not the SQL Server job was successful. I am currently developing my own SQL code that uses the msdb..sp_start_job and then monitors the job by selecting data from the msdb..sysjobhistory table. The problem with this is when the SQL Server job has only 1 jobstep, a record does not appear in the sysjobhistory table until the job completes. A work around is to make sure all SQL Server jobs have more than one jobstep and the first jobstep ends successful everytime. This way a row will show up in the sysjobhistory table before the job completes. Another option that I recently thought of was to take advantage of the master..sysprocesses table in conjuction with the msdb..sysjobhistory table.
-
This was removed by the editor as SPAM
-
I pieced this together using posts from elsewhere on SQLservercentral.com:
CREATE PROCEDURE dbo.usp_IsSQLAgentJobRunning
@ParmJobName sysname
AS
/*
-- procedure to determine if the SQL Agent job named in @ParmJobName is currently running. returns 0 if it is not currently running, -1 if it is.
*/
DECLARE @Return int
CREATE TABLE #Job (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 -- 1 if currently running, otherwise zero
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
INSERT INTO #Job
EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo
IF EXISTS (SELECT *
FROM #Job AS j
JOIN msdb.dbo.sysjobs AS s
ON j.job_id = s.job_id
WHERE s.name = @ParmJobName
AND j.running = 1)
RETURN -1
ELSE
RETURN 0
GO
There is no "i" in team, but idiot has two. -
Excellent! I don't find any documents that talk about xp_sqlagent_enum_jobs. What are the parameters being passed? Is there other parameters that my be passed?
-
These are the parameters :
EXEC xp_sqlagent_enum_jobs <is sysadmin (0 or 1)>, <job owner name> [, <job id>]
HTH
* Noel -
Dave,
When I execute your stored procedure in Query Analyzer I don't get any value returned.
Sue
-
xp_sqlagent_enum_jobs is undocumented. As such, Microsoft makes no claims as to its availability or functionality in future releases, and generally discourages customers' reliance on them. You may want to consider this before using it in a project that you intend to market!
Steve
-
Sue: Obvious question, but how are you calling it? EXEC @Ret = dbo.usp_IsSQLAgentJobRunning 'myjobname'? Then @Ret will have either a 0 or a -1. Also, you need EXECUTE permission on master.dbo.xp_sqlagent_enum_jobs and SELECT permission on master.dbo.sysjobs.
There is no "i" in team, but idiot has two. -
Steve: That's a good point. Now that you mention it, I recall considering that at the time, and fiddled around for quite awhile trying to get the same result out of a pure SQL solution, and finally gave up and went with the undocumented xproc. We have this on our list of things to verify when we get a new service pack for SQL Server.
There is no "i" in team, but idiot has two. -
Dave:
What is the problem with using sp_help_job and use the current_execution_status field ?
* Noel -
Noel: It just plain didn't work for me, for reason or reasons unknown. current_execution_status always returned zero, no matter what I did. grrrr.
So I came up with the proc I described earlier.
There is no "i" in team, but idiot has two. -
Same here, Dave.
Couldn't get the output I needed from Microsoft Documented procedures, so I hacked my own. Works great! I just hope Microsoft doesn't change or eliminate it. But if they do, its my own little procedure in my own little world, and my own little fault for using an undocumented proc!
Steve
-
The problem with sp_help_job is that one cannot capture its output. When one tries to capture the output into a table, an error occurs because sp_help_job calls other procs (nested). sp_help_job works great when executing it manually.
-
Below you will find my enhancement of Dave's stored procedure. This stored procedure will allow us to create SQL Server jobs but have a third party vendor solution run them. In the code below, I question why I have to have the WAITFOR DELAY. I found that with Query Analyzer, I am able to start a SQL Server Job a second time before the first execution has completed. However, I am not able to do this with SQL Server Enterprise Manatger. Can someone explain?
CREATE PROCEDURE dbo.SpRunSQLAgentJob
@ParmJobName sysname
AS
/*
-- Procedure to start SQL Agent job named in @ParmJobName determine
-- when it finishes, and whether or not it was successful.
*/
SET NOCOUNT ON
DECLARE @Return int, -- return code when calling other stored procedures
@JobNotRunning bit, -- 0 = job is not running, 1 = job is running
@Loop_Flag bit,
@job_id uniqueidentifier,
@last_run_dateint,
@last_run_time int,
@run_statusint,
@run_status_name varchar(50),
@StoredProcsysname -- will contain this stored procedure name
CREATE TABLE #Job (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 -- 1 if currently running, otherwise zero
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
SET @StoredProc = db_name() + '..SpRunSQLAgentJob'
/*
-- Start the SQL Server Job
*/
EXEC @Return = msdb..sp_start_job @ParmJobName
IF @Return = 1
BEGIN
RAISERROR ('FAILED TO START SQL SERVER JOB ''%s'' FROM %s!', 18, 1, @ParmJobName, @StoredProc)
RETURN(-9010)
END
-- Delay processing for 15 seconds.
WAITFOR DELAY '00:00:15'
-- Set variables
SET @Loop_Flag = 1
SET @JobNotRunning = 1
-- Get SQL Server Job Id
SELECT @job_id = job_id FROM msdb..sysjobs WHERE name = @ParmJobName
/*
-- Loop until job stops executing. When this looping process
-- completes, there will be only 1 record in the #Job table.
*/
WHILE @Loop_Flag = @JobNotRunning -- Loop while job is executing.
BEGIN
INSERT INTO #Job
EXEC master.dbo.xp_sqlagent_enum_jobs 1, dbo, @job_id
IF EXISTS (SELECT * FROM #Job AS j
WHERE j.running = 1)
TRUNCATE TABLE #Job
ELSE
SET @JobNotRunning = 0
END
-- Get the SQL Server Job's run date and time.
SELECT @last_run_date = last_run_date, @last_run_time = last_run_time FROM #Job
/*
-- Determine whether or not the SQL Server Job executed successfully.
*/
-- Get the final execution status of the SQL Server Job.
SELECT @run_status = run_status FROM msdb..sysjobhistory
WHERE job_id = @job_id
AND step_id = 0
AND run_date = @last_run_date
AND run_time = @last_run_time
DROP TABLE #Job
-- Determine final executions status meaning.
SELECT @run_status_name =
CASE @run_status
WHEN 1 THEN 'SQL SERVER JOB COMPLETED SUCCESSFULLY'
WHEN 0 THEN 'SQL SERVER JOB FAILED'
WHEN 3 THEN 'SQL SERVER JOB WAS CANCELLED'
ELSE 'SQL SERVER JOBs STATUS IS ' + CAST(@run_status AS varchar(1))
END
-- Mark the job completion as successful or failing.
IF @run_status = 1
BEGIN
SELECT @run_status_name
END
ELSE
BEGIN
SET NOCOUNT OFF
RAISERROR ('%s', 18, 1, @run_status_name)
RETURN(-9020)
END
SET NOCOUNT OFF
RETURN(0)
-
Dave, Steve, DarylAndDaryl
Do you want to give this a shot? ....(IT WORKS FOR ME)
select *
from
openrowset ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q
--where q.name = MYJOBNAME
HTH
* Noel
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply