July 30, 2007 at 11:37 am
I'm using SQl Server 200 SP3 standard. I have an Agent job that runs a script calling several stored procedures. The job failed yesterday with "Divide by zero error encountered. [SQLSTATE 22012] (Error 8134)". But I made a pass through the procs and in every instance where I divide a number, it's on an update with "column_name <> 0" in the WHERE clause. Is there something else that would cause a divide by zero error?
I have a log table that stores the start and end time of each stored proc, so I figured I could tell which procedure it died in by looking for a null value in the end time, but it looks like each one ended. Then I thought I could look at the job history in the Agent job log file viewer and see which one it was executing when the job failed, but the job history says that the job failed after the last proc ended. Arithmetic abort is set to true for this database. Is there a better way to trap and log these errors? Thanks.
July 30, 2007 at 12:58 pm
Off the top of my head, I could see something like a Null value in the column, which would get it passed the WHERE clause, with an Isnull(column,0) as a denominator in the calculation that causes the error.
July 30, 2007 at 2:50 pm
Whole query would be useful.
It could be anything.
For example, implicit conversion. Some DECIMAL value may be 0 but being converted to, say, real becomes zero. Or other way around.
So, post the query.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply