How to get all result sets and all error messages from MS SQL 2000?

  • Thread starter Thread starter Apokrif
  • Start date Start date
A

Apokrif

There is a stored procedure with following code:

....
RAISERROR 30000 'xxxx'
SELECT 1 as F1
RAISERROR 30000 'zzzz'
....

In C# I have something like that:
.....
string _exceptionMessage = "";
SqlCommand cmd = new SqlCommand(sql,conn);
try
{
SqlDataReader rdr = cmd.ExecuteReader();
do
{
while(rdr.Read())
{
//do something
....
}
}
while(rdr.NextResult());
}
catch(SqlException e)
{
for(int j=0;j<e.Errors.Count;j++)
{
_exceptionMessage += e.Errors[j].Number.ToString()+" -
"+e.Errors[j].Message+"\r\n";
}
}
.....
So I want to get two error messages and one result set.
But I got exception in ExecuteReader() so I didn't get access to
DataReader. And I don't understand how to get everything from sql
server.
Even I can't get all error messages because within catch I always have
e.Errors.Count equal 1.

When I run my stored procedure in QueryAnalyzer I have no problem.
I can see everything as output:

Server: Msg 30000, Level 16, State 1, Procedure CompanyGetInfo, Line
31
xxxx
F1
-----------
1
(1 row(s) affected)
Server: Msg 30000, Level 16, State 1, Procedure CompanyGetInfo, Line
33
zzzz

Any help will be appreciated.

In the best we trust
George Nevsky
 
Unless the severity is 10 or higher, the exception
handler won't kick in.
You need to add a InfoMessage event handler to your code to trap the message
sent back from RAISERROR.
Click to expand...

Yes, I found OnInfoMessage event but I never get that
message if raiserror with severity > 10 happened before
exception with severity <10.
I.e. I can't get info message from following T-SQL code:
RAISERROR('error',16,1)
RAISERROR('info',10,1)
 
That's because high sev errors are passed back to ADO.NET and it gives up on
trying to process more data from the TDS stream.

--
____________________________________
Bill Vaughn
MVP, hRD
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.
__________________________________
 
Back
Top