job failing

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?
LVL 2
SmartestVEGAAsked:
Who is Participating?
 
justin-clarkeConnect With a Mentor Commented:
SET ANSI_WARNINGS OFF

Use that at the top of your stored procedure
0
 
SmartestVEGAAuthor Commented:

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
 
vinurajrConnect With a Mentor Commented:
SET ANSI_DEFAULTS { ON | OFF }
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
stelth240Connect With a Mentor Commented:
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
 
SmartestVEGAAuthor Commented:
Yes it is divide by zero ,several times in my sp is there any alternatives?
0
 
justin-clarkeConnect With a Mentor Commented:
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
 
SmartestVEGAAuthor Commented:
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
 
SmartestVEGAAuthor Commented:
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
 
stelth240Commented:
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
 
stelth240Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.