Don’t DROP Temp Tables in SQL Stored Procs

I’ve seen BizTalk log this obscure error message after calling a stored procedure via the WCF-SQL adapter:

System.Data.SqlClient.SqlException: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

I traced the error back to the use of DROP TABLE #tempTableName statements, particularly inside CATCH blocks.  Removing the DROP TABLE statements from the CATCH blocks surfaced the real, underlying SQL error messages.

It’s bad practice, and simply unnecessary, to explicitly drop temp tables in stored procs.  SQL Server caches and reuses temporary objects such as temp tables, so a DROP TABLE statement doesn’t always drop the object anyway.  It’s better to let SQL Server manage them by itself.

Here are some references on the topic:

“A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished.” (Reference)


“Dropping a temporary table in a procedure does not count as DDL, and neither does TRUNCATE TABLE, nor UPDATE STATISTICS.  None of these things prevent temporary table caching (so it does not matter whether you explicitly drop a temporary table at the end of a procedure or not).” (Reference)


Choosing a TFS 2010 Process Template for Scrum

I recently had to select a TFS 2010 process template to support a new development project using Scrum. Besides the obvious need for the template to work well for a Scrum project, I also wanted to keep things simple and flexible for my client. It was critical to choose a fully supported template with a future upgrade path for TFS v.Next.

Some of the considerations included:

  • How well does the template support Scrum?
  • How well rounded are the various TFS artifacts (work items, reports and SharePoint)?
  • Is there any extra tooling, documentation or other benefits?
  • How well supported is the template today and (best guess) into the future?

One of the great debates around TFS best practices is whether to create a separate team project for every development project, or whether to use a single team project to contain multiple dev projects. There are valid arguments for each option, which have largely been discussed elsewhere. To reduce the administrative overhead of managing many team projects and to provide consistency of the process template configuration, I decided that multiple dev projects in one team project was the right choice. If there are ever any custom modifications to the process template, it will only need to be done in one place.

That led to a few more questions:

  • Does any process template tooling assume that the team project contains only one dev project?
  • Do the reports included with the process template support multiple dev projects, or can they be easily modified? This applies to both SSRS and Excel reports.
  • Can the work item queries be easily modified to support multiple dev projects?

All of these requirements narrowed the field to three:

  1. MSF for Agile Software Development V5.0
  2. Visual Studio Scrum 1.0
  3. Scrum for Team System V3.0

I quickly ruled out Visual Studio Scrum due to concerns about its lack of maturity and support. My guess is that this template will morph into an out-of-the-box template as part of TFS v.Next. Today, however, it’s very basic. It doesn’t have any Excel reports and very few SSRS reports, no document templates and only very basic SharePoint support. It has a handful of seven basic work item types. On the project forum, I found a lot of questions and issues raised with no response from Microsoft, and there haven’t been any posts from Microsoft for months. I got the feeling that this template wasn’t going to receive any more attention until it becomes part of the TFS product (if it ever does).

That narrowed the field to two: MSF vs SfTS. SfTS is in its third release, now under the EMC brand, so it has been refined through real-world experience over many years. Support was a concern right away, because the SfTS forums are pretty dead and there is no official support program. The extra tooling is nice (TFS Workbench), the work item templates make sense and there’s a good selection of SSRS reports. There’s even a Windows service to do time rollups and so on. There are good feature lists and comparisons elsewhere, so I won’t spend time on that here.

In the past, SfTS hasn’t always had a clear upgrade path. There was little documentation about upgrading an SfTS project from TFS 2005 to 2008, for example. Today there’s a migration tool from V2 to V3, which is great. However, it’s anyone’s guess if and when the EMC employees who maintain the template will carry it on to TFS v.Next. That’s a definite concern.

One big sticking point with SfTS is that it has a built-in assumption that one team project holds a single dev project. If you use the project setup wizard in TFS Workbench, it will wipe out and replace the areas and iterations, etc. defined in the project. It also relies on a very specific iteration hierarchy which builds off of a release, not a product. There didn’t appear to be any good way to use this template with multiple dev projects in a single team project, and that’s what finally got it crossed it off the list.

The choice was MSF for Agile Software Development V5.0, thanks to its clear support from Microsoft, robust process guidance documentation and document templates, extensive reporting, good SharePoint support, reasonable selection of work item types and ability to be tweaked to support multiple dev projects. Is it the best Scrum template? Maybe not, but I think it will work fine for our needs. We’re not purists about Scrum or any agile process, so we’ll take the good parts and tune it to work best with the client’s culture and the particular skills on the team.

Workaround for infinite “Please wait while the installer finishes determining your disk space requirements” dialog during MSI install

Tonight I was attempting to test the MSI installer for the Deployment Framework for BizTalk when I encountered an infinite dialog box stating "Please wait while the installer finishes determining your disk space requirements.”  This is by no means the first time that I’ve seen this occur, but usually it goes away after restarting the install once or twice.  Unfortunately, this time every single attempt at a GUI install was blocked by this issue.  (As documented elsewhere, using msiexec.exe to start an unattended install worked, but I needed to test the GUI.)


The environment was a virtual machine running Windows Server 2008 on Windows Virtual PC (Windows 7).  As others have reported, this problem appears to happen much more often, if not exclusively, on virtual machines.

I figured that since disk space calculation was behind the issue, the best approach was to eliminate as many disks as possible from my virtual machine until the problem (hopefully) disappeared.  I had one (virtual) floppy drive, one hard drive and one DVD drive.

Using Device Manager, I disabled the floppy controller.  The floppy drive disappeared, but the problem didn’t.  Next, I disabled ATA Channel 1 of the IDE ATA/ATAPI controllers, which controls the DVD drive.  The DVD drive disappeared – and so did the dialog box!  I did some experimentation with a few combinations of drive configurations, and the problem definitely followed the DVD drive.  It seemed that leaving the IDE controller enabled in the virtual machine, but setting Virtual PC to None for the DVD drive option also worked fine.  The common default setting that maps the virtual DVD drive to a physical DVD drive or to an ISO file caused the problem to appear.

So, bottom line, if you’re seeing this happen in either a VMWare or Virtual PC VM, try disabling the IDE controller attached to the virtual DVD drive.

Fix for BizTalk ESB Toolkit 2.0 Error 115004 in ALL.Exceptions Send Port

On occasion we have had messages suspend on the ALL.Exceptions send port with the error:

Error 115004: An unexpected error occurred while attempting to retrieve the System.Exception object from the ESB Fault Message.

The source of the error is the pipeline component Microsoft.Practices.ESB.ExceptionHandling.Pipelines.ESBFaultProcessor, part of the ESB Toolkit’s custom pipeline on the ALL.Exceptions port.

Some suspicions and hunting through code using .NET Reflector led to an explanation.

The ExceptionMgmt.CreateFaultMessage() method, which is used to create a fault message in an orchestration exception handler, automatically locates and stores the exception object that was previously thrown.  It stores the exception by binary-serializing it, Base64 encoding it and storing it in a property on the first message part of the fault message.  Later on, the ESBFaultProcessor pipeline component attempts to de-serialize the exception.

The trouble arises when the thrown exception contains a non-serializable inner exception more than one level deep.  The method ExceptionMgmt.IsExceptionSerializable() only checks the root exception and the first InnerException.  If a non-serializable exception happens to be nested further, the code does not detect it.  As a result, the ESBFaultProcessor fails while attempting to de-serialize it.

In our case, we are pulling a flat file down from a web service and disassembling it inside of an orchestration using the XLANGPipelineManager class.  If there is a problem with the file format, an XLANGPipelineManagerException is thrown.  It contains an InnerException of XmlException, which in turn contains an InnerException of Microsoft.BizTalk.ParsingEngine.AbortException – which has no serialization constructor.

To solve this issue, I wrote a short helper method in C#.  I call it immediately after ExceptionMgmt.CreateFaultMessage() and pass it the newly created fault message and the caught exception’s Message property value.  It checks whether the stored exception can be de-serialized, and if not, replaces the stored exception with a special exception class.  This is the same thing that would have happened had the IsExceptionSerializable() method correctly detected the situation.

I submitted this bug to Microsoft Connect.

To use this code, you’ll need a C# class library with references to:

  • Microsoft.Practices.ESB.ExceptionHandling
  • Microsoft.Practices.ESB.ExceptionHandling.Schemas.Faults
  • Microsoft.XLANGS.BaseTypes

For convenience, I added a couple of using statements at the top.

using System;
using Microsoft.XLANGs.BaseTypes;
using ExceptionHandling = Microsoft.Practices.ESB.ExceptionHandling;
using ExceptionHandlingSchemas = Microsoft.Practices.ESB.ExceptionHandling.Schemas.Property;

namespace BizTalkHelpers
    public static class OrchestrationHelper
        /// <summary>
        /// Work around a bug in the BizTalk ESB Toolkit 2.0 related to
        /// non-serializable exceptions. When
        /// ExceptionMgmt.CreateFaultMessage() creates a message, it
        /// automatically locates and stores the caught exception. If the
        /// exception contains an InnerException more than one level deep
        /// that is not serializable, the ESBFaultProcessor pipeline
        /// component will later fail when it attempts to deserialize the
        /// exception, resulting in the error:
        /// Error 115004: An unexpected error occurred while attempting to
        /// retrieve the System.Exception object from the ESB Fault Message.
        /// </summary>
        /// <param name="msg">
        /// Message created by ExceptionMgmt.CreateFaultMessage()</param>
        /// <param name="exceptionMsg">
        /// Message property value of the caught exception</param>
        public static void FixNonSerializableExceptionInFaultMsg(
            XLANGMessage msg, string exceptionMsg)
            // Incoming msg must have been created by
            // ExceptionMgmt.CreateFaultMessage()
            XLANGPart p = msg[0];

            if (p == null)

            // Extract the Base64-encoded string representation of the
            // exception serialized by CreateFaultMessage().
            string str =
                typeof(ExceptionHandlingSchemas.SystemException)) as string;

            if (str == null)

            catch (Exception)
              // If an exception is not serializable, the correct behavior
              // is to store a serialized instance of
              // SetExceptionNonSerializableException.
              ExceptionHandling.SetExceptionNonSerializableException ex =
                new ExceptionHandling.SetExceptionNonSerializableException(
                  0x1c13e, new object[] { exceptionMsg });


BizTalk Adapter for DB2 Error SQLSTATE: HY000, SQLCODE: -270

My client had been using the BizTalk Adapter for DB2 from the BizTalk Adapters for Host Systems for quite some time with no significant issues.  The target system was DB2 on an AS/400 (iSeries/System i).  Late last week, the AS/400’s i5/OS was upgraded from V5R4 to V6R1 and the DB2 team did a database restore.

Somewhere around that time, BizTalk started logging the following errors:

An internal network library error has occurred. The requested command encountered an implementation-specific error condition on the target system. SQLSTATE: HY000, SQLCODE: –270

Searches for this error turned up only one post on a Host Integration Server newsgroup, which didn’t give us any answers.

I started out by looking up the SQLCODE –270 in the IBM SQL Messages and Codes book.  That description had to do with unique indexes or constraints on distributed tables, which didn’t seem relevant to our situation.

I found the actual meaning of –270 in DB2OLEDB.H on the Host Integration Server 2006 CD (MSI\x86\PFiles\SDK\Include).  It’s defined there as DB2OLEDB_DDM_CMDCHKRM, and CMDCHKRM means “command check reply message.”  The problem is that the error code(s) contained in the reply message are not surfaced, so this was still a dead end.

The Microsoft OLE DB Provider for DB2 is the foundation for the DB2 Adapter, so in order to rule out BizTalk and the DB2 Adapter as possible problems, we created a five-line C# command-line app:

OleDbConnection cn =
    new OleDbConnection("Provider=DB2OLEDB;REST_OF_CONNECTION_STRING_HERE");
OleDbCommand cmd =
    new OleDbCommand("SELECT COUNT(*) FROM A-TABLE-IN-DB2", cn);
int rc = Convert.ToInt32(cmd.ExecuteScalar());
Console.WriteLine("Rows in table: " + rc.ToString());

Sure enough, the test app encountered the same error.  The problem was definitely in the OLE DB Provider for DB2.

The OLE DB Provider requires various “packages” (a DB2 concept) to exist in the DB2 system.  The packages correspond to various transaction isolation levels, so they are named READ UNCOMMITTED, REPEATABLE READ, etc.  We did not enable transactions in the DB2 connection string, nor did we configure isolation level in BizTalk, so we still don’t know which isolation level (and thus which package) is being used.  SERIALIZABLE is a good guess since it is often the BizTalk default.

When a connection is opened, if the DB2 Provider finds that the package associated with the active isolation level does not exist, it is supposed to automatically create it.  The active user account must have sufficient rights in DB2.  If that is not an option, then the Data Access Tool can be used to manually create the packages (the Packages button on the next-to-last page of the New Data Source wizard).

In our case, the user account should have had enough permissions to automatically create a package, but evidently that process failed and resulted in the obscure SQLSTATE: HY000, SQLCODE: –270 error.  As soon as I manually created the packages in the Data Access Tool, the error disappeared and everything began working normally again!

This page of the OLE DB Provider for DB2 documentation is an excellent resource for understanding the DB2 packages, the auto-create process, various error messages that may result and more.