Microsoft KB Archive/120763

= How to Retrieve Info from RAISERROR Function in SQL Server DB =

Article ID: 120763

Article Last Modified on 11/7/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



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.

  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.

Note You must change Username= and PWD = 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= ;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: KB120763

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.