April 11, 2013 at 3:21 am
Dear All,
I'm getting the following error message Msg 8134, Level 16, State 1, Procedure "stored procedure name", Line 149
Divide by zero error encountered, and I don't seem to find a way to fix the issue. I've looked at some Forums and one of them suggested that using
SET ARITHABORT OFF
GO
SET ANSI_WARNINGS OFF
GO
Should resolve the problem, which it does but for some reason after I've altered the Stored Procedure to include it and reopen the SP, SET ARITHABORT OFF
GO
SET ANSI_WARNINGS OFF
GO is no longer there. I'm not sure what I'm missing or are there other ways to resolve the error message?
Thank you in advance!
April 11, 2013 at 3:43 am
could you try putting NULLIF round the bit that's causing the divide by zero error?
April 11, 2013 at 3:13 pm
Like the previous poster said, the best solution is to "armour plate" the divisions with nullif.
As for the SET ARITHABORT and SET ANSI_WARNINGS, it sounds like you had them outside the stored procedure itself. ("GO" is a batch separator, so it can't be inside the stored procedure code itself)
April 11, 2013 at 3:23 pm
Turning ANSI_WARNINGS off is dealing with the symptom not the problem. You need to make the code stop dividing by 0. You didn't post the code so we can't make much more of a suggestion than that. NULLIF, ISNULL are the most likely ways to avoid this situation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply