ExecuteReader() not firing exceptions from stored procedures, also HasRows() vs Read()

[James Hokes]

Hi All,

We're using the 1.1. Framework against SQL Server 2000, and are having a
strange issue where we don't get errors back from the stored procedure, i.e.
the exception never gets thrown.

The scenario is this:
1.) Set up SqlConnection and SqlCommand
2.) Set SqlDataReader equal to the return value from
SqlCommand.ExecuteReader
3.) Check SqlDataReader.HasRows() to see if there is any data in the stream

If we do these things, but do not call SqlDataReader.Read(), we never get
any exceptions from the stored procedure.
Our understanding was that you want to check SqlDataReader.HasRows() before
doing anything, in case there was no data returned.
This apparently does not work.
The mysterious part is why the exception does not fire on step #2
(SqlCommand.ExecuteReader() ).


We use SqlDataReader objects to stream stored procedure results back when we
have a smallrowsets.

Normally, our code looks like so:

'...set up connection and command...
rdr = cmd.ExecuteReader

While rdr.Read
'Process row...
Loop

'et cetera...

In this case, the exception fires normally.


However, one of our developers tried:

If rdr.HasRows Then
While rdr.Read
'Process row..
Loop
End If

This results in the total suppression of SQL Server errors that occurred in
the stored procedure.

Why does this occur?
Of what use is HasRows() if it doesn't throw the exceptions that are
apparently waiting in the SqlDataReader stream?

Are there any other "scenarios" we should be aware of which would suppress
the SQL Exceptions? This is scary.

Here is the necessary repro information:
/*SQL Server 2000*/

USE Northwind
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[ADOTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[ADOTest]
GO

IF EXISTS (SELECT * from dbo.sysobjects WHERE id =
object_id(N'[dbo].[selADOTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[selADOTest]
GO

CREATE TABLE
ADOTest
(
DateVal varchar(19) NOT NULL
)
GO

INSERT INTO ADOTest (DateVal) VALUES ('2005-02-2811:55.000')
GO

CREATE PROCEDURE dbo.selADOTest
AS

SET NOCOUNT ON

SELECT CONVERT(datetime, DateVal) AS DateVal FROM ADOTest
GO

GRANT EXECUTE ON selADOTest TO public
GO



'VB.Net: (Paste code into the click event of a button on a Windows Form):

Dim conn As System.Data.SqlClient.SqlConnection, cmd As
System.Data.SqlClient.SqlCommand, rdr As System.Data.SqlClient.SqlDataReader

Dim hasRows As Boolean

Try

conn = New
System.Data.SqlClient.SqlConnection("SERVER=DEV;DATABASE=Northwind;Integrated
Security=SSPI;")

conn.Open()

cmd = conn.CreateCommand

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "dbo.selADOTest"

rdr = cmd.ExecuteReader

If rdr.HasRows Then

While rdr.Read

Debug.WriteLine(rdr("DateVal"))

End While

End If

MsgBox("Successfully executed stored procedure.")



Catch sqlEx As System.Data.SqlClient.SqlException

MsgBox("SQLException: " & sqlEx.Message)

Catch appEx As ApplicationException

MsgBox("ApplicationException: " & appEx.Message)

Finally

If Not IsNothing(rdr) Then

rdr.Close()

rdr = Nothing

End If

If Not IsNothing(cmd) Then

cmd = Nothing

End If

If Not IsNothing(conn) Then

If conn.State = ConnectionState.Open Then

conn.Close()

End If

conn = Nothing

End If

End Try

'Try it with the "If rdr.HasRows() Then.." and also without.
'You'll see what I mean.
'The exception will not fire with the HasRows() check in place.




Thanks,
James Hokes

 

[David Browne]

Hi All,

We're using the 1.1. Framework against SQL Server 2000, and are having a
strange issue where we don't get errors back from the stored procedure,
i.e.
the exception never gets thrown.

The scenario is this:
1.) Set up SqlConnection and SqlCommand
2.) Set SqlDataReader equal to the return value from
SqlCommand.ExecuteReader
3.) Check SqlDataReader.HasRows() to see if there is any data in the
stream

If we do these things, but do not call SqlDataReader.Read(), we never get
any exceptions from the stored procedure.
Our understanding was that you want to check SqlDataReader.HasRows()
before doing anything, in case there was no data returned.
This apparently does not work.
The mysterious part is why the exception does not fire on step #2
(SqlCommand.ExecuteReader() ).

The Issue:

SqlDataReader.HasRows() returns false instead of throwing a SqlException if
the first row contains a data conversion error.

The issue is not limited to stored procedures. Here's a simplified repro:


dim conn as SqlConnection = ...
dim cmd as New SqlCommand("SELECT convert(datetime,'2005-02-2811:55.000') as
DateVal", conn)
dim rdr as SqlDataReader = cmd.ExecuteReader
If rdr.HasRows Then
While rdr.Read
Debug.WriteLine(rdr("DateVal"))
End While
End If

This exception shouldn't be thrown until the row is fetched. If the
conversion happened on the 50th row instead of the first, then you could
fetch the first 39 without error.

Obvious workarounds include using a DataSet, and not using HasRows.


David

 

[James Hokes]

David,

Thank you very much!

I believe we'll institute a general avoidance of using SqlDataReader now, in
favor of the DataSet approach.

Our testing shows that filling the DataSet causes the exceptions to be
thrown every time.

It seems much nicer to know this is by design, rather than accident.

Much gratitude,
James Hokes

 

[Michael C#]

Before you give up completely on your current approach, give this a shot:
try adding RETURN @@Error to the end of your Stored Procedure:

CREATE PROCEDURE dbo.selADOTest
AS

SET NOCOUNT ON

SELECT CONVERT(datetime, DateVal) AS DateVal FROM ADOTest
RETURN @@Error
GO

 

[Michael C#]

My example still didn't throw the exception you're looking for. Sorry about
that. Checking .HasRows helps you avoid having the Exception thrown, which
is the reason to use it in the first place. Another option might be to tap
the SqlInfoMessageEventHandler delegate for your SqlConnection. I haven't
tried it personally, but it might be interesting to look into...

Thanks,
Mike C.

 

[Pablo Castro [MS]]

This is a known issue and we're working on having a better story in the next
version.

Note that if you use a pattern like the one below, you're more likely to get
all the messages/errors:

reader = cmd.ExecuteReader();
do {
while(reader.Read()) {
// ... process row
}
} while(reader.NextResult());

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[James Hokes]

Mr. Castro,

Thanks for posting. This is good information, and I appreciate your time.

When using SqlDataReader, we will implement the pattern as you suggest; this
should even catch those other issues, like the SqlDataReader/Deadlock error
non-propagation which we are also aware of. My testing also seemed to reveal
similar behavior today with ExecuteScalar(); I will work more on that angle
next week.

More favorably, however, I think we'll lean toward DataSet more often than
SqlDataReader.

What is your opinion? Do you think this would be a viable alternative until
the next release?

Thanks,
James Hokes