asked on

Step failed with the message bellow. Why?

Hi. I made a job which shows me that it failed, with the description bellow:
*****
Duplicate key was ignored. [SQLSTATE 23000] (Message 3604)
Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Error 8153)
begin ppeventlog [SQLSTATE 01000] (Error 0) end ppeventlog [SQLSTATE 01000] (Error 0) begin cust care [SQLSTATE 01000] (Error 0) start account update (instead of cursor) [SQLSTATE 01000] (Error 0) complete account update (instead of cursor) count=0 [SQLSTATE 01000] (Error 0) end cust care [SQLSTATE 01000] (Error 0) in main loop [SQLSTATE 01000] (Error 0) updates c1 = 0; c2 = 0; c3 = 0; c4 = 0 [SQLSTATE 01000] (Error 0) create fact tables [SQLSTATE 01000] (Error 0) update view [SQLSTATE 01000] (Error 0) begin insert into cdr [SQLSTATE 01000] (Error 0) end insert into cdr; count = 460 [SQLSTATE 01000] (Error 0) end main loop [SQLSTATE 01000] (Error 0) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed.
*****
In the sp, right at the end, is the
print 'end main loop'
So the sp makes its job, but because the job reports that the step failed, it will not continue with the next steps. but quits with failure....
I tried to find the [SQLSTATE HY007], but in the books online I found nothing... and I thought this is maybe the 'thing' that generates the failure....
Anybody who has any ideas, please help me.
thx, xenon

adathelad

The actual error that occurs is 8153, which is shown in the second line of the description.

Make sure in your stored procedure, if you are doing an aggregate function like SUM(FieldName) or MAX(FieldName), that if there is a chance the "FieldName" value will be NULL, then change the code like: SUM(ISNULL(FieldName, 0)) or MAX(ISNULL(FieldName, 0))

Let me know if you need more help.
Cheers

xenon_je

ASKER

the 8153 is just a warning. By itself, just like the warning 'duplicate key ignored' will not make the job to report failure.
Anyway I will test what you just sayd, to be sure that I'm right or not...

xenon_je

ASKER

Here is the content of the test sp:
*******
create table #tmp1(
c1 int
)

insert into #tmp1 values(1)

insert into #tmp1 values(null)

select Count(c1) from #tmp1

drop table #tmp1
****
At execution the job reported succes, so I didn't understood well what you wrote, or the answer is not good.

adathelad

Try an aggregate function like SUM or MAX. Try this and let me know if that makes a difference:

create table #tmp1(
c1 int
)

insert into #tmp1 values(1)

insert into #tmp1 values(null)

select SUM(c1) from #tmp1
-- OR: select MAX(c1) from #tmp1

drop table #tmp1
****

ASKER CERTIFIED SOLUTION

ronar4

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xenon_je

ASKER

adathelad
I tried what you suggested, but even if in the job history I had the messages bellow, the job ended with succes
***
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). The step succeeded.
*****

ronar4:
it works..thx

xenon_je

ASKER

Not a patch...solved the problem

luckyinc

Hi Xenon,

What did you end up doing to solve the problem?

Thanks,
Luckyinc

xenon_je

ASKER

luckyinc this question is an old one...more than 2 years ago....

But as I remember the solution with RAISERROR ("xxx", 1, 1) after the insert into command worked for me....did you had problems with this solution I know that maybe is not very elegant....but take into consideration that the error message, and the behaviour is prety strange too...Anyway for me it worked at that time...

Let me know if this doesn't solve your problem...
regards,
xenon