You are here:

MS SQL/Checking status of the JOB

Advertisement


Question
Question category: MS SQL programming

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 im my code.

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

My code:
USE [gas_input]
GO
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 = 'GAS_log1'   

  RETURN @IsJobRunning
END
GO

Answer
Hi Nelia

I guess you are trying to find out what all is running currently.

I request you to have a look at alternate approach give under and pass the Job Id...

http://www.sqlnotes.info/2012/01/13/are-jobs-currently-running/#more-1194


Thanks
Mohit

MS SQL

All Answers


Answers by Expert:


Ask Experts

Volunteer


Mohit Nayyar

Expertise

Microsoft SQL Server: T-SQL development (stored procedures, triggers, functions), Database optimization, performance tuning, high availability (Clustering, Log Shipping, Mirroring, Replication), scalability, SQL Server migration (Sybase/Oracle to SQL Server), SSIS/DTS, Data Warehousing (Kimball Methodology), ETL (SSIS), Cube (SSAS), Reporting Services (SSRS), database modeling, database administration, Security implementation and typically anything related to Microsoft SQL Server.

Experience

With over 10 years of experience in Database Administration, Development, Business Intelligence solutions and managing enterprise level database solutions based on Microsoft SQL Server.
Publications
IT Magazine: ASP, SQL

Publications
IT Magazine: ASP, SQL
SQLServerCentral.com

Education/Credentials
MCA (Masters)

Awards and Honors
Brainbench Certified: RDBMS
MCTS: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCITP: DBA/Developer/BI for SQL Server 2005/SQL Server 2008
MCDBA: SQL Server 2000
MCP: SQL Server 6.5

©2016 About.com. All rights reserved.