Problem: you have an application that needs to trigger some process via SQL Server but don't want your main process hung up waiting. So you decide to setup Service Broker in order to make an asynchronous call, with the receiving service doing your work for you. You've read all about it, and tried it out after hours, but it didn't work. What gives?

Problem: You have a long-running query in SQL Server that is causing failures all over the place. Activity Monitor shows you the cause is blocking.

Causes: Blocking essentially means "you’ve locked a table, and now someone else is stuck waiting for you to unlock it." We all know (or should know) that transactions cause table locks, and thus blocking. That’s one reason that transactions need to be short and sweet. But there is another source of blocking, one that can be a bit more subtle: long-running queries that are trying to read committed data only.

READ COMMITTED is the default isolation level for SQL Server 2005 (and most other versions, I presume). Ben Gan describes: "In this isolation level, processes request a shared lock to read data and release it as soon as the data has been read – not when the transaction terminates." Thus if you have a poorly-performing SELECT query, it might take a while before the shared lock is released. In the meantime, everyone else is blocked.

I've been having problems migrating some SSIS packages from localhost (32bit) to the server (Windows Server 2003 64bit, SQL Server 2005 Enterprise SP2). These packages have Excel data connectors, and hence must be run in 32 bit mode. When I try to run them with the 32bit dtexec, I get the "generic" error "the package failed to load" (0xC0010014). I stripped down the package until I found the source of the error: a ForEach container, using the File Enumerator with the directory set to a variable. When I remove the variable setting it works, when I add the variable expression it fails. Note: this does not fail in the 64bit dtexec.

I read KB 913817 and determined that this was not causing my problem. When I open the package in Business Intelligence Studio on the server itself, I get an error when I try to edit the expressions on the ForEach loop (but only when the Enumerator is set to File).Poking around the SSIS DLLs, I found c:\program files (x86)\microsoft sql server\90\dts\foreachenumerators\ForEachFileEnumerator.dll. Re-registered it with regsvr32. Voila, problem solved.

A few weeks ago I started trying to better understand the concepts behind service oriented architecture (SOA), how I can apply them to my work, and if doing so is actually a good idea. When I first started hearing about SOAP years ago, I understood it as an alternative to objects — you build and consume services instead of objects. This sounds great for highly distributed environments, but also sounds like a performance nightmare in a business environment that does not need distribution. So I largely ignored it, and assumed I could ignore SOA as well.

Years ago I knew how to configure the Apache webserver, back when I was a grad student / Linux admin. As any reader will have figured out, I've been a Microsoft-centric programmer for a number of years now. Thus it is not surprised that I would have forgotten some basics about Apache.

We recently returned to the discussion of using XML style transforms to format flat file output from Reporting Services. The report we created was pretty basic, so let's look at something more complex: a fixed format.

Let's say the client has given us the following file format, and we've trimmed the data in the SQL query to make sure it doesn't exceed the data width:

(Hm, forgot to continue this a few months back...)

In the third installment of a series, we defined some intended flat file output, designed a simple report in MSSQL Reporting Services, and looked at the plain XML output from Reporting Services. Now, long after that post, we can create and apply the XLST needed to convert that report to the desired output.

You've written requirements. You've created a detailed use cases, and unit tests, and reviewed all three. Code is delivered, passes system testing, moves on to UAT. Whoa, holdup there fella, where's the data validation that prevents the user from doing X? Its right there in the requirements. What happened? Not only was it missed in the unit testing (and possibly use cases), but it was missed in the review process as well.