locked
A SQL Server Divide by 0 error not returning properly RRS feed

  • 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

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:

    Transact-SQL

    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