Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Divide by zero error message Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 3:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:09 AM
Points: 249, Visits: 925
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!
Post #1441145
Posted Thursday, April 11, 2013 3:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 832, Visits: 2,436
could you try putting NULLIF round the bit that's causing the divide by zero error?
Post #1441159
Posted Thursday, April 11, 2013 3:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 PM
Points: 364, Visits: 384
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)

Post #1441495
Posted Thursday, April 11, 2013 3:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 13,239, Visits: 12,081
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1441498
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse