SP work in Query Analyser but fails in SQL Agent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP work in Query Analyser but fails in SQL Agent

Author

Topic

melliott1963
Starting Member

5 Posts

Posted - 2004-06-22 : 05:32:31

Hi,

I've written a Stored Procedure to query the current jobs that are running in SQL Agent and return -1 if there's a job which has been running for longer than 15 minutes and 0 if not.

It works fine when I run it manually within Query Analyser, but when I run it as a job under SQL Agent, I get the following error:-

Syntax error converting datetime from character string. [SQLSTATE 22007] (Error 241). The step failed.

The code I'm trying to run is:-

----------------------------------------------------------------

CREATE PROCEDURE usp_xml_running_job_info
AS
BEGIN
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname

SET NOCOUNT ON

-- Step 1: Create intermediate work tables
CREATE TABLE #filtered_jobs (job_id UNIQUEIDENTIFIER NOT NULL,
current_execution_status INT NULL,
next_run_date DATETIME NULL)

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)

-- Step 2: 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()

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

-- Step 3: Filter running jobs only
BEGIN
INSERT INTO #filtered_jobs
SELECT sjv.job_id,
ISNULL(xpr.job_state, 4),
CAST(xpr.next_run_date as varchar(8)) + ' ' + Left(xpr.next_run_time, 2) + ':' + Substring(CAST(xpr.next_run_time as varchar(6)), 3, 2) + ':' + Right(xpr.next_run_time, 2)
FROM msdb.dbo.sysjobs_view sjv
LEFT OUTER JOIN #xp_results xpr ON (sjv.job_id = xpr.job_id)
WHERE xpr.job_state = 1
END

-- Return the result set
IF (SELECT COUNT(*)
FROM #filtered_jobs fj LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON (fj.job_id = sjv.job_id)
WHERE sjv.name like 'XML%' AND DateDiff(mi, fj.next_run_date, getdate())>15) >0
SELECT -1
ELSE
SELECT 0

-- Clean up
DROP TABLE #filtered_jobs
DROP TABLE #xp_results
END
GO
----------------------------------------------------------------

This is basically a butchered version of the system proc sp_get_composite_job_info

I've tried putting the next_run_date and next_run_time into the table #filtered_jobs in many ways and, whilst they work in QA, I can't get them to work under SQL Agent.

Has anyone got any ideas as to why this is, or if there is a better way to go about what I'm trying to do?

Thanks.

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-22 : 12:43:03

When you "View Job History", check the "Show Details" box to get more information. I'm pretty sure that will clarify things.

-PatP

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-22 : 23:08:07

I am not positive about this, but possibly QA has a different SET DATEFORMAT than SQL Agent and you are not explicitly casting next_run_date with a format.
select cast (getdate() as varchar(8))
--------
Jun 22 2 <-- this is probably not what you want

select convert (varchar(8),getdate(),112)
--------
20040622

Try changing CAST (xpr.next_run_date as varchar(8)) to convert(varchar(8),xpr.next_run_date,112)

melliott1963
Starting Member

5 Posts

Posted - 2004-06-23 : 04:42:45

Thanks for the replies but......

quote:
Originally posted by Pat Phelan

When you "View Job History", check the "Show Details" box to get more information. I'm pretty sure that will clarify things.

At the start of my original message, I put the error that the Job History comes up with:- Syntax error converting datetime from character string. [SQLSTATE 22007] (Error 241). The step failed.

quote:
Originally posted by kselvia

Try changing CAST (xpr.next_run_date as varchar(8)) to convert(varchar(8),xpr.next_run_date,112)

I've tried this, but still no joy.

Has anyone got any other ideas, or is there an easier way to find out how long a job has been running?

Thanks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-06-23 : 06:41:15

Put a SET DATEFORMAT statement into the top of your SP.
If you can....output any variables to a flat text file for inspection.

Your debug options are quite limited, once the code is being launched by the agent....so you may have to invent/be crude in tackling the problem.

melliott1963
Starting Member

5 Posts

Posted - 2004-06-23 : 10:21:04

Sorry, but SET DATEFORMAT doesn't work

I realised that the code was slightly wrong in that it wouldn't work if the time was prior to 12:00pm (the time, being a number, wouldn't show the leading 0). The current version of my code, which still works in QA but not SQL Agent is:-

-----------------------------------------------------------
CREATE PROCEDURE usp_xml_running_job_info
AS
BEGIN
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname

SET NOCOUNT ON
SET DATEFORMAT ymd

-- Step 1: Create intermediate work tables
CREATE TABLE #filtered_jobs (job_id UNIQUEIDENTIFIER NOT NULL,
current_execution_status INT NULL,
next_run_date DATETIME NULL)

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)

-- Step 2: 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()

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

-- Step 3: Filter running jobs only
BEGIN
INSERT INTO #filtered_jobs
SELECT sjv.job_id,
ISNULL(xpr.job_state, 4),
CONVERT(DATETIME, CAST(xpr.next_run_date as varchar(8)) + ' ' + CASE WHEN Len(xpr.next_run_time) = 5 THEN '0' + Left(xpr.next_run_time, 1) ELSE Left(xpr.next_run_time, 2) END + ':' + CASE WHEN Len(xpr.next_run_time) = 5 THEN Substring(CAST(xpr.next_run_time as varchar(6)), 2, 2) ELSE Substring(CAST(xpr.next_run_time as varchar(6)), 3, 2) END + ':' + Right(xpr.next_run_time, 2), 112) as next_run_date
FROM msdb.dbo.sysjobs_view sjv
LEFT OUTER JOIN #xp_results xpr ON (sjv.job_id = xpr.job_id)
WHERE xpr.job_state = 1
END

-- Return the result set
IF (SELECT COUNT(*)
FROM #filtered_jobs fj LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON (fj.job_id = sjv.job_id)
WHERE sjv.name like 'XML%' AND DATEDIFF(mi, fj.next_run_date, getdate()) >1) >0
SELECT -1
ELSE
SELECT 0

-- Clean up
DROP TABLE #filtered_jobs
DROP TABLE #xp_results
END
GO
-----------------------------------------------------------

Just for testing, I replaced the line:-

CONVERT(DATETIME, CAST(xpr.next_run_date as varchar(8)) + ' ' + CASE WHEN Len(xpr.next_run_time) = 5 THEN '0' + Left(xpr.next_run_time, 1) ELSE Left(xpr.next_run_time, 2) END + ':' + CASE WHEN Len(xpr.next_run_time) = 5 THEN Substring(CAST(xpr.next_run_time as varchar(6)), 2, 2) ELSE Substring(CAST(xpr.next_run_time as varchar(6)), 3, 2) END + ':' + Right(xpr.next_run_time, 2), 112) as next_run_date

with getdate() and it doesn't error out in SQL Agent, so this is obviously where the problem lies, but I'll be damned if I can work out why.

For information, next_run_date comes over as 20040623 (i.e. yyyymmdd) and the next_run_time as 150600 (i.e. 3:06pm)

melliott1963
Starting Member

5 Posts

Posted - 2004-06-23 : 11:05:22

Just a bit of additional information. I've just converted the Stored Procedure into a DTS Package. This runs fine when I execute it under Enterprise Manager and also from a command prompt using DTSRun, but I still get the same syntax error when using exactly the same DTSRun command under SQL Agent.

This is getting really annoying now. Surely this has to be a ~~bug~~ feature of the latest version of SQL Server 2000?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-23 : 14:38:00

I would not depend on the way any date formatting is done by default. I would use convert(varchar,<datefield>,<format number>) from BOL. For both dates, and times.

melliott1963
Starting Member

5 Posts

Posted - 2004-06-24 : 06:22:46

OK, I've given up on the idea of using SQL Agent and approached it from a different angle.

I've now created a DTS package using exactly the same code with the exception that at the end, instead of returning -1 or 0, it now runs xp_sendmail to send a mail message if a job has been running for longer than 15 minutes.

This DTS package is now run, using the DTSRun command, under the standard Windows Task Scheduler, and it works well!

Thanks anyway for the suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources