December 11, 2006 at 11:45 am
When I try to use sp_MSget_jobstate to determine if a job is running, the variable returned by the stored procedure cannot be used....in QA it will display the result but any further reference to the variable returns a '0'. That makes it useless for any further conditional processing. An example:
declare @job uniqueidentifier, @job_status int
set @job = (select job_id from msdb..sysjobs where name = 'Backup Northwind')
exec @job_status = master..sp_MSget_jobstate @job
if @job_status = 4
print 'idle'
else
print 'not idle'
Any suggestions???
December 11, 2006 at 12:45 pm
exec @job_status = master..sp_MSget_jobstate @job--- here yoour variabile will not hold the "desired job_status" but the result of executing the stored procedure succesfully/unsuccessfully
DECLARE @job_id uniqueidentifier, @job_status int
SET @job_id = (select top 1 job_id from msdb..sysjobs)
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
DECLARE @job_state INT
DECLARE @job_id_as_char VARCHAR(36)
SET NOCOUNT ON
CREATE TABLE #xp_results (
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)
if (@job_id IS NULL)
BEGIN
SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char)
END
-- Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = suser_sname(suser_sid())
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
-- Select the job state of the job in question
SELECT @job_state = job_state FROM #xp_results WHERE @job_id = job_id
PRINT @job_state
DROP TABLE #xp_results
Vasc
December 12, 2006 at 11:52 am
Thanks Vasc. The problem I was trying to solve was transaction log backups starting while a full backup was still running. Before getting your reply, I worked out something similar by creating a stored procedure using sp_MSget_jobstate as a skeleton.
I replaced the first 2 lines with:
CREATE PROCEDURE sp_check_jobstatus
@job_id UNIQUEIDENTIFIER, @job_status INT OUTPUT
Toward the bottom I replaced
"SELECT @job_state" with "SELECT @job_status = @job_state"
Now the transaction log backup job looks like this:
declare @job uniqueidentifier, @return int, @job_status int
set @job = (select job_id from msdb..sysjobs where name = 'Backup Northwind')
exec @return = master..sp_check_jobstatus @job, @job_status output
if @job_status = 4
With this setup, the transaction log backup cannot run while the full backup is still running. As the transaction log backup normally runs every 15 minutes, skipping them during the full backup is not a problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply