Answered by:
A SQL Server Divide by 0 error not returning properly

Question
-
I have a problem with SQL Server not returning an error when a divide by 0 occur.
I've created s short VB.net program below to show this. It calls SQL server 2005 twice, They both should error out with a divide by 0. However, only the 2nd block of code is caught. When I run the SQL through Mgmt Studio, both do error out.
Am I missing something in my code?
-------------
Module Module1
Sub Main()
Dim adoCon As New SqlConnection
Dim adoCmd As New SqlCommand
Dim NewRec As SqlDataReader
Dim txt As String = ""
Try
adoCon.ConnectionString = "Data Source=SQLSMST0;Initial Catalog=master;User ID=guest;Password=???;Connect Timeout=40"
adoCon.Open()
adoCmd.Connection = adoCon
txt = "block1"
adoCmd.CommandText = "select 1/0"
adoCmd.CommandType = CommandType.Text
NewRec = adoCmd.ExecuteReader()
NewRec.Close()
Catch ex As Exception
Debug.Print(txt)
End Try
Try
txt = "block2"
adoCmd.CommandText = "declare @i as integer; select @i=1/0"
adoCmd.CommandType = CommandType.Text
NewRec = adoCmd.ExecuteReader()
Catch ex As Exception
Debug.Print(txt)
End Try
End Sub
End Module
- Edited by wud5 Monday, November 1, 2010 6:34 PM lost carriage returns in the original post
- Moved by Liliane Teng Monday, November 8, 2010 9:39 AM (From:Visual Basic General)
Monday, November 1, 2010 5:54 PM
Answers
-
I think the following would be the forum to post to:
You might find this useful too:
http://www.sommarskog.se/error_handling_2005.html
HTH,
Andrew
- Marked as answer by Liliane Teng Monday, November 8, 2010 9:39 AM
Tuesday, November 2, 2010 9:23 AM
All replies
-
What do the calls actually return? Bear in mind that when you're using SQLSMS, some settings are different from what is used when you make the call from elsewhere. A query to/search of an SQL Server forum will hopefully provide the grisly details. What problem are you encountering with your real code?
--
Andrew- Proposed as answer by Cor Ligthert Tuesday, November 2, 2010 7:28 AM
Monday, November 1, 2010 10:55 PM -
I wasn't quite sure where to post it: SQL Server, VB or ADO...so I tried posting here first. SQL Server was able to catch the error correctly.
In my real code, it is an sp (1000+ line of horrid code). It didn't have any errors. Our business staff insisted there was a problem and I track it down to this uncatchable divide by 0. Apparently, the sp hit the divide by 0, stopped all further processing but returned a OK.
I can put in a try-catch block in the sp to prevent this from happening in this sp but its not a solution to code this for all our sps in our systems.
Any pointers or direction in debugging this problem would be appreciated as I don't even know where to start looking at this.
Tuesday, November 2, 2010 12:11 AM -
I think the following would be the forum to post to:
You might find this useful too:
http://www.sommarskog.se/error_handling_2005.html
HTH,
Andrew
- Marked as answer by Liliane Teng Monday, November 8, 2010 9:39 AM
Tuesday, November 2, 2010 9:23 AM