I am using the .net odbc data providder to execute a stored procedure on Sql
Server 2000. The stored procedure uses @raiserror in case of an error.
However, when I try to catch the exception in c# code I can’t get it. Any
ideas? (before anyone suggest using the Sql data provider instead I have no
choice but to use odbc as it is company policy!!)

try
{
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
// Doesn't get here using ODBC
MessageBox.Show(ex.Message);
}
finally
{
cn.Close();
}



--
Scott

Hi Scott,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you were unable to catch the error text
that a stored procedure has thrown from SQL Server 2000. If there is any
misunderstanding, please feel free to let me know.

I tried your code on my machine. However, it seems to be working fine. I
created stored procedure like the following:

CREATE PROCEDURE dbo.sperror
AS
raiserror('sperror raises an error',16,1)
GO

A message box with the following message was shown.

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]sperror raises
an error

Would you try it on your computer to see if it works?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

The problem seems to occur if there is an if statement in the sql statement
e.g. try

CREATE PROCEDURE dbo.sperror
AS
If 1 = 1
Begin
raiserror('sperror raises an error',16,1)
End
GO


"Kevin Yu [MSFT]" wrote:

Hi Scott,

Based on my research, this is a known issue for ODBC providers. For
workaround, we can add SET NOCOUNT ON before raising the error in the
sproc, and the exception will be caught by your program. Here is an example:

CREATE PROCEDURE dbo.sperror
AS
set nocount on
If 1 = 1
Begin
raiserror('sperror raises an error',16,1)
End
GO

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."