CLR stored procedure fails to rollback the transaction?

[Andy B.]

I created the following stored procedure. It inserts a row into the
Headlines table. If it fails, it returns -1 with the error that sql server
returned. So far, everything seems to be working right except the rollback
part. Is there any reason why the rollback fails?

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Transactions;

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]

public static void InsertHeadline(string Title, string Description, string
Content, DateTime StartDate, DateTime EndDate) {

string SqlQuery = "INSERT INTO Headlines (HeadlineTitle,
HeadlineDescription, HeadlineContent, HeadlineStartDate, HeadlineEndDate)
values (@HeadlineTitle, @HeadlineDescription, @HeadlineContent,
@HeadlineStartDate, @HeadlineEndDate)";

try {

using(TransactionScope Scope = new TransactionScope()) {

using(SqlConnection Connection = new SqlConnection("context
connection=true")){

SqlCommand Command = new SqlCommand(SqlQuery, Connection);

#region Command parameters

Command.Parameters.Add("@HeadlineTitle", DbType.String);

Command.Parameters["@HeadlineTitle"].Value = Title;

Command.Parameters.Add("@HeadlineDescription", DbType.String);

Command.Parameters["@HeadlineDescription"].Value = Description;

Command.Parameters.Add("@HeadlineContent", DbType.String);

Command.Parameters["@HeadlineContent"].Value = Content;

Command.Parameters.Add("@HeadlineStartDate", DbType.Date);

Command.Parameters["@HeadlineStartDate"].Value = StartDate;

Command.Parameters.Add("@HeadlineEndDate", DbType.Date);

Command.Parameters["@HeadlineEndDate"].Value = EndDate;

#endregion

Connection.Open();

Command.ExecuteNonQuery();

} //end connection.

Scope.Complete();

} //end transaction.

} catch(Exception ex) {

using(SqlConnection Connection = new SqlConnection("context
connection=true")) {

Connection.Open();

SqlCommand Command = new SqlCommand("SELECT -1, @ErrorMessage", Connection);

Command.Parameters.Add("@ErrorMessage", DbType.String);

Command.Parameters["@ErrorMessage"].Value = ex.Message;

SqlContext.Pipe.ExecuteAndSend(Command);

}

}


}

};

 

[RayLopez99]

I created the following stored procedure. It inserts a row into the
Headlines table. If it fails, it returns -1 with the error that sql server
returned. So far, everything seems to be working right except the rollback
part. Is there any reason why the rollback fails?

I have no idea. But CLR is weird--you ever look at what goes on
behind the scenes? More than meets the eye. It's almost like they
have a automatic parallel thread generator--I'm always surprised at
the temporary variables that seem to get created. Maybe something is
out of sync so that when you think there should be a rollback, the CLR
engine is in fact 'behind the times' or 'ahead of time' from where you
think it is, and hence the database (which runs on its own clock, so
to speak) never gets affected.

That's the only thing I can think of without a line by line dissection
of your code. BTW I am not a db expert though I do have a pretty good
handle on it.

RL

 

[Mr. Arnold]

I created the following stored procedure. It inserts a row into the
Headlines table. If it fails, it returns -1 with the error that sql server
returned. So far, everything seems to be working right except the rollback
part. Is there any reason why the rollback fails?

Maybe you need to use nested Using(s) like this.

using(TransactionScope Scope = new TransactionScope())
using(SqlConnection Connection = new SqlConnection("context
connection=true"))

try
{
conn.open();
other statemants
scope.complete
}
catch
{

}
finally
{
if(conn.State == conn.Open)
conn.close();
}


What that stuff you have about going back to get SQL to get some exception
is what? It doesn't seem to make any sense.

 

[Andy B.]

What that stuff you have about going back to get SQL to get some exception

is what? It doesn't seem to make any sense.

1. I need to return the HeadlineID of the headline just inserted (if it was
successful) as well as a "Your headline was successfully saved." message.
This part wasn't included in the stored proc for now. The other part was in
case the insert failed and a rollback happened. It seemed to be the easiest
way of making a resultset for the -1 and the exception text. Do you have any
easier way to work it out? If there was an easy way to create a .net
resultset I would do it.

 

[Andy B.]

1. I need to return the HeadlineID of the headline just inserted (if it
was successful) as well as a "Your headline was successfully saved."
message. This part wasn't included in the stored proc for now. The other
part was in case the insert failed and a rollback happened. It seemed to
be the easiest way of making a resultset for the -1 and the exception
text. Do you have any easier way to work it out? If there was an easy way
to create a .net resultset I would do it.

Ok. I reworked the stored proc again. It now doesn't go to the database for
error handling, just sends the error results to the client. Everything still
works fine, but the rollback still fails to execute. Any ideas? Here is the
complete stored proc code.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Transactions;

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]

public static void InsertHeadline(string Title, string Description, string
Content, DateTime StartDate, DateTime EndDate) {

string SqlQuery = "INSERT INTO Headlines (HeadlineTitle,
HeadlineDescription, HeadlineContent, HeadlineStartDate, HeadlineEndDate)
values (@HeadlineTitle, @HeadlineDescription, @HeadlineContent,
@HeadlineStartDate, @HeadlineEndDate)";

using(SqlConnection Connection = new SqlConnection("context
connection=true")){

SqlCommand InsertCommand = new SqlCommand(SqlQuery, Connection);

#region InsertCommand parameters

InsertCommand.Parameters.Add("@HeadlineTitle", DbType.String);

InsertCommand.Parameters["@HeadlineTitle"].Value = Title;

InsertCommand.Parameters.Add("@HeadlineDescription", DbType.String);

InsertCommand.Parameters["@HeadlineDescription"].Value = Description;

InsertCommand.Parameters.Add("@HeadlineContent", DbType.String);

InsertCommand.Parameters["@HeadlineContent"].Value = Content;

InsertCommand.Parameters.Add("@HeadlineStartDate", DbType.Date);

InsertCommand.Parameters["@HeadlineStartDate"].Value = StartDate;

InsertCommand.Parameters.Add("@HeadlineEndDate", DbType.Date);

InsertCommand.Parameters["@HeadlineEndDate"].Value = EndDate;

#endregion

SqlTransaction InsertTransaction;

Connection.Open();

InsertTransaction=Connection.BeginTransaction("Insert");

InsertCommand.Connection=Connection;

InsertCommand.Transaction=InsertTransaction;

try {

InsertCommand.ExecuteNonQuery();

InsertTransaction.Commit();

} catch(Exception ex) {

//Handle any commit errors.

SqlDataRecord InsertError = new SqlDataRecord(new SqlMetaData[]

{ new SqlMetaData("ID", SqlDbType.Int),

new SqlMetaData("Message", SqlDbType.NVarChar, 2048) });

InsertError.SetInt32(0, -1);

InsertError.SetString(1, ex.Message);

try {

InsertTransaction.Rollback();

SqlContext.Pipe.Send(InsertError);

} catch(Exception ex2) {

//Deal with rollback errors.

InsertError.SetString(1, ex2.Message);

SqlContext.Pipe.Send(InsertError);

}

}

}

}


};

 

[Mr. Arnold]

Ok. I reworked the stored proc again. It now doesn't go to the database for
error handling, just sends the error results to the client. Everything still
works fine, but the rollback still fails to execute. Any ideas? Here is the
complete stored proc code.

Is not the statement below suppose to be the first line in the Catch of the
first Try/Catch?

InsertTransaction.Rollback();

What does the second Try/Catch have anything to do with the first Try/Catch
and what happened there and rollback of transaction?

You ran the stop sign it seems and started executing other SQL statements
that have nothing to do with what happened in the first Try/Catch from a
logical standpoint.

And you have the rollback in the wrong Try/Catch, which means it blew it off
and committed the changes it sems well before it got to the rollback
statement.

 

[Andy B.]

Is not the statement below suppose to be the first line in the Catch of
the
first Try/Catch?

InsertTransaction.Rollback();

Probably, but if you have to do error handeling (i.e. log an exception, or
send it to the client) you need to trap the exception before you do a
rollback. If you don't, you lose everything since rollback resets everything
back to default state: The number of commits is set to 0, the number of
commitable statements is set to 0, all references to error codes are set to
null and all exceptions get thrown out the window and are set to null. This
is the same way in transact-sql statements. Consider the following code:

create procedure TestTransaction
as

-- create a transaction
begin try
begin transaction
insert into ATableOfSomeKind (ID, Message) values(0, 'This might work...');
commit transaction
end try
begin catch
-- will send a message to the client if the insert above fails for some
reason.
print error_message()

-- rollback now that there is an uncommitable transaction somewhere.
rollback
-- Just for kicks, try to resend the error again. It should fail.
print error_message()
end catch
--end transact-sql

The above transact-sql is a basic example of what my clr proc was trying to
model after, but it isn't working for some strange reason. You would think
CLR would follow the same rules as transact-sql does...

What does the second Try/Catch have anything to do with the first
Try/Catch
and what happened there and rollback of transaction?

Unless Microsoft is totally clueless and
1. Was born yesterday, and
2. Crawled out from a rock in the desert

They must not know how to write code for their own server. At this link:
http://msdn.microsoft.com/en-us/library/2k2hy99x(VS.71).aspx
It explains how to do the same thing I want to do but with error reporting
to the client in a controlled way. The answer to the next questions are in
that sample.

You ran the stop sign it seems and started executing other SQL statements
that have nothing to do with what happened in the first Try/Catch from a
logical standpoint.

And you have the rollback in the wrong Try/Catch, which means it blew it
off
and committed the changes it sems well before it got to the rollback
statement.

I can try working it out again, but the try...catch inside the first catch
block was Microsoft's idea (see above). I guess it's supposed to deal with
errors in case rollback fails.

 

[Mr. Arnold]

I can try working it out again, but the try...catch inside the first catch
block was Microsoft's idea (see above). I guess it's supposed to deal with
errors in case rollback fails.

The example link you supplied.

What I see here is a Try/Catch with the rollback in the first Try as the
first thing done on the caught exception, like I said. What are you seeing?


Try
myCommand.CommandText = "Insert into Region (RegionID,
RegionDescription) VALUES (100, 'Description')"
myCommand.ExecuteNonQuery()
myCommand.CommandText = "Insert into Region (RegionID,
RegionDescription) VALUES (101, 'Description')"
myCommand.ExecuteNonQuery()
myTrans.Commit()
Console.WriteLine("Both records are written to database.")
Catch e As Exception
Try
myTrans.Rollback()
Catch ex As SqlException
If Not myTrans.Connection Is Nothing Then
Console.WriteLine("An exception of type

Where is this CLR example? Are you trying to roll your own with two
examples combining the two on a pillage and plunder?

 

[Mr. Arnold]

Andy B. wrote:

<snipped>

What I suggest you do is rework the code for now doing a divideByZero in
code before the commit. In the catch, there should only be one statement
in the catch the rollback -- no try in the catch no nothing.

You can do it with the Using Transcope too and let it rollback.

I suggest that you see it in its simplistic example to know that it will
work the -- rollback.

Then I suggest that you sit back and ponder this. What are you doing in
the code that leads to the rollback not working and the trans is
committed, even after it blew-up?

 

[maps]

I know this post is very old but perhaps it could help someone who finds it,

I think that there is a problem with scope. By implementing the using(connection)... everything is bound inside that using. So if you want to do error handling, do it inside your using otherwise if you handle errors outside then the scope of all those variables is lost once you get out of there...