SQL Server Raiserror and .NET SqlDataAdapter

[Guest]

Running SQL Server 2000 SP4
and .NET Framework 1.1.43322 SP1

I have a trigger that does some work and then, when it decides there is a
problem does:
if @ErrMsg is not null
BEGIN
RAISERROR (50001,16,1, 'AssetConfig', 'AssetKey/Site', @ErrMsg)
ROLLBACK transaction

In my Windows Forms application I have
try
{
SqlTransaction sqlTrans = ….BeginTransaction();
Update several tables, including the one with the trigger
sqlTrans.Commit();
}
catch (SqlException e)
{
sqlTrans.Rollback();
}


When the trigger raises the error, I do not get the @ErrMsg because the
SqlDataAdapter does not trap the error. Instead, I get
System.InvalidOperationException: This SqlTransaction has completed; it is
no longer usable.
at System.Data.SqlClient.SqlTransaction.Rollback()
at the Rollback statement.

This is because the Commit threw this SqlException
Message # 3902 "The COMMIT TRANSACTION request has no corresponding
BEGIN TRANSACTION."

What I WANT is to have the SqlDataAdapter.Update throw the error that I have
specified, just as it would do if, for example, a primary or foreign key had
been violated. Then the catch block would do the rollback and handle the
error. I tried taking the Rollback out of the trigger, but then I got no
message, no error, and the database was updated with bad data. What is the
secret to making SQL Server Raiserror work with the Framework to return a
meaningful error?

 

[Kevin Yu [MSFT]]

Hi Aurin,

I think you're getting this error message, because you're commiting and
rolling back transactions both in the trigger and in your code. I suggest
you try to remove the ROLLBACK transaction and also the commit code in the
trigger and you might get the error message from SQL.

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

 

[Guest]

I KNOW that's why I'm not getting the error message I want. As I stated "I
tried taking the Rollback out of the trigger, but then I got no message, no
error, and the database was updated with bad data." There is no commit in
the trigger.

Let me try to phase the question again: How do I raise an error in a
trigger that will be trapped by the SqlDataAdapter.Update statement just like
an ordinary SQL "Constraint violated" error?

 

[Kevin Yu [MSFT]]

Hi Aurin,

Sorry that I didn't see the last lines in your first post.

Generally, when an error is raised using RAISERROR in SQL server, the .NET
framework will ALWAYS catch it as a SqlException. In the SqlException
object, there is an Errors property which is a collection that contains all
the errors raised from SQL Server. You can try to check in this collection
and get the exact error message. When the message is get, we can throw
another exception according to the type of error. You can get more
information about SqlException.Errors from the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfSystemDataSqlClientSqlExceptionClassErrorsTopic.asp

I tried to create a trigger which raises an error, when updating, it works
fine on my machine. Here is an example:

CREATE TRIGGER reminder
ON Table1
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)

this.sqlConnection1.Open();
SqlTransaction sqlTrans = this.sqlConnection1.BeginTransaction();
try
{
this.sqlDataAdapter1.InsertCommand.Transaction = sqlTrans;
this.sqlDataAdapter1.UpdateCommand.Transaction = sqlTrans;
this.sqlDataAdapter1.Update(this.dataSet1.Tables[0]);

sqlTrans.Commit();
}
catch (SqlException ex)
{
sqlTrans.Rollback();
}
finally
{
this.sqlConnection1.Close();
}

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

 

[Guest]

Your response forced me to build a simple app with a simple trigger and of
course it worked. But neither my app or the trigger is simple, so I spent an
entire day of hit-and-miss until I found the problem. It was in the trigger.
But keep in mind that the trigger worked just fine when run using an insert
statement in Query Analyzer (assuming that you follow the raiserror with a
rollback in the trigger).

In truth, the trigger performs several tests looking for error conditions.
A few of them looked like this:

select AssetConfigId
from inserted
where AssetConfigId = ParentId
and InstalledRemInsId is NOT null

if @@ROWCOUNT > 0
BEGIN
raiserror (50004,16,1)
rollback
END

As soon as the trigger did one such command, neither it, not any subsequent
error, would be caught by sqldataadapter.update(). Once I changed the select
to
select @count = count(*)
and test
if @count > 0
it appeared to work.

 

[Kevin Yu [MSFT]]

Hi Aurin,

It as nice to hear that you have had the problem resolved. Thanks for
sharing your experience with all the people here. If you have any
questions, please feel free to post them in the community.

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