How to Retrieve Info from RAISERROR Function in SQL Server DB

ID: Q120763

The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows, version 3.0

SUMMARY

This article explains how to return the error message generated by the RAISERROR function in Microsoft SQL Server. You can use the RAISERROR function in a SQL stored procedure or trigger on a SQL Server database to set a global error number and return a user-defined error message.

MORE INFORMATION

When Visual Basic encounters a return value from the RAISERROR function, all it knows is that the "ODBC Call Failed" and Err is set to 3146. The entire ODBC error message is included in the Error$ function. To return the entire error message generated by the RAISERROR function, use the Error$ function in your error trap routine. For example:


   If Err = 3146 then
      MsgBox Error$
   End If

Note that this is different from performing a 'MsgBox Error$(Err)' command, which returns only the "ODBC Call Failed" message.

Unfortunately, the only way to return the error number set by RAISERROR is to include the number in the error message and parse the return string. However, the Microsoft Access 2.0/Visual Basic 3.0 Compatibility Layer appends the error number to the end of the error message automatically.

Step-by-Step Example

The following demonstrates an example stored procedure that generates a RAISERROR message.

In the SQL Administration Facility (SAF), run the following code to create the stored procedure:


   create procedure my_error @custname varchar(18) = null
   as
   if @custname is null
      begin
      raiserror 50001 'You Must Enter a Field Name! (#50001)'
   end
   else
      begin
      select * from TempTbl
      where TempTbl.Last_Name = @custname
   end

In Visual Basic, start a new project (ALT F, N). Form1 is created by default.

Place a command button (Command1) on the form and enter the following code in the Click() event:


   Sub Command1_Click ()
      Const DB_SQLPassThrough = 64  ' Set the passthrough constant.

      Dim db As database            ' Dimension the local variables.
      Dim ds As dynaset
      Dim conn As String
      Dim sql As String

      On Error GoTo Trap            ' Set up the error trap.

      ' Append the SQL Server database:
      conn = "odbc;dsn=texas;database=playpen;uid=sa;pwd=;"
      Set db = OpenDatabase("", False, False, conn)

      ' Run the stored procedure:
      sql = "my_error"
      Set ds = db.CreateDynaset(sql, DB_SQLPassThrough)

      ' Print the returned record (never get here because of the error).
      For i = 0 To ds.Fields.Count - 1
         Print ds(i)
      Next i
   Exit Sub

   Trap:
      If Err = 3146 Then   ' ODBC call failed.
          MsgBox Error$
      Else
          MsgBox "Error:" & Err & "-" & Error$
      End If
      On Error GoTo 0
      Exit Sub
   End Sub

Press the F5 key to run the program. Click the command button, and you should get error 3146 with the complete error message including the ODBC information and the following user-defined error message:

You Must Enter a Field Name! (#50001)

Additional query words: 3.00

Keywords :
Version :
Platform :
Issue type :