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

  • Thread starter Thread starter ABC
  • Start date Start date
A

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


Milosz Skalecki said:
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 said:
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?
Click to expand...
Click to expand...
 
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.
__________________________________
 
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...
 
Back
Top