Solved

job failing

Posted on 2009-03-31
10
1,320 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

829 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