Paul Wilson's .NET Blog

you may find this of interest as well: http://www.thedatafarm.com/blog/PermaLink.aspx?guid=22bc28a2-2497-4ab8-a991-49e4b13604a4

... only on sqlserver 2005, as the db has to promote the transaction to a DTC controlled one, it won't work on Oracle, IMHO.

I tested this with Ms Sql 2000 -- so I know that its false to say you must have Ms Sql 2005. And my understanding, but untested, is that Oracle does support the DTC -- else how would it work with Enterprise Services. What I keep hearing, and its also in Julie's link, is that you will automatically get even a single transaction in the DTC unless you are using Ms Sql 2005. Thus you want to continue using IDbTransactions unless you really need more, but then it should work seamlessly -- or at least that was true on Ms Sql 2000 in my tests.

Yes, Oracle will integrate into the DTC, through a tool that Oracle has called the "Oracle Services for MTS" assuming you are using the Oracle Client. The MS Client for Oracle has support for DTC built in, however, it does provide for full integration with Oracle. I don't know if any of this will allow for integration with the TransactionScope without the 10gR2 client. According to the things that I have read and heard, the 10gR2 client will provide additional integration with Whidbey.

I thought to get the fully promotable transaction, you had to have a specific type of connection, which Sql2k5 as the first database to support this connection.

There's a lot of things we've all read and heard, and that's the problem. Some people seem to have heard that you need Ms Sql 2005, but my tests show that is simply not true since it worked with Ms Sql 2000. Of course my assumption that things will also work with Oracle, since I don't see it as any different than Ms Sql 2000, also needs to be tested since otherwise its no better advice than the other hearsay. The one thing that seems to be consistent in all the hearsay is that only Ms Sql 2005 is smart enough to not enlist in the DTC if its not necessary -- but that's not the same thing as saying you need Ms Sql 2005.

By the way, this reminds me of something I learned from someone far better than I -- and which taught me that you must test things for yourself and not believe what you read/hear:

If you remember COM+ transactions, they required you to call either SetComplete or SetAbort. All the examples only called SetAbort if something went wrong, and in fact all the documentation flat-out said that calling SetAbort would doom the transaction. So I was quite annoyed when I looked at my colleague's code and notice that the first thing he always did was call SetAbort! My reading of the documentation and all the MS examples meant that he was automatically making all his transactions fail -- but of course he was not and that much was obvious since his code was obviously working. So I asked him how this could possibly be working, and he told me that what mattered was not whether or not you called SetAbort, but what was called last when you left that method -- so his calling SetAbort first didn't matter as long as he later called SetComplete when things worked correctly. I could see he had to be right since his code worked, but that brought up my next question, which was why do it this way though? His answer was that the default was Complete, so if something catastropic happened but SetAbort was not called then the transaction would get committed! What could possibly happen that would be catastrophic and not get my SetAbort called, since it was in my error handler? He pointed out that there are lots of things that can go wrong in the real world, like power and network surges. I kept wanting to insist on the documentation and all the code examples -- and he encouraged me to test it myself. It took the better part of a day, but I finally did manage to hit my power button at just the right time to cause just such a catastrophic event -- and I proved him to be quite right and all the documentation and examples very much flawed. So the thing I learned was that you can't assume that what you hear and/or read is correct in all the details.

Paul,

Please correct me if I am wrong on this, however, I think we are looking at two slightly different questions.
#1 is your original question with regards to the transactionscope integration. I believe that you are talking about the ability to integrate.
#2 is the ability to have promotable transactions. I believe that this is a inadvertent threadjacking.

Wally

I'm not sure I see the distinction. If you mean that only Ms Sql 2005 doesn't automatically enlist the DTC then I agree, but if you mean that Ms Sql 2000 (and probably Oracle) won't get promoted to the DTC then I think I disagree.

Ok, I misread your posting. I thought the transaction wasn't initially in an enterprise services managed component (or what's its successor called in .NET 2.0). So if it's not in a COM+ managed component, the DTC won't know about it, and it will never get promoted. Though if it's already in a COM+ managed transaction, then yes, you don't need to do anything. But that's not really that new, in .NET 1.x this is also already the case.

No you read the initial post correctly. I have intentionally not introduced anything related to Enterprise Services or COM+ -- other than the of course using the new TransactionScope. All I did was create two regular IDbTransactions against a Ms Sql 2000 database and execute two commands, the first of which I committed, but the second one failed and rolled back (due to duplicate record in my particular case). The block of code without be wrapped in a TransactionScope ended up with the first command having been committed, but the second not -- as I expected. The exact same block of code wrapped in a TransactionScope had the result that both commands were rolled back. Nowhere did I do anything else that involved any setup on my part to prepare things for the DTC by using EnterpriseServices or COM+.

Paul I saw a demo of this today. And this is my take away.

Yes you can use System.Transactions with SQL2000 there is a drawback however. Because SQL2000 and every other database except SQL2005 aren't Ligthweight transaction aware, once a SqlConnection (2000) has an ambient TransactionScope it automatically enlists with MSDTC, even if only that connection is the only connection used in the transaction.

I.e you don't get lightweight transactions. What does that mean? It means sure you can use a TransactionScope with SQL2000 but if you could have done it with a standard SqlTransaction and no DTC you have just made your code slower, because of the MSDTC overhead.

To illustrate imagine this in a transaction scope:

connection2000.open();
commandOn2000_1.execute();
commandOn2000_2.execute();

the call to connection.Open() will always spin up a MSDTC transaction, why because 2000 can't upgrade it's transaction manager on demand, and so it needs to start with the most flexible one first namely the MSDTC one.

whereas this:

connection2005.open();
commandOn2005_1.execute();
commandOn2005_2.execute();

won't get to MSDTC because 2005 is compatible with the ability to upgrade transaction managers on demand, so it can start with the lightweight one (which doesn't use MSDTC) and since it can all be done locally it never needs to upgrade.

Does that make sense?

Sure, we are in agreement that using the TransactionScope is not the way to do a single transaction (unless maybe you know you are using SQL 2005). I don't think anyone has disagreed with that, although maybe that wasn't clear so thank you for clarifying that. My point was that you can take your existing code that uses the IDbTransaction and seamlessly enlist it into a TransactionScope when you need to do so.

We maybe in agreement, the point is it is a real shame... I would have really liked to replace my SqlTransaction code, with TransactionScope calls instead, it would have made my code so much more readable.

But now I have a decision to make is readability or speed more important?