Possible Bug in SqlClient.ExecuteReader

[BuddyWork]

Hello,

The problem is that when using SqlCommand.ExecuteReader
and the SQL statement raises an error of severity of 16
then ExecuteReader throws an exception, when you use
OdbcCommand.ExecuteReader the exception is not raised but
when you call Read() method of the OdbcDataReader you then
get the exception which is correct, basically SqlClient
does not let you call the Read method, the main problem is
that if run a Sql statement of say
'Need to create the following SP first.
create proc test
as
rollback tran
go

now run the following SQL through SqlCommand and then
OdbcCommand
begin tran
execute test
select hello="World"

you will notice that SqlCommand throws an exception on
ExecuteReader function where OdbcCommand does not, so Odbc
allows you to call Read and NextResult to go through the
recordset and retrieve your data, wher SqlClient does not
because you don't get the SqlDataReader object back.

Thanks,

 

[William Ryan eMVP]

Buddy:

If that's the proc, then your problem is not the reader although an
exception may be getting raised there because of the proc itself: This may
help you...

<<Server: Msg 3903, Level 16, State 1, Procedure test, Line 3
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.>>

If your proc blows up, there's nothing to read. So you should get a Null
Reference Exception which is going to be the case if you don't have a valid
reason for any reason.

HTH,

Bill

 

[David Browne]

Hello,

The problem is that when using SqlCommand.ExecuteReader
and the SQL statement raises an error of severity of 16
then ExecuteReader throws an exception, when you use
OdbcCommand.ExecuteReader the exception is not raised but
when you call Read() method of the OdbcDataReader you then
get the exception which is correct, basically SqlClient
does not let you call the Read method, the main problem is
that if run a Sql statement of say

SqlServer clients recieve data through 3 mechanisms. Cursors, messages and
parameters. An error is just a kind of message. SqlServer is capable of
retuning any combination and number of these, but a particular client may
not support certian combinations. The famous "set nocount on" problem with
old ADO was an example of this.

When a statement returns both an error message and a Cursor, the library
designer must make a choice. In ODBC the choice was made not to throw an
exception, in SQLClient the opposite choice was made.

Which one is right? I think SqlClient's approach is more appropriate for a
..NET environment. The general rule has to be that an error message
generates a SqlException, even if the exception requires you to discard
certian valid data.

The basic problem here is that TSQL has horrible exception handling, and
will blithly continue processing after an error is generated. This is
really a defect in TSQL, and TSQL code which both generates error messages
and returns data is basically broken. You should never continue running
TSQL statements after a error has occured, but without structured
exceptions, you must check for the error condition manually. So to fix this
check for @@error after any TSQL statement that might have generated an
error and don't process any more.


Something like this:

begin tran
execute test
if @@error <> 0 return
select hello="World"


David

 

[BuddyWork]

Thanks for the reply,

But I don't think you understood my question, basically
the problem is that Odbc.ExecuteReader does NOT THROW AN
EXCEPTION whereas SqlClient.ExecuteReader does THROW AN
EXCEPTION, so because SqlClient.ExecuteReader THROWS AN
EXCEPTOIN there is NO WAY of retrieving the SqlDataReader
object which then means you CANNOT go through the
recordset for each MULTIPLE results returned, I've looked
at the IL code of SqlClient and can see that data is there
but SqlClient refuses you to get to it, when Odbc.Net
allows you to access the data.

Thanks

 

[BuddyWork]

Thanks for the reply,

But I don't think you understood my question, basically
the problem is that Odbc.ExecuteReader does NOT THROW AN
EXCEPTION whereas SqlClient.ExecuteReader does THROW AN
EXCEPTION, so because SqlClient.ExecuteReader THROWS AN
EXCEPTOIN there is NO WAY of retrieving the SqlDataReader
object which then means you CANNOT go through the
recordset for each MULTIPLE results returned, I've looked
at the IL code of SqlClient and can see that data is there
but SqlClient refuses you to get to it, when Odbc.Net
allows you to access the data.

Thanks

 

[David Browne]

Thanks for the reply,

But I don't think you understood my question, basically
the problem is that Odbc.ExecuteReader does NOT THROW AN
EXCEPTION

But I would say that that's the bug.

whereas SqlClient.ExecuteReader does THROW AN
EXCEPTION, so because SqlClient.ExecuteReader THROWS AN
EXCEPTOIN there is NO WAY of retrieving the SqlDataReader.

Right. SqlClient always throws a SqlException when an error message is
generated. That's a good thing because it's a simple and consistent rule,
and the only time it's inconvienent is with TSQL code that's badly written
to start with.

This is really kind of an OO/SQL mismatch issue. In .NET program units
communicate only through parameters, return values and exceptions. And when
an exception is raised, you cannot access return values or parameters. TSQL
just doesn't work that way, and must be coerced into it inside the driver.
The upshot is that there are a few (mostly useless) things you can do in
TSQL that you cannot do with SQLClient. The right answer, then, is it to
prevent those things from happening as a matter of TSQL coding practice.

David

 

[BuddyHome]

Thanks for the Reply David but the issue here is that due to code that was
written 10 years ago using Sql Server 4.2 we where using DBLibrary and
because the flexibility of the libray it ignored the serverity errors. We
are now converting how code and not the stored procedures to .Net so we
started to use the SqlClient provider and found this problem, if we use the
Odbc.Net provider then it works as DBLibrary.

Example
try
{
SqlDataReader dr = sqlCommand.ExecuteReader("...", con );
...dr.Read()...
}
catch(...)

The above code will throw an exception and will not get to the line dr.Read
because and dr is set to null whereas Odbc.Net will Not throw and exception
on ExecuteReader but will throw it on dr.Read() so this allows me to go to
the next recordset. This is how DbLibrary works. I've debugged into the IL
code of SqlClient and found that the data is there.

I've got someone from Microsoft looking into this.

Thanks

 

[David Browne]

Thanks for the Reply David but the issue here is that due to code that was
written 10 years ago using Sql Server 4.2 we where using DBLibrary and
because the flexibility of the libray it ignored the serverity errors. We
are now converting how code and not the stored procedures to .Net so we
started to use the SqlClient provider and found this problem, if we use the
Odbc.Net provider then it works as DBLibrary.

Ouch. Bummer. I kind of assumed you could change the TSQL.

SqlClient has a message severity threshold of 10. The instant SqlClient
gets a message with severity 10 or greater it aborts the current operation
and throws it as as SqlException. If the severity is less than 10, then the
message is posted as an InfoMessage and processing continues.

If you could configure that threshold up, or the severity of you message
down, you'd get what you want. The error severities are stored in
master..sysmessages, but that's a server-wide system table. And as far as I
know the SqlClient severity threshold is hard-coded. Perhaps MS can provide
you with a means to configure the threshold for a SqlConnection.

Or, failing that, from what I've heard Odbc.Net isn't so bad. Better than
OleDb.NET.

David