Solved

job failing

Posted on 2009-03-31
10
1,299 Views
Last Modified: 2013-11-30
Trying to execute a step(Store Procedure) inside Job.But the job is failing because of warning resulted from the store procedure.
But this warnings can be ignored. But the job is failing because of warnings.

Is there any way to make the job execute successful even there are warning messages in the job?
0
Comment
Question by:SmartestVEGA
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 2

Author Comment

by:SmartestVEGA
ID: 24028250

Executed as user: COMPANY\$$SQLService. 

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)  Divide by zero error encountered. [SQLSTATE 22012] (Error 8134)  

Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Error 8153)  

The statement has been terminated. [SQLSTATE 01000] (Error 3621).  

The step failed

Open in new window

0
 
LVL 4

Accepted Solution

by:
justin-clarke earned 300 total points
ID: 24028355
SET ANSI_WARNINGS OFF

Use that at the top of your stored procedure
0
 
LVL 8

Assisted Solution

by:vinurajr
vinurajr earned 100 total points
ID: 24028551
SET ANSI_DEFAULTS { ON | OFF }
0
 
LVL 7

Assisted Solution

by:stelth240
stelth240 earned 100 total points
ID: 24028690
I would think it wouldn't be the warnings that are causing a problem, but the divide by zero error that's the problem.  You have to do a check in your stored procedure for divide by zero like this:

SELECT CASE WHEN Field2 = 0 THEN 0 ELSE Field1 / Field2 END AS Result

The warnings are probably not causing the package to fail although using SET ANSI_WARNINGS OFF should definitely remove them like Justin said.
0
 
LVL 2

Author Comment

by:SmartestVEGA
ID: 24028710
Yes it is divide by zero ,several times in my sp is there any alternatives?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Assisted Solution

by:justin-clarke
justin-clarke earned 300 total points
ID: 24029502
Well if you showed your code we could probably pick out the bit where it's giving the error.

But say you have a sum statement..

sum(column1) then you can replace with sum(IsNull(column1, 0))

It will be something like that causing the errors in the first place.
0
 
LVL 2

Author Comment

by:SmartestVEGA
ID: 24030412
This is problem
DELETE FROM E_In_AvgPriceMarSubcat

INSERT E_In_AvgPriceMarSubcat 

SELECT SubCat_ID, CASE WHEN SUM(SalesQty) = 0 THEN 0 ELSE SUM(SalesExVAT) / SUM(SalesQty) END avg_price_subcat, 

CASE WHEN SUM(SalesQty) = 0 THEN 0 ELSE SUM(BuyersMgn) / SUM(SalesQty) END avg_margin_subcat 

FROM t_in_article_sales tas

INNER JOIN t_in_ph ph ON ph.Article = tas.Article

AND tas.fiscalweek >= dbo.fn_getPreviousWeek(@latest_week, 52)

GROUP BY SubCat_ID

ORDER BY SubCat_ID

DELETE FROM E_In_AvgPriceMarBMC 

INSERT E_In_AvgPriceMarBMC 

SELECT BMC_ID, CASE WHEN SUM(SalesQty) = 0 THEN 0 ELSE SUM(SalesExVAT) / SUM(SalesQty) END avg_price_bmc, 

CASE WHEN SUM(SalesQty) = 0 THEN 0 ELSE SUM(BuyersMgn) / SUM(SalesQty) END avg_margin_bmc 

FROM t_in_article_sales tas

INNER JOIN t_in_ph ph ON ph.Article = tas.Article

AND tas.fiscalweek >= dbo.fn_getPreviousWeek(@latest_week, 52)

GROUP BY BMC_ID

ORDER BY BMC_ID

Open in new window

0
 
LVL 2

Author Comment

by:SmartestVEGA
ID: 24030626
OK i can eliminate
By changing
sum(SalesExVAT) as SalesExVAT,

to

sum(IsNull(SalesExVAT,0)) as SalesExVAT,


These warning can be eliminated:


Warning: Null value is eliminated by an aggregate or other SET operation.

how divide by zero?
0
 
LVL 7

Expert Comment

by:stelth240
ID: 24030693
It looks like there are CASE WHEN statements for each division operation so there shouldn't be any divide by zero errors now.  If there still are it must be somewhere else where it's happening.  Maybe you can run the code by hand and see what line it says it's failing on.
0
 
LVL 7

Expert Comment

by:stelth240
ID: 24030782
Justin, the Null values aren't responsible for the division by zero errors, just the warnings, which aren't bad per se.  The Null division would just return Null as the answer, like this code:

DECLARE @Num INT;
SET @Num = NULL;

SELECT 1 / @Num;

But to remove division by zero, it has to be a CASE WHEN statement that checks if the denominator is zero, like what I see in the code above.  Like this:

CASE WHEN SUM(SalesQty) = 0 THEN 0 ELSE SUM(SalesExVAT) / SUM(SalesQty) END

The only thing I can think of is the getPreviousWeek function might be trying to do a divide by zero but we can't see that code so I don't know for sure.  All the CASE WHEN statements look correct though for division by zero checks.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now