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?

[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)";

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);

}

}


}

};

On Mar 12, 5:04=A0pm, "Andy B." <a_bo...@sbcglobal.net> wrote:
> 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 serve=
r
> returned. So far, everything seems to be working right except the rollbac=
k
> 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

"Andy B." wrote:

> 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.

>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." <a_borka@sbcglobal.net> wrote in message 
news:ewhoYRgwKHA.5812@TK2MSFTNGP02.phx.gbl...
> >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.
>
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);

}

}

}

}


};

> 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.

"Mr. Arnold" <No@No.com> wrote in message 
news:0A00CD47-EE9E-4F72-B834-D9003EC25C17@microsoft.com...
>
>
>> 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();

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. 

Andy B. wrote:
> 
> 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?

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?