HELP: SQLServer Stored Procedure Error Control over DataReader

  • Thread starter Thread starter Hugo Ferreira
  • Start date Start date
H

Hugo Ferreira

Hi everyone,

I've got a huge problem here that I can't seem to be able to solve (hope
someone out there can give me an hand):

I've made a stored procedure that applies some actions, which can result in
an error. IF an error is produced (like a FK contraint), then it is logged
in a temporary table (i.e., #ERROR_TABLE) along with several other stuff I
need, and the execution continues. This stored procedure will be invoked
using an SqlDataReader. My logic is: if the DataReader returns any result,
then an error was produced; allowing me to react to the error inside my
application. If it doesn't return any rows, then all went ok.

Ok... So the problem here is: SqlDataReader is checking for errors generated
by the stored procedure invokation, and is throwing an exception. I can
catch this exception, but then I will not be able to access the resultset
with the temporary table I want to read. This temporary table contains vital
information about the error that I need to process. So I'm looking to one of
two things:

a) Either a way to tell SqlServer to don't report errors back to the client
(when executing a stored procedure) (XACT_ABORT OFF is not enough);
b) Or a way to tell SqlDataReader to DON'T THROW an exception, since there
is results in the resultset that I want to read!

Another way to get around the problem is to log the errors to a permanent
table. But the problem is that I'm inside a transaction, which will be
rollbacked if an error occurs. This rollback will affect the just inserted
row in the error-log table. If there is a way to issue a command inside a
stored procedure that is explicitly outside the current transaction, I would
be glad to know ;)

Thanks in advance,

Hugo Ferreira
 
Hugo Ferreira said:
Hi everyone,

I've got a huge problem here that I can't seem to be able to solve (hope
someone out there can give me an hand):

I've made a stored procedure that applies some actions, which can result
in
an error. IF an error is produced (like a FK contraint), then it is logged
in a temporary table (i.e., #ERROR_TABLE) along with several other stuff I
need, and the execution continues. This stored procedure will be invoked
using an SqlDataReader. My logic is: if the DataReader returns any result,
then an error was produced; allowing me to react to the error inside my
application. If it doesn't return any rows, then all went ok.

Ok... So the problem here is: SqlDataReader is checking for errors
generated
by the stored procedure invokation, and is throwing an exception. I can
catch this exception, but then I will not be able to access the resultset
with the temporary table I want to read. This temporary table contains
vital
information about the error that I need to process. So I'm looking to one
of
two things:

a) Either a way to tell SqlServer to don't report errors back to the
client
(when executing a stored procedure) (XACT_ABORT OFF is not enough);
Click to expand...

No can do.

The errors are just messages to the client, and SqlServer always reports
them. This is because SqlServer lacks any real error handling. This will
be fixed in Sql Server 2005.
b) Or a way to tell SqlDataReader to DON'T THROW an exception, since there
is results in the resultset that I want to read!
Click to expand...

No can do.

SqlClient is unfortunately hard-coded to throw a SqlException and ignore all
other data from the server whenever it gets a message with severity over (I
think) 10.
Another way to get around the problem is to log the errors to a permanent
table. But the problem is that I'm inside a transaction, which will be
rollbacked if an error occurs. This rollback will affect the just inserted
row in the error-log table. If there is a way to issue a command inside a
stored procedure that is explicitly outside the current transaction, I
would
be glad to know ;)
Click to expand...

That's called an "autonomous transaction". And Sql Server doesn't support
it.

So you're really stuck here. Due to these limitations in Sql Server and the
SqlClient, The approach you are taking simply won't work. There is really no
choice but to communicate the error information back to the client in the
errors (SqlExceptions). Use RAISERROR in TSQL to return an error message to
the client. SqlClient will throw a SqlException which will contain all the
errors you raised from the stored procedure, not just the first one.

Like this:

using (SqlConnection con = SQLConnect())
{
SqlCommand cmd = new SqlCommand(@"
create table #t(i int primary key);
insert into #t values(1);
insert into #t values(1);
raiserror('more information',16,10)
",con);
cmd.ExecuteNonQuery();

}

}
catch (SqlException ex)
{
foreach (SqlError err in ex.Errors)
{
Console.WriteLine(err.ToString());
}
}

David
 
Back
Top