SQL – Divide by zero error

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.

SQL - Divide by zero error 1

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

Was this article helpful?

Related Articles