Fast transactions with System.Transactions and Microsoft SQL Server 2000

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Sami — Sun, 02 Oct 2005 15:30:32 GMT

Hello Florin,
Im trying to get the PDC dlinq samples work with TransactionScope object, the idea is to build a case study showing the whole Indigo+tx flow+dlinq thing. But it seems that the bits provided with dlink are only targeting Sql 2005 Express Edition. When I run this code with Sql 2000, MsDTC always rollback the current transaction with no message.

// AFTER
ObjectDumper.Write(from p in db.Products where p.ProductID == 4 select p);
ObjectDumper.Write(from p in db.Products where p.ProductID == 5 select p);

Display the correct row data, but the table is not consistent with this message.

Any idea?

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Sami — Sun, 02 Oct 2005 17:03:55 GMT

Ok I found the problem. PDC Dlinq samples don't work because they lack of ts.Complete() statement.

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Vikas Jindal — Tue, 11 Oct 2005 21:38:18 GMT

Really Fantastic

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Tomas Carlsson — Thu, 15 Dec 2005 11:58:49 GMT

Hello,
Thank you fore a good artickle.
I would like to make a distributed transaction over two databases (and be able to role back if anything goes wrong).

Is this possible with SQL Server 2000 and this pattern? I don't like to "mess up" my code with EnterproceServices and COM+.

I have tried to modify your code-example with two connections but I get an exeption that says "Cannot enlist in a distributed transaction", se my code below. What am I doing wrong?

Thank you for helping me ;)

Tomas

string connStrAar1Reg1 = GemFunktioner.AnslutningAarRegion(AAR_1);
connStrAar1Reg1 += "Enlist=false";
m_connAar1 = new SqlConnection(connStrAar1Reg1);
m_dbAdapterAar1 = new DatabaseTransactionAdapter(m_connAar1);
m_connAar1.Open();

string connStrAar2Reg1 = GemFunktioner.AnslutningAarRegion(AAR_2);
connStrAar2Reg1 += "Enlist=false";
m_connAar2 = new SqlConnection(connStrAar2Reg1);
m_dbAdapterAar2 = new DatabaseTransactionAdapter(m_connAar2);
m_connAar2.Open();

m_dbAdapterAar1.Begin();
m_dbAdapterAar2.Begin(); // Here I get the exeption "Cannot enlist in a distributed transaction"

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

florinlazar — Tue, 31 Jan 2006 10:57:09 GMT

To: Tomas Carlsson
Tomas, when you have 2 database connections, you don't have to use this adapter, since you can't avoid using the DTC transaction in this scenario. Simply use the normal pattern for TransactionScope and you will be all set.

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Rob Steele — Wed, 01 Feb 2006 16:04:27 GMT

Will this pattern work with Oracle 9.x or 10.x? Are you aware of any issues? I am utilizing the ODP.Net provided by Oracle.

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

florinlazar — Wed, 01 Feb 2006 21:09:53 GMT

To: Rob Steele

If Oracle supports internal transactions, you should be able to change the adapter to work with ODP.Net.

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Richard Purchas — Tue, 21 Mar 2006 03:12:37 GMT

I tried John Doty's sample TransactionAdapter with mixed results:
- where I had transactions that mixed TransactionScopeOption.Required with TransactionScopeOption.Suppress operations, the adapter doesn't support transactions operating in TransactionScopeOption.Suppress mode. I changed all of these instances to use TransactionScopeOption.Required and that seemed to keep it happy.

- next, I started getting errors when running transactions that involved the work of multiple data components, where each data component would (a) connect to the database and then (b) do some work.

In this last case, it was trying to enlist the current transaction more than once (and hence failed). Also, I could see from the SQL 2000 trace that when each data component created a connection a NEW connection was being created rather than some resource dispenser detectng that a transaction was active and re-using the current transaction.

My sole reasons for wanting to use John's example was to eliminate the need for the MSDTC on this single-database SQL 2000 application (as use of the MSDTC violates my client's corporate security policies, and it is not great for performance reasons either).

Looks like I'll need to hunt around for another solution (unless anyone else has resolved this problem already ??).

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Joe Egan — Thu, 13 Apr 2006 21:21:54 GMT

...perfect solution as I migrate to CSLA .NET 2.0 but stick with SQL Server 2000 for a while. Thanks!

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Javed — Wed, 24 May 2006 00:34:36 GMT

I am having difficulty getting the DataBaseTransactionAdapter to work. It was easy enough to use. Everything seems to go smoothly but when the routine exits, I get an exception "Transaction was aborted". I do indeed call ts.Complete right after .ExecuteNonQuery. The method executes without problem, but back in the calling routine the Try...Catch block shows up with the about exception.

Using msmq and sql

Avi_H — Mon, 03 Jul 2006 09:26:35 GMT

Could you please post a sample for it ?

Thanks

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Fyodor Sheremetyev — Tue, 01 Aug 2006 11:52:11 GMT

To: Richard Purchas Have you found any other solution?

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Steve Phillips — Tue, 05 Sep 2006 14:03:15 GMT

Florin, The reason you have to set the transaction manually is because the SqlConnection object has been explicitly excluded from it using the "EnList=false" parameter.

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Liviu Uba — Thu, 07 Sep 2006 19:07:03 GMT

Excelent. I struggle with Transactions and Dataset designer generated datasets for a while, and I have only headaches... DatabaseTransactionalAdapter is just awesome. I have written a small wrapper for the DataTableAdapters generated by Dataset Designer: Adapter with implicit conversion to T where T si DataTableAdapter. the reason: setting of the transaction: DatabaseTransactionAdapter dta = new DatabaseTransactionAdapter(conn); PersonTableAdapter p1 = new Adapter(dta.Transaction); excellent!!!!!

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Liviu Uba — Mon, 11 Sep 2006 17:37:01 GMT

Thanx. My previous post did not show up, but anyway I want to thank you, because based on your adapter and custom code generation that extends the dataset designer code I can write now following and it rocks!! : Data data = new Data(); using (Db.ReadCommitted) { data.Document.GetDataById(5456454, 1); data.Document.DataRows(0).FreeTxt = Guid.NewGuid(); data.Document.Save(); Db.Commit(); }

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

Manoj — Tue, 03 Oct 2006 06:46:07 GMT

I tried to use the TransactionAdapter with the following test code. The code fails at the adapter.Begin() inside the TxnInner method below. However, if I use TransactionScopeOption RequiresNew for inner transaction the code works winhout problem. using System; using System.Data; using System.Transactions; using System.Data.SqlClient; namespace Test { public class TxnTest { const string connectionString = ".......;enlist=false"; const string sql = "select * from authors"; private static void TxnInner() { using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required)) { SqlConnection connection = new SqlConnection(connectionString); connection.Open(); SqlCommand command = new SqlCommand(sql, connection); DatabaseTransactionAdapter adapter = new DatabaseTransactionAdapter(connection); adapter.Begin();// Fails here if TransactionScopeOption is Required command.Transaction = (SqlTransaction)adapter.Transaction; command.ExecuteNonQuery(); scope.Complete(); } } public static void TxnOuter() { using (TransactionScope scope = new TransactionScope()) { SqlConnection connection = new SqlConnection(connectionString); connection.Open(); DatabaseTransactionAdapter adapter = new DatabaseTransactionAdapter(connection); SqlCommand command = new SqlCommand(sql, connection); adapter.Begin(); command.Transaction = (SqlTransaction)adapter.Transaction; command.ExecuteNonQuery(); TxnInner(); scope.Complete(); } } public static void DoTest() { TxnOuter(); } } }

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

florinlazar — Wed, 04 Oct 2006 02:18:32 GMT

To: Richard Purchas

For reusing the connection, you might also take a look at the ConnectionScope class posted at http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx

An error occurred while enlisting in a distributed transaction

Muralidhar — Wed, 23 Jan 2008 18:03:56 GMT

I hav a diff problem. Am using 1.1 with sql server 2000 and Auto enlisting. Transactions are implemented thru Enterprise components.

At regular intervals I am getting "An error occurred while enlisting in a distributed transaction" error. And when I run the same activity agains it succeds without any error..

Can u show some thoght into this.. Thanks

re: Fast transactions with System.Transactions and Microsoft SQL Server 2000

florinlazar — Wed, 23 Jan 2008 21:58:05 GMT

To: Muralidhar

For this sort of issues, I recommend posting at the Transactions Forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=388&SiteID=1

Or if it is critical for your business, you should contact Microsoft Support.