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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ABC - 25 Jan 2006 08:19 GMT

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?

Reply to this Message

Milosz Skalecki - 25 Jan 2006 14:25 GMT

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

Signature

Milosz Skalecki
MCP, MCAD

> How to retrieve all sql server errors on ADO.NET?
>
[quoted text clipped - 16 lines]
> But now I write a try .. catch statement to retrieve sqlexception, it only
> has one error. What happen?

Reply to this Message

Milosz Skalecki - 25 Jan 2006 14:33 GMT

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
{
}

Signature

Milosz Skalecki
MCP, MCAD

> Two ways:
>
[quoted text clipped - 95 lines]
> > But now I write a try .. catch statement to retrieve sqlexception, it only
> > has one error. What happen?

Reply to this Message

William (Bill) Vaughn - 25 Jan 2006 16:09 GMT

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.fir
einfomessageeventonusererrors.aspx
hth

Signature

____________________________________
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.
__________________________________

> How to retrieve all sql server errors on ADO.NET?
>
[quoted text clipped - 16 lines]
> But now I write a try .. catch statement to retrieve sqlexception, it only
> has one error. What happen?

Reply to this Message

peedi002@mc.duke.edu - 27 Jan 2006 13:33 GMT

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?

Reply to this Message

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

Free Magazines