Using Severity with ADO.Net (Urgent)

[BuddyWork]

. Reply (E-mail) Forward (E-mail)

Subject: Using Severity with ADO.Net
From: "BuddyWork" <[email protected]>
Sent: 1/13/2004 5:56:58 AM




Hello,

I need to know if there is anyway that I can tell ADO.Net
NOT to throw a SQLException if the severity level is
greater than 10. I want to be able to control the
raiserror's myself because when ADO.Net throws an
exception it does not RETURN the resultset for the queries
performed after the error.

E.g.

EXEC myTestSP
-- SP myTestSP calls Raiserror with severity of 16.
SELECT Name from Bank

The SELECT statement calls is processed by SQL Server but
ADO.Net does not allow me to get the data where DBLib did.

Thanks
..

 

[Paul Clement]

¤ . Reply (E-mail) Forward (E-mail)
¤
¤ Subject: Using Severity with ADO.Net
¤ From: "BuddyWork" <[email protected]>
¤ Sent: 1/13/2004 5:56:58 AM
¤
¤
¤
¤
¤ Hello,
¤
¤ I need to know if there is anyway that I can tell ADO.Net
¤ NOT to throw a SQLException if the severity level is
¤ greater than 10. I want to be able to control the
¤ raiserror's myself because when ADO.Net throws an
¤ exception it does not RETURN the resultset for the queries
¤ performed after the error.
¤
¤ E.g.
¤
¤ EXEC myTestSP
¤ -- SP myTestSP calls Raiserror with severity of 16.
¤ SELECT Name from Bank
¤
¤ The SELECT statement calls is processed by SQL Server but
¤ ADO.Net does not allow me to get the data where DBLib did.

See if the following helps:

HOW TO: Return Errors and Warnings from a SQL Server Stored Procedure in ADO.NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;321903&Product=adonet


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[BuddyWork]

Hi Paul,

I've already seen that artical and it does not answer my
question. That artical states that severity of 10 and
lower does NOT throw an exception but severity of 11 and
above does. The problem I have is that we have been using
DBLibrary and are converting to ADO.Net, we found that if
DBLibrary does not check the severity levels as this was
introduced in SQL Server 7.0 and above, and Microsoft did
not update DBLibrary SDK. We do not really want to change
600 Stored Procedures and add the severity level to the
raiserror message because this is very expensive. We want
to know if there is any way within ADO.Net you can switch
the severity checking off. This is how DBLibrary works.

Thanks,

-----Original Message-----

 

[William Ryan]

YOu can use the InfoMessage event which will fire a message with a Severity
of < 10. http://www.knowdotnet.com/articles/connections.html


Hi Paul,

I've already seen that artical and it does not answer my
question. That artical states that severity of 10 and
lower does NOT throw an exception but severity of 11 and
above does. The problem I have is that we have been using
DBLibrary and are converting to ADO.Net, we found that if
DBLibrary does not check the severity levels as this was
introduced in SQL Server 7.0 and above, and Microsoft did
not update DBLibrary SDK. We do not really want to change
600 Stored Procedures and add the severity level to the
raiserror message because this is very expensive. We want
to know if there is any way within ADO.Net you can switch
the severity checking off. This is how DBLibrary works.

Thanks,

-----Original Message-----

 

[BuddyWork]

Hello William,

Please read my original message and it states that I'm
aware that InfoMessage event is triggered for Severity of
10 and less but because I'm porting code from DBLibrary, I
want to ignore the serverity checks, I want ADO.Net to
stop CHECKING the serverity and let me control the error
message within my code.

Basically the following codes shows the problem

DECLARE @Status CHAR(4)
EXEC myTest, @Status OUTPUT --Raise an error of severity
16, please note that my SP was written in version 4.5.
SELECT Status=@Status --I do not get this data back when
using ADO.Net but SQL Server procesess it, I do get the
data back when using DBLibrary.

Thanks

-----Original Message-----
YOu can use the InfoMessage event which will fire a message with a Severity
of < 10. http://www.knowdotnet.com/articles/connections.html


Hi Paul,

I've already seen that artical and it does not answer my
question. That artical states that severity of 10 and
lower does NOT throw an exception but severity of 11 and
above does. The problem I have is that we have been using
DBLibrary and are converting to ADO.Net, we found that if
DBLibrary does not check the severity levels as this was
introduced in SQL Server 7.0 and above, and Microsoft did
not update DBLibrary SDK. We do not really want to change
600 Stored Procedures and add the severity level to the
raiserror message because this is very expensive. We want
to know if there is any way within ADO.Net you can switch
the severity checking off. This is how DBLibrary works.

Thanks,

 

[Paul Clement]

¤ Hi Paul,
¤
¤ I've already seen that artical and it does not answer my
¤ question. That artical states that severity of 10 and
¤ lower does NOT throw an exception but severity of 11 and
¤ above does. The problem I have is that we have been using
¤ DBLibrary and are converting to ADO.Net, we found that if
¤ DBLibrary does not check the severity levels as this was
¤ introduced in SQL Server 7.0 and above, and Microsoft did
¤ not update DBLibrary SDK. We do not really want to change
¤ 600 Stored Procedures and add the severity level to the
¤ raiserror message because this is very expensive. We want
¤ to know if there is any way within ADO.Net you can switch
¤ the severity checking off. This is how DBLibrary works.
¤

AFAIK the exception handling mechanism is hardwired within the native .NET managed SQL Server
provider. I'm not aware of any way to bypass this mechanism and return exceptions in output
parameters of your stored procedures.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)