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

[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?

 

[Guest]

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

 

[Guest]

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

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

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?

 

[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/li...nection.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.
__________________________________

 

[peedi002]

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