?
Solved

Step failed with the message bellow. Why?

Posted on 2003-03-13
9
Medium Priority
?
3,013 Views
Last Modified: 2007-12-19
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
Comment
Question by:xenon_je
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 8127077
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
 
LVL 9

Author Comment

by:xenon_je
ID: 8127251
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
 
LVL 9

Author Comment

by:xenon_je
ID: 8127284
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 23

Expert Comment

by:adathelad
ID: 8127327
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
 

Accepted Solution

by:
ronar4 earned 400 total points
ID: 8127862
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
 
LVL 9

Author Comment

by:xenon_je
ID: 8128001
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
 
LVL 9

Author Comment

by:xenon_je
ID: 8128006
Not a patch...solved the problem
0
 

Expert Comment

by:luckyinc
ID: 14844134
Hi Xenon,

What did you end up doing to solve the problem?

Thanks,
Luckyinc
0
 
LVL 9

Author Comment

by:xenon_je
ID: 14868366
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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question