May 14, 2012

sp_MSget_jobstate (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSget_jobstate(uniqueidentifier @job_id)

MetaData:

   
-- Procedure sp_MSget_jobstate
--
-- Descriptions:
-- The proc takes a specific Job ID and returns the Job State of the job
-- Returns a row with one column job_state
-- Returns a row with NULL if job does not exist
--
-- Parameters: as defined in create statement
--
-- Returns: 0 - success
-- 1 - Otherwise
--
-- Security:
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_MSget_jobstate
@job_id UNIQUEIDENTIFIER
AS
BEGIN
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)

-- Need a job_id
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)
RETURN(1) -- Failure
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

-- Error if we have no rows selected
if (@job_state IS NULL)
BEGIN
SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char)
RETURN(1) -- Failure
END
ELSE
SELECT @job_state

-- All done
DROP TABLE #xp_results
RETURN(0) -- Success
END

No comments:

Post a Comment

Total Pageviews