How to Retrieve Info from RAISERROR Function in SQL Server DB (120763)



The information in this article applies to:

  • Microsoft Visual Basic Professional Edition for Windows 3.0

This article was previously published under Q120763

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.
  1. 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
    
    						
  2. In Visual Basic, start a new project (ALT F, N). Form1 is created by default.
  3. Place a command button (Command1) on the form and enter the following code in the Click() event.

    Note You must change Username= <username> and PWD =<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.
       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;Username=<username>;PWD=<strong password>;"
          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
    
    						
  4. 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)

Modification Type:MajorLast Reviewed:11/7/2003
Keywords:KB120763