Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSget_jobstate

  No additional text.


Syntax

-- 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

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash