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.

Team Foundation Server Integration Tools – Watch Your Shared Step

One of my clients had recently created a Team Project in TFS 2010, but after a few weeks, decided they wanted to change the name of the Team Project.  Since this is not supported in TFS 2010, we decided to create a new Team Project with the new name and use the Integration Tools to move the work items and source code over.  Being a TFS 2010 to TFS 2010 migration, I expected everything to go smoothly and for the most part it did.  After confirming that everything got migrated correctly, I removed the users permissions from the old Team Project and directed them to use the new one.

After a couple of days, I started to receive reports from a few of the testers that they could not open their Test Cases.  They were getting the following error “TF26198: The work item does not exist or you do not have permissions to access it”.  I was able to open the Test Cases.  They also were unable to open some of the Requirement work items with the same error, but other Requirements they could open.

The problem turned out to be the Shared Steps work items.  Any Test Case that had Shared Steps or any requirement that linked to a Test Case that had Shared Steps could not be opened by the testers.  It turns out that the Integration Tools migrate the Test Cases and the Shared Steps correctly, but the mapping of the Test Cases to the Shared Steps is not.  The Test Cases were still pointing to the Shared Steps work items from the old Team Project.  For example, when opening the Shared Steps from a Test Case in the new Team Project, the work item id of the Shared Steps work item was the one from the old Team Project.  The reason why they could not open the Test Cases or Requirements was because I took their permissions away from the old Team Project.  Once I added their permissions back, they were able to open them.

The solution for me was to map the work item ids from the old Team Project’s Shared Steps to the new Team Project’s Shared Steps work item ids.  I then went through each Test Case and deleted the Shared Steps work items from the Manual Steps and re-added them with the equivalent Shared Steps work item in the new team project.  The other option would have been to go directly into the “WorkItemLongTexts” table of the TFS collection database and change the Shared Step reference in the XML data structure.  This probably would have taken just as long if not longer and would be very risky.  It is never a good idea to change data directly in the TFS databases.  Luckily, they only had around 50 Test Cases, so it took a couple of hours to fix.

I have not tried it yet, but there is the Test Case Migrator plus tool that is supposed to migrate all Test Case related work items over correctly.  It can be found at http://tcmimport.codeplex.com/.