Transaction Error Calling SQL Server 2000 from Data Layer

During the implementation phase of an engagement where I re-architected their system using a layered approach, one of the developers was getting the following error when calling a SQL Server 2000 database.

“The transaction has already been implicitly or explicitly committed or aborted”

The data layer is using the Enterprise Library 5.0 Data Access Application Block.  All calls are wrapped in a TransactionScope using a connection string from an app.config file.  There is a generic delegate called InstanceReader that accepts an IDataReader as a parameter.  The snippet of code below shows how it is written.

This works for SQL Server 2005 and SQL Server 2008, so it was puzzling why SQL Server 2000 was complaining about it.  The advice in the community was to enable MSDTC.  That did not resolve the problem.  Not knowing where to go next, I decided to look at the connection string options to see if there were something special that needed to be passed in for SQL Server 2000.  I did not find anything that was SQL Server 2000 specific, but I did find something that looked promising when looking up the ConnectionString property on MSDN – http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx.

There is a keyword called “Enlist” which is set to true by default.   The keyword is described as “true indicates that the SQL Server connection pooler automatically enlists the connection in the creation thread’s current transaction context”.  Apparently, in SQL Server 2000, it tries to enlist a second transaction even when one has already been established using TransactionScope.  The following connection string does not work with SQL Server 2000.

<add name=SQL2000ConnectionString connectionString=Database=MyDatabase;Server=.;Integrated Security=SSPI providerName=System.Data.SqlClient />

But, adding the Enlist keyword and setting it to false resolves the problem as seen below.

<add name=SQL2000ConnectionString connectionString=Database=MyDatabase;Server=.;Integrated Security=SSPI;Enlist=false providerName=System.Data.SqlClient />

I tried to get them to disable MSDTC to see if that really needed to be enabled, but they were struggling with it for a couple of days and were not in the mood to experiment.  Maybe next time.