SQL job runs fine alone but fails when added to larger job

Question

SQL job runs fine alone but fails when added to larger job

Janet Barnett

SSChasing Mays

Points: 604
More actions
August 27, 2019 at 3:06 pm

#3674758

I have a step in a large SQL Server job that has been running fine. Recently, it's now erroring out with a datetime mismatch. I suspect that the error isn't giving the real problem and I'll explain why below.
I converted all dates explicitly. I've tried setting the language manually to us-english for this step to force correct date translation. I can run fine using SQL Server Agent job separately where it is the only step, using the same network sql server account that is also used for the larger job. I can also run it from the query window just fine. But, when I execute within the larger job where it is step 17, it errors out again: "...job step 17 failed. The conversion of date data type to a smalldatetime data type resulted in an out-of-range value."
I'd like to keep it in the larger job, because the events are cascading within the job and rely upon each other. But because it runs fine alone, I don't think it's an actual smalldatetime issue?
Any ideas on what is really happening? Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

crow1969 Hall of Fame Points: 3001 More actions · Answer 1

This sounds like a job for Extended Events. In order to get more information, you should probably set up an Event Session to collect Login Events from this particular ID. With the Login Event, you should be able to get the environmental variables that the session is running with.

My initial thoughts run along the same path you have already checked. Is this a T-SQL job, or is this an SSIS job? If it is an SSIS, Powershell, or CmdExec job, it may have a proxy account set up for the job.

Lowell SSC Guru Points: 323518 More actions · Answer 2

i might consider checking the ansi and date_format sessions settings for default dates, as they could be different on the server, running as an agent , vs running in SSMS

ie these values:

 SET ANSI_NULLS ON;
 SET ANSI_PADDING ON;
 SET ANSI_WARNINGS ON;
 SET ARITHABORT ON;
 SET CONCAT_NULL_YIELDS_NULL ON;
 SET NUMERIC_ROUNDABORT OFF;
 SET QUOTED_IDENTIFIER ON;
SET DATE FORMAT mdy
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT ' SET ANSI_NULLS '              + CASE WHEN SESSIONPROPERTY('ANSI_NULLS')              = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET ANSI_PADDING '            + CASE WHEN SESSIONPROPERTY('ANSI_PADDING')            = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET ANSI_WARNINGS '           + CASE WHEN SESSIONPROPERTY('ANSI_WARNINGS')           = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET ARITHABORT '              + CASE WHEN SESSIONPROPERTY('ARITHABORT')              = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET CONCAT_NULL_YIELDS_NULL ' + CASE WHEN SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL') = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET NUMERIC_ROUNDABORT '      + CASE WHEN SESSIONPROPERTY('NUMERIC_ROUNDABORT')      = 1 THEN 'ON;' ELSE 'OFF;' END UNION ALL
SELECT ' SET QUOTED_IDENTIFIER '       + CASE WHEN SESSIONPROPERTY('QUOTED_IDENTIFIER')       = 1 THEN 'ON;' ELSE 'OFF;' END
UNION ALL 
SELECT 'SET DATE FORMAT ' + CONVERT(VARCHAR(30),[date_format]) AS DataFormat
FROM sys.dm_exec_sessions 
  where session_id = @@SPID
UNION ALL 
SELECT 'SET TRANSACTION ISOLATION LEVEL ' 
+ CASE transaction_isolation_level 
    WHEN 0 THEN 'UNSPECIFIED' 
    WHEN 1 THEN 'READ UNCOMMITTED' 
    WHEN 2 THEN 'READ COMMITTED' 
    WHEN 3 THEN 'REPEATABLE READ' 
    WHEN 4 THEN 'SERIALIZABLE' 
    WHEN 5 THEN 'SNAPSHOT' 
  END + ';' AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!