SQL Server Performance

Checking status og JOB execution

Discussion in 'ALL SQL SERVER QUESTIONS' started by Nelia, Oct 30, 2012.

  1. Nelia New Member

    Hello!
    I’m trying to create function that returns BIT value. This function returns the answer if the JOB execution still running. But I get an error:Invalid use of side-effecting or time-dependent operator in 'INSERT EXEC' within a function.
    Is this possible to use EXECUTION of stored procedure in Scalar function? Or, maybe, there are mistakes in my code.

    P.S. I'm a SQL newbie, so, please, be nice to me

    My code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET NOCOUNT ON
    GO

    CREATE FUNCTION [fn_JobStatusCheck_GAS_log1] (@job_owner sysname)
    RETURNS BIT
    AS
    BEGIN

    DECLARE @xp_results TABLE
    (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,
    request_source INT NOT NULL,
    request_source_id sysname COLLATE database_default NULL,
    running INT NOT NULL,
    current_step INT NOT NULL,
    current_retry_attempt INT NOT NULL,
    job_state INT NOT NULL)

    SET @job_owner = SUSER_SNAME()
    INSERT INTO @xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner

    DECLARE @IsJobRunning BIT

    SELECT @IsJobRunning = x.running
    FROM @xp_results x
    INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.job_id
    WHERE sj.name = 'MyJobName'

    RETURN @IsJobRunning
    END
    GO
  2. Shehap MVP, MCTS, MCITP SQL Server

    First , welcome to Forums

    It is inapplicable to use stored procedure within Functions but only Function and stored procedure , thereby what you can do by that case is to translate the function to a SP using output function like that one below “fn_JobStatusCheck_GAS_log1_SP” to be able to get an equivalent meaning for your function fn_JobStatusCheck_GAS_log1

    --Create new SP

    CREATEprocedure[fn_JobStatusCheck_GAS_log1_SP] @job_owner sysname, @IsJobRunning bitoutput

    AS

    BEGIN

    DECLARE @xp_results TABLE

    (job_id UNIQUEIDENTIFIERNOTNULL,

    last_run_date INTNOTNULL,

    last_run_time INTNOTNULL,

    next_run_date INTNOTNULL,

    next_run_time INTNOTNULL,

    next_run_schedule_id INTNOTNULL,

    requested_to_run INTNOTNULL,

    request_source INTNOTNULL,

    request_source_id sysnameCOLLATE database_default NULL,

    running INTNOTNULL,

    current_step INTNOTNULL,

    current_retry_attempt INTNOTNULL,

    job_state INTNOTNULL)

    SET @job_owner =SUSER_SNAME()

    INSERTINTO @xp_results

    EXECUTEmaster.dbo.xp_sqlagent_enum_jobs1, @job_owner

    SELECT @IsJobRunning = x.running

    FROM @xp_results x

    INNERJOIN msdb.dbo.sysjobs sj ON sj.job_id = x.job_id

    WHERE sj.name ='MyJobName'

    RETURN @IsJobRunning

    END

    --Exec SP

    DECLARE @IsJobRunning BIT

    EXEC fn_JobStatusCheck_GAS_log1_SP @JOB_OWNER ='SA',@IsJobRunning=@IsJobRunning OUTPUT

    SELECT @IsJobRunning

    Kindly try it and let me know your feedback

Share This Page