SSDT Power Tools November 2012 for Visual Studio 2012 – Installation Steps

When VS 2012 informed me there was an update for the SSDT Power tools, I was expecting to click the update button and that would be it. Instead, a message popped up informing that the SQL Server Data Tools reference was missing. Knowing that I already had SQL Server Data Tools installed, I decided to go straight to the SSDT Power Tools site and install it from there. It did install, but when I opened VS 2012 and went to the SQL Server Object Explorer, VS crashed… every time. Certainly not the desired effect, so I immediately uninstalled the vsix by going to Tools > Extensions and Updates > Installed > Tools. Now that VS 2012 was not crashing, it was now time to figure out why it would not work. Here are the steps for installing it correctly.

  1. The first step is to update the version of the SQL Server Data Tools at http://msdn.microsoft.com/en-us/jj650015
    1. Download the update in step 2 from the page above, but do not install yet
    2. Install the Microsoft® SQL Server 2012 Data-Tier Application Framework (November 2012) at http://www.microsoft.com/en-us/download/details.aspx?id=35756
      1. Everything on this page needs to be installed. If you are on an x64 machine, the x86 versions also need to be installed.  This is the order I took.
        1. ENU\x64\SqlDom.msi and ENU\x86\SqlDom.msi
        2. ENU\x64\SQLSysClrTypes.msi and ENU\x86\SQLSysClrTypes.msi
        3. ENU\x64\DACFramework.msiand ENU\x86\DACFramework.msi
    3. Now install the SQL Server Data Tools downloaded in bullet 1 above
  2. Finally, install the SSDT Power Tools found at http://visualstudiogallery.msdn.microsoft.com/96a2f8cc-0c8b-47dd-93cd-1e8e9f34a917

SSDT and the power tools should now work as expected.  Even though this is for the November 2012 release, these steps may be needed for future updates.

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.