Hi!
Sometimes I have som jobs that dies.
In the job history I found such as:

Message
Executed as user: domain\batchuser. ...r 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) 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
ot... The step failed

Message
Executed as user: domain\batchuser. ...ted 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)
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) String or binary
data would be truncated. [SQLSTATE 22001] (Error 8152) The statement
has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

Whats going on?
Normally I got errors catched and logged in table, but not in this
case...

Suggestions how to find root cause?
/Bjorn

B D Jensen (bjorn.d.jensen@gmail.com) writes:
> Message
> Executed as user: domain\batchuser. ...ted 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)
> 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) String or binary
> data would be truncated. [SQLSTATE 22001] (Error 8152) The statement
> has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
>
> Whats going on?
> Normally I got errors catched and logged in table, but not in this
> case...

Quite much of a guessing game that you offer, since we don't know what
type of job it is, we don't see any code etc.

However, there are some clues. You say "Normally I got errors catched and
logged in table", so I assume the jobs runs a stored procedure that has
a TRY-CATCH section and which logs error into a table. Next clue is the
error message: "String or binary data would be truncated". From this my
conclusion is that the column for the error message is not long enough,
so you get an error when trying to save the error message. Either
alter the log table to use nvarchar(MAX) for the error message, or
use substring when you save the error.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi!
Sorry about the guessing game here.
I actually use batchlogging to avoid this game.

Running the same batchjob manual I got no error.
I hoped maybe it look similar to another kind of problem seen in
practice by people.
I now identified the proc that result in warning, which I now disabled
(SET ANSI_WARNINGS OFF, not that I'm happy about that,
but it should not harm in this case) Now I hope to see more from the
error message.
Remark that they are not exactly the same, or maybe they are, but it
seems that errormessage is truncated...

-thanks
Bjorn