Catch SQL Server's RAISERROR message

[Enzo Marinelli]

Hello everyone,

I have a HUGE database, with very complex stored procedures that handle every possible error and the passed it to an ASP site with RAISERROR.

Now I need to use that same database on an ASP.NET site, but all I can do is:

Try
Common.ExecuteNonQuery(QueryString, Common.Connection)
Catch Ex As SqlException
Result.Text = Ex.Message
Finally
Common.Connection.Close()
End Try

I really NEED to have the RAISERROR message... how can I do this?

[avnrao]

trap SQLException and log the same.

System.Exception innerException;
innerException = sqlE.InnerException;
while (innerException != null)
{
//logerror here
innerException = innerException.InnerException;
}
and use throw to raise the same error.

Av.

"Enzo Marinelli" <an*******@discussions.microsoft.com> wrote in message
news:7E**********************************@microsof t.com...

Hello everyone,

I have a HUGE database, with very complex stored procedures that handle
every possible error and the passed it to an ASP site with RAISERROR.

Now I need to use that same database on an ASP.NET site, but all I can do
is:

Try
Common.ExecuteNonQuery(QueryString, Common.Connection)
Catch Ex As SqlException
Result.Text = Ex.Message
Finally
Common.Connection.Close()
End Try

I really NEED to have the RAISERROR message... how can I do this?

[Enzo Marinelli]

Sorry, I really don't understand your solution :

Could you be a little more specific

And, if possible, use VB and not C#?

[avnrao]

let me make my understanding clear..
there are lot of stored procs which RaiseError and you want to trap the
message returned by RaiseError rgt?
Ex.Message returns that error isnt it? and you can also loop thru to find
out the inner exceptions.

Av.

"Enzo Marinelli" <an*******@discussions.microsoft.com> wrote in message
news:F9**********************************@microsof t.com...

Sorry, I really don't understand your solution :$

Could you be a little more specific?

And, if possible, use VB and not C#?

[Enzo Marinelli]

Yeah, that's what I want to do..

But by printing Ex.Message(), I get this

ERROR [23000] [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_User'. Cannot insert duplicate key in object 'User'. ERROR [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

And I need to show MY custom message, which I made by RAISERROR("That username is already taken. Please, choose another one",16,1)

[CT]

You can catch these messages by like this:

Public Class Test
Private Shared Sub OnInfoMessage(ByVal sender As Object, _
ByVal args As SqlInfoMessageEventArgs)
Dim objError As SqlError

' Loop through all the error messages
For Each objError In args.Errors
' Display the error properties
MessageBox.Show("The " & objError.Source & _
" has raised a warning on the " & _
objError.Server & " server. These are the properties :" & _
vbCrLf & vbCrLf & "Severity: " & objError.Class & vbCrLf & _
"Number: " & objError.Number & vbCrLf & "State: " & _
objError.State & vbCrLf & "Line: " & objError.LineNumber &
vbCrLf & _
"Procedure: " & objError.Procedure & vbCrLf & "Message: " & _
objError.Message)
Next

' Display the message and the source
MessageBox.Show("Info Message: " & args.Message & _
vbCrLf & "Info Source: " + args.Source)
End Sub

Public Sub TriggerInfoMessageEvent()
Dim cmmUserMan As SqlCommand = _
New SqlCommand("RAISERROR('This is an info message event.', 10,
1)", _
prcnnUserMan)
' Open the connection
prcnnUserMan.Open()
' Execute the T-SQL command
cmmUserMan.ExecuteNonQuery()
End Class

Alternatively, you use the AddHandler statement to catch the like this:

AddHandler connection.InfoMessage, New SqlInfoMessageEventHandler(AddressOf
OnInfoMessage)

The problem with the RAISERROR statement is that the severity must be set to
19 or higher for it to trigger a "normal" exception.

--
Carsten Thomsen
Enterprise Development with VS .NET, UML, and MSF
http://www.apress.com/book/bookDisplay.html?bID=105
"Enzo Marinelli" <an*******@discussions.microsoft.com> wrote in message
news:7E**********************************@microsof t.com...

Hello everyone,

I have a HUGE database, with very complex stored procedures that handle every possible error and the passed it to an ASP site with RAISERROR.
Now I need to use that same database on an ASP.NET site, but all I can do is:
Try
Common.ExecuteNonQuery(QueryString, Common.Connection)
Catch Ex As SqlException
Result.Text = Ex.Message
Finally
Common.Connection.Close()
End Try

I really NEED to have the RAISERROR message... how can I do this?

[Hans Kesting]

"Enzo Marinelli" <an*******@discussions.microsoft.com> wrote in message news:7E**********************************@microsof t.com...

Hello everyone,

I have a HUGE database, with very complex stored procedures that handle every possible error and the passed it to an ASP site with RAISERROR.
Now I need to use that same database on an ASP.NET site, but all I can do is:

Try
Common.ExecuteNonQuery(QueryString, Common.Connection)
Catch Ex As SqlException
Result.Text = Ex.Message
Finally
Common.Connection.Close()
End Try

I really NEED to have the RAISERROR message... how can I do this?

SqlException has an Errors collection, with all "RAISedERRORs" that happened
in the your SP.

The Error collection containes SqlError classes, that have members like Message.
Hans Kesting