How to retrieve all sql server errors on ADO.NET?

25 Jan 2006 8:19 AM

ABC

How to retrieve all sql server errors on ADO.NET?

I have a stored procedure which will validate data and raiserrors when any
error value inputted?

Example from my stored procedure when running sample data.

Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 52
Campaign Code must be inputted!
Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 58
Campaign Description must be inputted!
Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 64
Campaign Start Date must be inputted!
Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 70
Campaign End Date must be inputted!

(1 row(s) affected)

But now I write a try .. catch statement to retrieve sqlexception, it only
has one error. What happen?

25 Jan 2006 2:25 PM

Milosz Skalecki

Two ways:

1. When you use RAISERROR with serverity set above 10 in you stored proc,
SqlException will be raised with Collection of all errors encountered during
execution:
T-SQL:

CREATE PROCEDURE [VeryNastyProcedure]
AS
RAISERROR ('Error Message 1', 11, 16)
RAISERROR ('Error Message 2', 11, 16)
RAISERROR ('Error Message 3', 11, 16)
GO

C#:

command.ComandText = "VeryNastyProcedure";
command.CommandType = CommandType.StoredProcedure;

try
{
    connection.Open();
    command.ExecuteNonQuery();
}
catch (SqlException ex)
{
    foreach (SqlError e in ex.Errors)
    {
          text1.Text += e.Message;
    }
}
finally
{
....
}

2. When you use RAISERROR with serverity set to <= 10 in you stored proc,
you have to handle InfoMessage event of your connection object:

CREATE PROCEDURE [VeryNastyProcedure]
AS
RAISERROR ('Error Message 1', 1, 16)
RAISERROR ('Error Message 2', 1, 16)
RAISERROR ('Error Message 3', 1, 16)
GO

C#:

command.ComandText = "VeryNastyProcedure";
command.CommandType = CommandType.StoredProcedure;
connection.InfoMessage += new
SqlInfoMessageEventHandler(connection_InfoMessage);

try
{
    connection.Open();
    command.ExecuteNonQuery();
}
catch (SqlException ex)
{
    foreach (SqlError e in ex.Errors)
    {
          text1.Text += e.Message;
    }
}
finally
{
....
}

and the event handler:

private void connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    text1.Text = e.Message;
}

Hope this helps

--
Milosz Skalecki
MCP, MCAD

Show quoteHide quote

"ABC" wrote:

> How to retrieve all sql server errors on ADO.NET?
>
> I have a stored procedure which will validate data and raiserrors when any
> error value inputted?
>
> Example from my stored procedure when running sample data.
>
>
>
> Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 52
> Campaign Code must be inputted!
> Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 58
> Campaign Description must be inputted!
> Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 64
> Campaign Start Date must be inputted!
> Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 70
> Campaign End Date must be inputted!
>
> (1 row(s) affected)
>
>
>
> But now I write a try .. catch statement to retrieve sqlexception, it only
> has one error. What happen?
>
>
>
>
>

25 Jan 2006 2:33 PM

Milosz Skalecki

Small mistake, in the secon approach exception won't be raised. You just
assign event handler.

try
{

}
catch (SqlException ex)
{
// this line won't be riched
}
finally
{
}

--
Milosz Skalecki
MCP, MCAD

Show quoteHide quote

"Milosz Skalecki" wrote:

> Two ways:
>
> 1. When you use RAISERROR with serverity set above 10 in you stored proc,
> SqlException will be raised with Collection of all errors encountered during
> execution:
> T-SQL:
>
> CREATE PROCEDURE [VeryNastyProcedure]
> AS
> RAISERROR ('Error Message 1', 11, 16)
> RAISERROR ('Error Message 2', 11, 16)
> RAISERROR ('Error Message 3', 11, 16)
> GO
>
> C#:
>
> command.ComandText = "VeryNastyProcedure";
> command.CommandType = CommandType.StoredProcedure;
>
> try
> {
>     connection.Open();
>     command.ExecuteNonQuery();
> }
> catch (SqlException ex)
> {
>     foreach (SqlError e in ex.Errors)
>     {
>           text1.Text += e.Message;
>     }
> }
> finally
> {
> ...
> }
>
>
> 2. When you use RAISERROR with serverity set to <= 10 in you stored proc,
> you have to handle InfoMessage event of your connection object:
>
> CREATE PROCEDURE [VeryNastyProcedure]
> AS
> RAISERROR ('Error Message 1', 1, 16)
> RAISERROR ('Error Message 2', 1, 16)
> RAISERROR ('Error Message 3', 1, 16)
> GO
>
> C#:
>
> command.ComandText = "VeryNastyProcedure";
> command.CommandType = CommandType.StoredProcedure;
> connection.InfoMessage += new
> SqlInfoMessageEventHandler(connection_InfoMessage);
>
> try
> {
>     connection.Open();
>     command.ExecuteNonQuery();
> }
> catch (SqlException ex)
> {
>     foreach (SqlError e in ex.Errors)
>     {
>           text1.Text += e.Message;
>     }
> }
> finally
> {
> ...
> }
>
> and the event handler:
>
> private void connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
> {
>     text1.Text = e.Message;
> }
>
> Hope this helps
> --
> Milosz Skalecki
> MCP, MCAD
>
>
> "ABC" wrote:
>
> > How to retrieve all sql server errors on ADO.NET?
> >
> > I have a stored procedure which will validate data and raiserrors when any
> > error value inputted?
> >
> > Example from my stored procedure when running sample data.
> >
> >
> >
> > Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 52
> > Campaign Code must be inputted!
> > Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 58
> > Campaign Description must be inputted!
> > Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 64
> > Campaign Start Date must be inputted!
> > Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 70
> > Campaign End Date must be inputted!
> >
> > (1 row(s) affected)
> >
> >
> >
> > But now I write a try .. catch statement to retrieve sqlexception, it only
> > has one error. What happen?
> >
> >
> >
> >
> >

25 Jan 2006 4:09 PM

William (Bill) Vaughn

Enable and handle the InfoMessage event and set the new
FireInfoMessageEventOnUserErrors property to
YesIReallyWantToCatchInfoMessages or True.
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.fireinfomessageeventonusererrors.aspx
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Show quoteHide quote

"ABC" <a**@abc.com> wrote in message
news:%23BmJQgYIGHA.1124@TK2MSFTNGP10.phx.gbl...
> How to retrieve all sql server errors on ADO.NET?
>
> I have a stored procedure which will validate data and raiserrors when any
> error value inputted?
>
> Example from my stored procedure when running sample data.
>
>
>
> Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 52
> Campaign Code must be inputted!
> Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 58
> Campaign Description must be inputted!
> Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 64
> Campaign Start Date must be inputted!
> Server: Msg 50000, Level 16, State 1, Procedure proc_Validation, Line 70
> Campaign End Date must be inputted!
>
> (1 row(s) affected)
>
>
>
> But now I write a try .. catch statement to retrieve sqlexception, it only
> has one error. What happen?
>
>
>
>

27 Jan 2006 1:33 PM

peedi002@mc.duke.edu

I am getting the same error (Msg 50000, Level 16, State 1) without the msg, when the following is executed from a sp, in a nightly batch... But, it is not consistent - sometimes I do not get it...

CREATE TABLE #SubRec (SR_iID int IDENTITY(1,1), SR_SQQUEDNR_cQuestion_Display varchar(10), SR_DD_iID int null)

Any ideas why I am getting the error?

**********************************************************************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...