A Going Away Present From TFS

Ah, TFS, you shouldn’t have. A gift? For me? I know I’m leaving but I really didn’t expect anything. Here, let me open it.

(sounds of paper being unwrapped, boxes opened, scotch tape sliced with an overly large kitchen knife)

The Present: “Note that you need to manually track the ID returned from your subscribe commands if you don’t plan on writing code. There’s no way to get a list of subscriptions from the command line. While you can poke around inside the SQL ServerĀ® 2005 databases, that is neither recommended nor supported.”

So, umm, TFS, if I created a subscription months ago, you’re telling me that I needed to manually track the ID so that I could unsubscribe from it months later when I was leaving? You suck TFS. You suck hard. I hope you kept the receipt because I want my damn money back.

Ordering Result Sets in SSRS By Parameter

I’m working on SSRS and reports as part of my current contract (you should pity me just a little) and we needed to order the result set of a report based on user input. I initially tried this

Select i.SkuNo, g.Reference from Item i
INNER JOIN GTable g on (g.ItemId = i.Id)
Where g.Category = @Category
ORDER BY CASE WHEN @OtherParameter = 'Some Value' THEN i.SkuNo ELSE g.Reference END

This worked fine as long as i.SkuNo and Reference were convertible to the same type, in this case a bigint. Unfortunately, Reference is a varchar and so if the table had some data in Reference that was not convertible to bigint, the report blew up. The fix ended up being pretty easy (they always are after they are fixed).

Select i.SkuNo, g.Reference from Item i
INNER JOIN GTable g on (g.ItemId = i.Id)
Where g.Category = @Category
ORDER BY CASE WHEN @OtherParameter = 'Some Value' THEN i.SkuNo END,
CASE WHEN @OtherParameter = 'Some Other Value' THEN g.Reference END

That worked like a champ. It didn’t make SSRS any more palatable but at least I can file another bug as fixed.