SQL Server Error Trapping in ASP/ASP.NET

[Guest]

I've run into an odd bit of behavior when dealing with SQL Server 2000 and
ASP/ASP.NET that I wanted to discuss.

The issue I encounter is that in a stored procedure, once a SELECT statement
succeeds and returns a recordset, all resulting errors are ignored by
ASP/ASP.NET in certain cases (see details below). Wouldn't you expect the
ADO/ADO.NET command to fail if the procedure it calls fails?

For example, I created a simple stored procedure that is meant to fail after
successfully returning a recordset.

--------------------------------------------------------------------------
CREATE PROCEDURE spt_TestSqlErrors

@ErrMsg VARCHAR(255) = '' OUTPUT

AS

--SUCCEEDS returns a recordset
SELECT 'Test' AS FirstName, 'User' AS LastName

--SUCCEEDS but does not return a recordset
DECLARE @SelectResults VARCHAR(255)
SELECT @SelectResults = FirstName FROM tblUsers WHERE UserID = 12345

--SUCCEEDS but updates 0 records
UPDATE tblPerson SET ID=0 WHERE 1=0

--FAILS divide by zero
SELECT 12/0 AS DivisionError

--SUCCEEDS and raises an error
SET @ErrMsg = 'Error Message.'
RAISERROR(@ErrMsg, 16, 1) WITH LOG

GO
--------------------------------------------------------------------------


With ASP.NET

Command.ExecuteNonQuery - will return an error (Divide By Zero)
Command.ExecuteDataReader - will not return an error (Divide By Zero)
Adapter.Fill - will return an error (Divide By Zero)

With ASP

Command.ExecuteNonQuery - err object does not register an error
Recordset.Open - err object does not register an error

Use whatever hypothetical situation you like to explain why a stored
procedure would return multiple recordsets and would raise an error if the
second recordset didn't exist. Wouldn't you expect the DivideByZero or
Raiserror to roll up to ASP and ASP.NET in all situations and not be ignored
by the ExecuteDataReader or by ASP completely?

What do you think?
Paul

 

[Guest]

A DataReader is a forward only stream. You should still encounter the
exception, but it will not happen until you hit that part of the stream. In
the case of NonQuery, the entire batch is run before the return (or at least
what can be run); the same is true for Adapter.Fill().

If you are batching many commands, the Reader is not necessarily the best
choice.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************