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.

Faster Downloads from MSDN Subscriber Downloads using Akamai

A while back Microsoft copied some of the biggest and most in-demand software downloads for MSDN Subscribers to the Akamai content distribution network.  If you haven’t heard of Akamai, it operates a worldwide network of content distribution servers with the goal of offloading download activity from web servers and moving the content as close to your physical location as possible.  It’s all about getting data to you faster.

There are now two ways to get some popular software from MSDN Subscriber Downloads.  The first, and familiar, way is to log into the Subscriber Downloads website as usual, and use the Microsoft Download Manager ActiveX control to download the software.  The newer, Akamai way is (I believe) only available from one web page here.  You will still need to log in as usual.

In order to use the Akamai downloads:

  1. Allow pop-ups from
  2. Visit the downloads link, log in, and when the page comes up, look down to the “Top Subscriber Downloads” area.  These are the Akamai download links.
  3. Determine if the software you want is in the list.  If not, you’ll have to go through the normal Subscriber Downloads site.
  4. If the software you want, like VS 2008, is in the list, click the link to begin the download.  A pop-up will appear, and you will have to allow the Akamai Download Manager ActiveX control to install.  You may need to re-click the link after the control installs.
  5. The Download Manager will ask for a folder to store the file in.  You’ll have to keep its window open while the download takes place.
  6. If you are on Vista with default configuration, there is one little catch: the Download Manager will not be allowed to download to most directories on your hard disk, and it will be forced into a Vista virtualized folder system.  That’s where Vista points an application to a folder in a temporary location (not the folder the application thinks it is pointing to) for security with Internet applications.  Once you have downloaded a multi-GB file and it is not in the folder that you selected at the start of the download, you are probably not going to be a happy camper.  It’s there, just not where you expect.  Look for a folder like this: C:UserstabrahamAppDataLocalMicrosoftWindowsTemporary Internet FilesVirtualizedCUserstabrahamDocuments.  In this example, I pointed the Download Manager to C:UserstabrahamDocuments and that is where it ended up.

I hope that helps.  Some of the downloads are a lot faster from Akamai — if they happen to be popular enough to be on the Top Subscriber Downloads list.  Visual Studio 2008 RTM is up there as I write this, and my download just finished.  Time to install!

Visual Studio 2008, .NET Framework 3.5, .NET Framework 3.0 SP1, .NET Framework 2.0 SP1 RTM

The next major release of Visual Studio 2008 and the .NET Framework 3.5 have quietly been released to manufacturing over the weekend, along with .NET Framework 2.0 SP1 and .NET 3.0 SP1.  This is an important release of Visual Studio because it fully integrates the tooling for the technologies released in .NET 3.0 (Windows Communication Foundation, Windows Presentation Foundation, Windows Workflow Foundation and CardSpace) and, for the first time, can target multiple versions of the .NET Framework.

Our team has been building experience in the .NET 3.0 technologies for most of this year and playing with the betas, and we’re looking forward to working with the improved tooling in the RTM release.

Download .NET Framework 3.5 here.  The .NET 3.5 installer will install the 2.0/3.0 Framework and 2.0 SP1 and 3.0 SP1 automatically.  The service packs are also available separately for 2.0 and 3.0.  As for Visual Studio 2008, I think the MSDN subscriber downloads site is having issues already, probably due to high demand.

I just finished installing .NET Framework 3.5 RTM on my laptop running Vista Enterprise.  The install took a long time, about 30 minutes, but succeeded.  Along with 2.0 SP1 and 3.0 SP1, it also installed two Vista hotfixes, KB929300 and KB110806.  This release, like 3.0, is more of an additive release that requires and builds upon 2.0 and 3.0, unlike the 1.1 to 2.0 transition.

There is also a big new training kit (120 MB) that includes presentations, demos and labs.  You can get that here.  There are also 3.5 whitepapers by David Chappell and a 3.0 common namespaces and types poster.

I’ve been working with 3.0 and the 2008 betas for about 10 months now, so I’ve been a bit negligent with my blogging!  It has just ended up to be another really busy year.  I hope to get some new posts out about 3.0/3.5 soon.

From Microsoft:

.NET Framework 3.5 builds incrementally on the new features added in .NET Framework 3.0. For example, feature sets in Windows Workflow Foundation (WF), Windows Communication Foundation (WCF), Windows Presentation Foundation (WPF) and Windows CardSpace. In addition, .NET Framework 3.5 contains a number of new features in several technology areas which have been added as new assemblies to avoid breaking changes. They include the following:

  • Deep integration of Language Integrated Query (LINQ) and data awareness. This new feature will let you write code written in LINQ-enabled languages to filter, enumerate, and create projections of several types of SQL data, collections, XML, and DataSets by using the same syntax.
  • ASP.NET AJAX lets you create more efficient, more interactive, and highly-personalized Web experiences that work across all the most popular browsers.
  • New Web protocol support for building WCF services including AJAX, JSON, REST, POX, RSS, ATOM, and several new WS-* standards.
  • Full tooling support in Visual Studio 2008 for WF, WCF, and WPF, including the new workflow-enabled services technology.
  • New classes in .NET Framework 3.5 base class library (BCL) that address many common customer requests.

.NET Framework 1.1 and 2.0 and 3.0 Side-by-Side (SxS)

I’ve written before about installing .NET Framework 1.1 and 2.0 side by side (SxS) on the same box.  Microsoft recently added to the mix .NET Framework 3.0, so it’s about time to clear up any confusion on the newest release.

Above all, you should understand that the version designation of 3.0 was strictly marketing-driven.  At the most basic level, .NET Framework 3.0 equals .NET Framework 2.0 plus a bunch of new DLLs.  If you install .NET Framework 3.0 on a box that does not already have .NET Framework 2.0, the installer installs Framework 2.0.  Therefore, if you already have .NET Framework 2.0 installed on a box, you should not be too concerned that installing 3.0 will break existing applications.  In fact, you will find that in the ASP.NET tab in IIS Admin, .NET 3.0 is not even a choice.

It is worth noting that .NET Framework 3.0 is pre-installed on Windows Vista and will be pre-installed on “Longhorn” Server.  However, .NET 3.0 may be installed on any Windows XP SP2 or Windows Server 2003 SP1 box.

If you’re working with a mission-critical server then you should, of course, be cautious and do some planning and testing anyway.  Just be aware that the 1.1 to 2.0 story is identical to the 1.1 to 3.0 story, because 3.0 is in effect 2.0 with more files.

By now you may be asking what .NET Framework 3.0 is good for if it is essentially Framework 2.0.  .NET 3.0 delivers a number of long-anticipated and important features, though mostly of interest to application developers rather than server administrators.  The list includes Windows Communication Foundation, Windows Workflow Foundation, Windows Presentation Foundation and Windows CardSpace.  You can read more about them on the official .NET Framework 3.0 site.

In summary:

  • .NET 3.0 is not a major change to the core .NET Framework as was 1.0/1.1 to 2.0
  • If you currently have only .NET Framework 1.0 or 1.1 and you are looking to install 2.0 or 3.0, see my side-by-side post
  • If you currently have .NET Framework 2.0, installing .NET Framework 3.0 is not a major change as the version numbers would suggest
  • If you currently run .NET 2.0 applications, they will not know or care that you have installed .NET 3.0

Please feel free to contact me with questions and your own experiences with the upgrade.  Thanks for reading!

Improve your Microsoft Management Console (MMC) 3.0 Snap-In Performance

You’re already familiar with the Microsoft Management Console (MMC) if you’ve ever opened Computer Management, IIS Administration or any of the other Windows management tools.  The most common version today is 2.0, found on Windows Server 2003 and Windows XP.  Back in March 2006, Microsoft released MMC 3.0 without much fanfare.  The big changes in 3.0 are a new programming model for .NET-based snap-ins and reliability improvements.  MMC 3.0 can isolate snap-ins from each other, at least if they are .NET-based, and do a bit better job of error handling and logging in general.

If you have not already installed MMC 3.0, you’ll need it before too long.  BizTalk Server 2006’s Enterprise Single Sign On Admin tool requires MMC 3.0, and there will be many more in the future.

There are freely available versions of MMC 3.0 for the various editions of Windows, but you need to get the right one for your OS.  Search Microsoft Downloads for ‘MMC 3.0’ to find the one you need.  You’ll need Windows XP SP2 or later, or Windows Server 2003 SP1 or later.  If you’re running Vista, you can stop reading now, because you already have MMC 3.0 and it is pre-optimized!

Once you have MMC 3.0 installed, you may find that snap-ins that rely on it (and you generally won’t know which ones do) start up slowly.  You may find yourself staring at the screen for a long time each and every time you start up one of those snap-ins.  The reason is that a bunch of code in MMC 3.0 is written in .NET.  No, that in itself does not make it slow!  For some unknown reason, Microsoft does not optimize the MMC 3.0 .NET code upon installation as it does with the .NET Framework itself.  (For you developers, the DLLs aren’t automatically GAC’d, nor are native images generated.)

There’s a very easy fix for this.  Speed up your MMC 3.0 snap-in startup times by opening a Command Prompt and running ‘mmcperf.exe’.  It’s in your Windows\System32 directory.  This will create ready-to-run versions of the MMC .NET code (native images).  You’ll notice a big difference in startup time — and we may never know why Microsoft didn’t just do this out of the box!  Note that this does not speed up the snap-ins once they are actually running.  This is related only to the startup time when you run MMC and load a .NET-based snap-in.

%d bloggers like this: