Lightweight Transaction Manager SQL Server 2005
Hello hello,
I'm trying to use the LTM but I'm convinced it's still using MSDTC. Here's the code I'm using:-
Using myTransactionAs TransactionScope =New TransactionScope
Using connectionAs SqlConnection = Program.SapphireUser.UserSqlConnection
connection.Open()
ForEach objAs DataAccessObjectIn DataAccessObjectList
myTableAdapter.Update(mydatatable)
Next
connection.Close()
EndUsingmyTransaction.Complete()
EndUsing
I know when using .NET 2.0 and SQL Server 2005 Promotable Enlistment should kick in and automatically participate in an LTM transaction.
However two indicators suggest otherwise:
1) When I put a watch on theSystem.Transactions.Current.TransactionInforrmation.DistributedIdentifierthe guid value isNOT{000000-0000.....} which as I understand means the transaction is not managed by LTM
2)When I look at the Distributed Transaction Coordinator (via Control Panel, Admin, Component Services) the transaction statistics are incrementing every time I use the above code. This shouldn't be happening if the transaction is managed by the LTM right?
Any help would be welcomed. I'm a bit new to all this so forgive me if you spot any schoolboy errors and please point them out!
Cheers
Stuart
p.s. I'm using VS 2005, SQL Server 2005 and Windows XP Pro.
From the diagnostic information you have provided, it does look like you are getting a distributed transaction. However, I'm not quite following everything that's happening in your code. Specifically, the For Each loop is not clear to me. If you comment out that part of the code, are you still seeing a distributed transaction? At what point in the code are you breaking to put the watch in the debugger? For example, if you break prior to the Open call, the guid should still be all 0s. Then step over the Open call and see what you get after each line of code. When does the transaction get promoted?
What is not clear to me what why you are iterating over DataAccessObjectList but aren't doing anything with obj in the body of the loop. Also, how is myTableAdapter set up? Is it referencing the connection variable and you just haven't shown that part in your code? If not, it is probably opening a second connection for the Update, which could cause the transaction promotion. If this were the case, you would see the transaction guid go to non-zeros after the first Update in the loop. I don't see anywhere that connection is used in this case, so I would guess either you just didn't post that part of the code, or there's really another connection being used. If you open two connections in your TransactionScope, you will get a distributed transaction.
Thanks,
Sarah
Please Mark as Answer if this answers your question, or Unmark as Answer if it is marked and you feel it is not answered.
