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