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

[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

 

[Apokrif]

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.

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)

 

[William \(Bill\) Vaughn]

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

 

[Apokrif]

From: "Apokrif" <[email protected]>

Nice nickname.