Problem
When running an SQL view using SQL Management Studio you experience the following message:
MSG 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
Solution
This issue is typically due to a poorly written SQL view – the coder has not properly considered 0’s in the data – now the view is trying to calculate (divide) using 0 and is not sure how to proceed.
Ideally you need to examine the code, what it is trying to calculate and fix it so it doesn’t happen – however a quick an easy solution exists.
Add the following lines to the top of the view, it will make the ‘divide by zero’ calculations give a NULL value – allowing the view to run.
SET ARITHABORT OFF SET ANSI_WARNINGS OFF
If you have access to the code you can fix the issue by wrapping any potential null values with
nullif(database.field, 0) for example –
database.field1 / nullif(database.field2, 0)
.
More information on this can be found here: http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql