Although I'm away from home this week (teaching in Hyderabad) I've heard that the first advance copies of Dan Sullivan's and my new SQL Server 2005 book, A Developer's Guide to SQL Server 2005, have made their appearence. Earlier than expected. Although we RTM'd before SP1, printing takes about 3 weeks and the service pack just beat us out the door. Lost a bet on that one... It's got much enhanced, detailed information, including a chapter on SMO, the SQL Server Management Objects API. Hope you like it. Look for an updates/code page on the SQLskills website soon.

Another item on my TechEd Boston dance card that slipped my mind is that I'll be doing book signings at the Addison-Wesley booth. See you there.

Thanks to my co-author, all of our many excellent reviewers, and the folks at A/W who made this possible.

As you can see, I'm trying to catch up on my blogging. Here's one I missed. I'll be at TechEd 2006 in Boston in June. It will be good to return to the location of my first-ever data processing job (yes, it was called data processing then, in the year 1977. and no, I'd don't count programming in grade school in my resume, I was 24 then). My TechEd dance card is filling up as I write this. So far, there will be:

A Breakout Session on SQL Server 2005 Error Handling: T-SQL, SQLCLR, and clients
Chalktalk on SQL Server 2005 Event Notifications, Database Triggers, and WMI
Chalktalk on SQL Server 2005 Plan Guides and Plan Forcing
Chalktalk on Controlling the new SQL Server features: Setup, Security, and Monitoring
DLINQ BOF with the amiable and outspoken Sahil Malik
New England SQL Server and Boston .NET user group meeting on a panel with Adam Mechanic (hopefully) and others

Whew, that ought to keep me busy. See you there.

This one ought to be titled "Your error message is such a tease".

I've always liked the fact that SQL Server 2005 error messages are verbose (modulo security considerations for login error for example). I always point out at least one during classes, start to read the message...and about 50 words or so later, tell students to "check out the entire text of the message offline". Excellent! Of course message boxes in applications that display these verbose errors must be made bigger or scrollbars added. Such is life.

So, in SP1, my perfectly working WMI alerts stopped working. Looking at the job history, I found the following:

Unable to start execution of step 1 (reason: The job step contains one or more tokens. For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run.). The step failed.

Great. Except what's the magic macro??? What a tease, everything I need to fix things except for the most vital piece of info. Downloaded the April Books Online update, which had a TON of great new and revised content. But nothing about this problem (well they can't catch everything right away). Then I remembered RTF-readme file. Went to the latest "KB 913090 - A List of bugs that have been been fixed" and found IT had been updated with a reference to "KB 915845 - SQL Server Agent jobs fail when the jobs contain job steps that use tokens after you install SQL Server 2005 Service Pack 1". Yep that's the one!! The magic macros are:

$(ESCAPE_SQUOTE(token))
$(ESCAPE_DQUOTE(token))
$(ESCAPE_RBRACKET(token))
$(ESCAPE_NONE(token))

There's even a nice script that catalogs a procedure to update your existing jobs. What could be easier? Except, attempting to catalog the procedure yields "A fatal scripting error occurred. Incorrect syntax occured while parsing '. Huh? That's too sparse of an error. What now, the saga continues....

Turns out this error occurred because I had SSMS working in SQLCMD mode by default (see "What's fixed in SP1 blog entry") and because there was a token $anything in the code, it was being processed by SQLCMD. Ohhhh. Turned off SQLCMD mode and it worked fine. Jobs fixed and working. The jobstep SQL now looks like this:

INSERT INTO DeadlockEvents(AlertTime, DeadlockGraph)
  VALUES (getdate(), N'$(ESCAPE_NONE(WMI(TextData)))')

So that's "the whole story". Hope it saves you some grief. That said, I REALLY like the WMI and Event Notification idea a lot. It's like having the database "tell me" when there's a problem that I want to know about. In real-time or queued and saved as I sleep for resolution "at my leisure". My leisure...ha, how about "the next morning"? I'll take that.

Lately I've been working on using WMI events in conjunction with SQLAgent alerts and jobs to notify operators, queue up notifications for the DBA's resolution offline, and other uses. Setting up the alert and the job is fairly straightforward, where I stumbled at first is referencing WMI variables (and even knowing what variables were available) in the job that processes the WMI event. The varaibles are doc'd in BOL in conjunction with event notifications, which is really what the WMI provider for Events uses underneath.

You use a WQL query that references the event to set up the WMI Alert, using "SELECT * FROM <my_event>" syntax (BOL example is SELECT * FROM DEADLOCK_EVENTS). And reference in job by using a WMI variable, like so:

-- BOL example
INSERT INTO DeadlockEvents(AlertTime, DeadlockGraph)
  VALUES (getdate(), N''$(WMI(TextData))''')

So, when you set all this up, why doesn't it work? It always produces "TextData not available". Hmmm...The "magic switch" to success is found under SQL Server Agent "Properties" menu entry under SSMS. You need to turn on (in the Alert System tab, at the bottom of the page) "Replace tokens for all job responses to alerts". They aren't WMI variables, they are WMI tokens. Success! Took me a while to find that one...

But, in SP1 it "breaks" again. Sigh. More about this in next blog entry.

You've likely heard by this time that Service Pack 1 for SQL Server 2005 was released earlier today/yesterday depending on what timezone you're in. Rather than go into how to get it, there's two KB articles that are really useful, KB 913090 - A List of bugs that have been been fixed and KB 916940 - A list of the new features and improvements. I noticed a couple tiny ones in SSMS that weren't listed in either document, so I'd bet there are likely more unlisted improvements and fixes.

Displaying the word "NULL" for a NULL instance of a user-defined type in "Results to Grid" mode works now, without having to install the UDT in the GAC or SSMS directory. This is even improved over the fix in SP1 CTP, where it worked in the UDT was installed. Prior to the SP, you could display it in "Results to Text" mode, but Grid mode threw an exception. Fixed now.

If you choose the "By default open new queries in SQLCMD mode" in Tools/Options Query Execution, the setting "sticks" now when you close SSMS and reopen. I know at least one person that will be happy with that.

That's all for now.

Since we were on a roll the SQL Server 2005 discussion, Richard and Carl had me back this week on dnr-TV. It's a combination of podcasting and demonstrations using Visual Studio, and I obliged with an hour demonstration of using SQLCLR with Visual Studio. I also talk about some of the things to watch out for in your SQLCLR coding...hope you find it useful. Should be available tomorrow...

I had the good fortune to be able to spend an hour or so (may have been a bit longer chatting with Richard and Carl last week on .NET Rocks! about SQL Server 2005 and the state of the world in general. This episode should be available on Tuesday, Apr 18 at the usual places, Franklin's.NET and MSDN. Although the topic was originally to be SQLCLR, things quickly shifted into a discussion of snapshot isolation (aka versioning), Service Broker, XML support, statement-level recompilation and other topics database folks enjoy talking about. Hope you'all enjoy it.

I just finished my second talk in the “designing and managing scalable systems with SQL Server 2005” webcast series. I got to talk about one of my favorite features in SQL Server 2005, the Service Broker. Some DBA may still think that Service Broker is a developer-only feature, but after you've seen what you can do with Event Notifications (specifically the blocked process event) today, I know you're convinced it also is a substrate for some of the more powerful tools in the DBA toolbox. As well as a great way to help acheive a reliable, scalable system that supports scale out.

I'm posting the broker configuration scripts here, but if these aren't sufficient to walk you through all the points I made in the webcast, feel free to write. I've also given seminars on advanced Service Broker concepts and implementation from a developer and DBA perspective; write if you're interested in learning more.

I referred in the webcast to Roger Wolter (one of the “fathers of Service Broker” 's) weblog, this is listed in the Blogroll at the left. In addition Rushi Desai, Jesús Rodríguez, and Dan Sullivan's blogs have some good info, too. Rushi and Jesús have written some amazing utilities around Service Broker.

Roger's “Where does Service Broker fit in?“ (with respect to MSMQ, Biztalk, and WCF/WWF) blog entry is the Feb 28 entry, but each of these blogs is worth subscribing to...

Here's the scripts from the presentation: