• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3179
  • Last Modified:

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
0
xenon_je
Asked:
xenon_je
1 Solution
 
adatheladCommented:
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
0
 
xenon_jeAuthor Commented:
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...
0
 
xenon_jeAuthor Commented:
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.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
adatheladCommented:
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
****
0
 
ronar4Commented:
The error is caused by the HY007.
This appears when you have an 'insert into ..' for example.
Solution:put the command
RAISERROR ("xxx", 1, 1)
just after the insert into statement. This way you signal that the error is a warning (the second 1 is for warning).
This should work.
You can take a look at : http://dbforums.com/arch/43/2002/7/422557
good luck


0
 
xenon_jeAuthor Commented:
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
0
 
xenon_jeAuthor Commented:
Not a patch...solved the problem
0
 
luckyincCommented:
Hi Xenon,

What did you end up doing to solve the problem?

Thanks,
Luckyinc
0
 
xenon_jeAuthor Commented:
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
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now