MarkN

Hi there

This was originally posted weeks ago on the ADO.NET forum. Unfortunately I did not receive replies.

Can anyone provide any feedback Thanks in advance!

I might be missing something here, but it seems that the SqlDataAdapter does something funky in terms of Sql Server RAISERRORs when used with @@ROWCOUNT.

I wont post the .NET code here unless totally necessary, but the RAISERROR from the stored proc code below does not raise a SqlException when using the SqlDataAdapter's Fill method, but it does when using the SqlCommand's ExecuteReader method;

Notes:-

* Running the proc in Mgt Studio obviously works as expected.

* If I put the RAISERROR as the first statement, it obviously works as it should and as expected.

* The messages are not raised as warnings, because even though the RAISERROR is done with a severity of 12, I checked this anyway by hooking into the InfoMessage event on the SqlConnection object.

* If I raise the error with a severity of 9, I do not get messages as warnings when using the Adapter, but I do as expected with the ExecuteReader.

* Its not the TRY-CATCHs, because this happens without using them too.

* Ive tried using WITH NOWAIT

* I am not using SET NOCOUNT ON........and @@ROWCOUNT still works with this set to on in any case.

* I've tried raising the error severity > 12.

* I've declared a local variable, set @@ROWCOUNT equal to this variable and then checked this variables value.

BEGIN TRY

SELECT

Firstname

FROM

MyTable

WHERE

ID = -1

IF @@ROWCOUNT = 0

BEGIN

RAISERROR ('Record not found.', 12, 1)

END

END TRY

BEGIN CATCH

--Raise an error with the details of the exception

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SELECT @ErrMsg = ERROR_MESSAGE(),

@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH



Re: .NET Framework Data Access and Storage SqlDataAdapter and RAISERRORs

Paul P Clement IV

I seem to remember that there are some limitations with respect to raising errors through the stored procedure to an SQLDataAdapter using RAISERROR. I'm fairly certain that no error will be returned when using the UpdateCommand or Fill command and a result set is returned.

The option of course is to use an SQLDataReader or return the error in a stored procedure parameter.






Re: .NET Framework Data Access and Storage SqlDataAdapter and RAISERRORs

MarkN

Thanks for the reply Paul.

In both cases, one using a data adapter and the other using a data reader, one is simply trying to return records from a database, and if no records are found that match certain criteria, an erorr is raised.

In terms of this, logically it makes no sense to me why variations in these two versions should exist.

From a .NET perspective, why do you believe these differences exist

How would one confirm with MS that this is in fact by design for whatever reason

(at this stage I'm inclined to believe that this is more a bug than a limitation).





Re: .NET Framework Data Access and Storage SqlDataAdapter and RAISERRORs

Paul P Clement IV

They don't appear to document it as a bug but the following should help:


INF: Handling SQL Server Error Messages When a Stored Procedure is Used in the SqlDataAdapter UpdateCommand Method






Re: .NET Framework Data Access and Storage SqlDataAdapter and RAISERRORs

MarkN

Thanks Paul.

The Fill method obviously falls into this category too. I still think this is a bug though. Smile

Is there any way I can ask the relevant team from MS about this





Re: .NET Framework Data Access and Storage SqlDataAdapter and RAISERRORs

MarkN

I looked at a 3rd version of this exercise, and with this new one the SqlDataReader also does not raise the exception. Originally, in terms of the SqlDataReader, I was using the DataTable's Load method and doing a command.ExecuteReader inside that. This raises the exception.

But, if you use a reader in the more 'traditional' sense, it doesnt raise an error until you call reader.NextResult().

So in summary, from what I have seen:-

1) If you use adapter.Fill you will not see the exception at all but will have an empty result set.

2) If you use dataTable.Load(command.ExecuteReader()), you will see the exception and not have a result set at all. (ie. nothing, not even an empty one because the exception is thrown at this point).

3) If you have a SqlDataReader and use it by doing a cmd.ExecuteReader(), followed by reader.Read(), you will not see the exception until you call reader.NextResult()....and so if you dont call reader.NextResult() you will have an empty result set.

The aim of all 3 of these mechanisms is effectively to accomplish the same thing, returning data using a SQL query.

This is highly ambiguous, and surely bordering on ridiculous ! !

===========================

The following SQL code was used in all 3 examples:-

SELECT TOP 1 Column1

FROM MyTable

WHERE ID = -1 -- this will never return records

IF @@ROWCOUNT = 0

BEGIN

RAISERROR('testing', 16, 11)

END





Re: .NET Framework Data Access and Storage SqlDataAdapter and RAISERRORs

Paul P Clement IV

Unless one of the Microsoft folks chimes in on this thread I will see what I can find out.






Re: .NET Framework Data Access and Storage SqlDataAdapter and RAISERRORs

MarkN

Thanks a lot Paul, I appreciate your efforts.