Failed job incomplete error message
-
Hi
One of my scheduled job failed and the logged error message is incomplete. There is no mention of what error came. Only some warnings aee written. Any way to get complete message? Here is the message logged:
Executed as user: NT AUTHORITY\SYSTEM. ...SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is
eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an
aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or
other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other
SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation.
[SQLSTATE 01003] (Message 8153) From Date May 16 2012 6:01AM [SQLSTATE 01000] (Message 0) To Date Jun 26 2012 12:00AM
[SQLSTATE 01000] (Message 0) BeginningJun 26 2012 6:01AM [SQLSTATE 01000] (Message 0) After @AffiliateSales Insert-Jun
26 2012 6:02AM [SQLSTATE 01000] (Message 0) After @AffiliateSales Insert ICF-Jun 26 2012 6:02AM [SQLSTATE 01000]
(Message 0) After @AffiliateHits Insert -Jun 26 2012 6:02AM [SQLSTATE 01000] (Message 0) After @@AffiliateCosts
\Insert -Jun 26 2012 6:05AM [SQLSTATE 01000] (Message 0) start @AffiliateHitsCost Insert and update cost -Jun 26 2
012 6:05AM [SQLSTATE 01000] (Message 0) After @AffiliateHitsCost Insert and update cost 1-Jun 26 2012 6:05AM [SQLSTATE
01000] (Message 0) After @AffiliateHitsCost Update -Jun 26 2012 6:05AM [SQLSTATE 01000] (Message 0) After @Affilia
teHitsCost Update 2 -Jun 26 2012 6:05AM [SQLSTATE 01000] (Message 0) Start BUdget Check -Jun 26 2012 6:05AM [SQLSTA
TE 01000] (Message 0) Affiliate Company ID 55 Affiliate ID 1618 Date Viewed 05/17/2012 Computed Cost 245721.70 Daily C
ost 124.67 Total Cost 245846.37 Company Budget 249999.39 [SQLSTATE 01000] (Message 0) Warning: Null value is eliminated
by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggreg
ate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SE
T operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation
. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTA
TE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003]
(Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message
8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) War
ning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null v
alue is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is elim
inated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an
aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or
other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET o
peration. [SQLSTATE 01003] (Message 8153) Affiliate Company ID 55 Affiliate ID 1617 Date Viewed 05/17/2012 Computed
Cost 245721.70 Daily Cost 138.45 Total Cost 245984.82 Company Budget 249999.39 [SQLSTATE 01000] (Message 0) Affiliate
Company ID 55 Affiliate ID 1617 Date Viewed 05/18/2012 Computed Cost 245721.70 Daily Cost 106.65 Total Cost 246091.47
Company Budget 249999.39 [SQLSTATE 01000] (Message 0) Affiliate Company ID 55 Affiliate ID 1618 Date View
ed 05/18/2012 Computed Cost 245721.70 Daily Cost 109.85 Total Cost 246201.32 Company Budget 249999.39 [SQ
LSTATE 01000] (Message 0) Warning: Null value is eliminated by an aggregate or other SET operation. [SQL
STATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLS...
The step failed.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
The messages logged by SQL Agent are limited, and formatting is removed making it difficult to read. Might I suggest piping the output of your job step to a file using SQL Agent. In the job step properties:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
You may be able to get some additional information out of the sys tables
DECLARE @ScheduledJobName sysnameSET @ScheduledJobName = 'your Scheduled Job Name'
SELECT SQL_message_id,messages.text,sql_severity,message
FROM msdb.dbo.sysjobhistory
JOIN msdb.dbo.sysjobs
ON sysjobhistory.Job_id = sysjobs.Job_id
JOIN sys.messages
ON sysjobhistory.SQL_message_id = messages.message_id
WHERE sysjobs.name = @ScheduledJobName
AND run_status = 0
AND language_id = 1033
I've set the language code to 1033 (English) BOL will give you the other codes if you are more comfortable with another language
Ian
-
This actually gives the same text as you get thru GUI and again it won't be complete
440692 I am just a number (6/26/2012)
You may be able to get some additional information out of the sys tablesDECLARE @ScheduledJobName sysnameSET @ScheduledJobName = 'your Scheduled Job Name'
SELECT SQL_message_id,messages.text,sql_severity,message
FROM msdb.dbo.sysjobhistory
JOIN msdb.dbo.sysjobs
ON sysjobhistory.Job_id = sysjobs.Job_id
JOIN sys.messages
ON sysjobhistory.SQL_message_id = messages.message_id
WHERE sysjobs.name = @ScheduledJobName
AND run_status = 0
AND language_id = 1033
I've set the language code to 1033 (English) BOL will give you the other codes if you are more comfortable with another language
Ian
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
-
So I I direct output to a file, will it write complete message? Ok, let me try it.
opc.three (6/26/2012)
The messages logged by SQL Agent are limited, and formatting is removed making it difficult to read. Might I suggest piping the output of your job step to a file using SQL Agent. In the job step properties:Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply