It's raining here today, starting to feel like home.

Many thanks to the folks that told me they had an excellent time delving into error handling in SQL Server 2005 server and clients during yesterday's talk. The demos are already posted on the SQLskills website; look under “Current Events” for the link.

Today's talk (to a smaller audience, only the hard core perf folks were there) was on plan guides and plan forcing in SQL Server 2005. The demos for this one will be up shortly, same general place.

My talk on error handling in SQL Server and clients starts in another hour. Error handling in SQL has been more of an art than a science in previous versions, and some folks just pass every error back to the client directly. With TRY-CATCH in Transact-SQL you don't have to do "passthrough" error handling now, but its not as "remove @@ERROR, plug in TRY-CATCH" as people first thought. Some folks wondered how I could do over an hour on errors alone; there's actually even more to it than that. I'm sure glad they have unit testing in Team System for DB Pros; it will sure get people past the "oh, that error will never happen" mentality.

The DLINQ BOF last night was amazingly well attended. I'm surprised that there were very few "this should all be done in stored procedures" afficianados in attendence. It would make things more interesting if DLINQ worked in SQLCLR. Hmmm... Then this would be IN stored procedures.

Started my part of the festivities today with a chalktalk mentioning SQL Server DDL triggers and Event Notifications. But explaining Event Notifications meant explaining Service Broker services, queues, and contracts. And the fact that both Event Notifications and DDL Triggers use a XML format. That you can change to a rowset format using the XML.nodes method. Which meant talking about XML.nodes. Showed a DDL trigger for CREATE ASSEMBLY that had to be written in SQLCLR.

Went on from there to setup a cross-database event notification, which (the way that I did it) entailed using the TRUSTWORTHY database property. And what TRUSTWORTHY means (I'll do a blog entry on that in future). And because cross-database service broker uses session keys (BEGIN DIALOG CONVERSATION uses encryption by default), each database must have a database master key. Explained what database master keys are.

Finally, I cross-instance event notification. Needed to explain what ENDPOINTs were and the ENDPOINT abstraction. And explain service broker ROUTEs. A good portion of the new "infrastructure type features" (I don't think they're only for developers any more) in one talk. That's some (fairly useful, I think) scope creep.

On to the WMI provider for events, and how this works with SQLAgent. Watching it use WAITFOR (a queue) and RECEIVE. Uh, oh...

If you haven't already seen the good news on our website, SQLskills welcomes Elizebeth Vitt. Elizebeth is well-known in the business intelligence community and will be our Director of Business Intelligence Skills. We're looking forward to even more synergy among members of the organization; Business Intelligence may be the most earthshattering part of SQL Server 2005. Welcome Elizabeth.

We're also "pre-announcing" a series of Immersion events, featuring in-depth architectural guidence. The events will be smaller to focus on personal attention. Check out the website for more details.

The week before TechEd, everyone's reminded to blog about what they'll be up to, to draw folks to sessions. The sheer number of presentations and topics this year, including breakouts, chalktalks, BOFs is overwhelming. The show is sold out, but some of the talks are being "simulcast" as well. So I'll be there talking about SQL Server and ADO.NET (surprise,surprise) but I thought I'd begin with the "extracurricular activities". And no, I don't mean the late-night parties in the evenings.

First off, SQLskills might have some exciting announcements coming that we've saved for the occasion. You'll have to find out about this one as it happens...hope you can stand the suspense.

Monday night, I'm participating in a mega-meeting of the New England User Groups on the TechEd premises.

Tuesday afternoon/evening, I'm participating in DataDirect Technology's ADO.NET get-togther hosted by Jonathan Bruce, with some of the ADO.NET team members. Followed by a DLINQ BOF with Sahil Malik. Oh, and I'm at the Addison-Wesley booth signing books at lunchtime. I may even have some books to give away at the events above...

Thursday, I'll be up bright and early (well, early, especially with the timezone difference) to answer questions of the broadcast during Greg Low's (Mr SQLDownUnder) simulcast session on SQLCLR for DBAs. Wonder what time it is in Brisbane then.

The chalktalks break down as follows. I'll try and leave lots of time at the end for your questions:
Monday 10:45-12 - DDL triggers, Event Notifications, and WMI in SQL Server 2005
Tuesday 8:30-9:45 - Security Controlling the new SQL Server 2005 features for DBAs: Service Broker, XML, and SQLCLR
Thursday 9:45-11 - Plan Guides in SQL Server 2005

Whew, some of the rest of the time, I should be hanging out in the "Data" breakout area. Oh, and my breakout session is Wednesday June 14 - Everything you always wanted to know about error handling in SQL Server 2005, server and client but were afraid to ask. The official blurb is:

DAT424  SQL Server Error Handling: T-SQL, SQLCLR and Clients
Day/Time: Wednesday, June 14 2:00 PM - 3:15 PM  Room: 157 ABC
Speaker(s): Bob Beauchemin
In SQL Server 2005, both T-SQL and SQLCLR procedures can use Try-Catch constructs to intercept and process exceptional conditions. This is a big change for both server and client/middle-tier processing, as client and middle-tier received and processed all errors directly from SQL Server. In addition, both T-SQL and SQLCLR error handling don't catch every type of error or allow the ability to directly rethrow all errors to reproduce SQL Server 2005 behavior. This session looks at the mechanism in-depth and provides a comprehensive and consistant error handling strategy that works for all procedural code and clients.

What a slacker, eh? So be sure to stop by and say hi. I'll also try to blog some during the "activities" going on, especially the buzz surrounding Visual Studio Team System for Database Professionals (aka DataDude).

Did I remember everything...? (hope so)

I've been looking at the DLINQ syntax a bit more (I'm participating in a BOF at TechEd next week), and its always struck me how, at its most basic, its reminiscent of XQuery FWOR. Yes I did leave out the L (Let) on purpose. I guess its the relative placement of the SELECT part of the query:

FWOR:

for $x in /customers/customer/address
where $x/city = "Portland"
order by $x/zipcode
return $x

DLINQ:

from a in db.Address
where a.City == "Portland"
orderby a.Zipcode
select a

I've done the FLWOR/SQL comparison quite a bit. Just replace "for" by "from" and "return" by "select". The ordering of the keywords in DLINQ just makes it a bit more obvious. Or maybe its the "in" keyword in the from/for clause. The superficial similarity kind of ends there, however.

I usually show folks the SQLCLR streaming table-valued functions and using SqlDataRecord with SqlPipe.SendResultsStart/SendResultsRow/SendResultsEnd on the same day. This sometimes brings up the question "which is better"? Since you can synthesize rowsets with both mechanisms and they both stream rows back to the client one buffer-worth at a time, its an interesting choice. However, with SqlPipe/SqlDataRecord, you can synthesize multiple rowsets in the same stored procedure. Just cruft up two different SqlDataRecord With multiple SendStart/SendRow/SendEnd blocks. Can't do that with any kind of table-valued function. You must "finish" one rowset (SendResultsEnd) before "starting" the next (SendResultsStart), though, you can't have interleaved output any more than you would with an ordinary cursorless rowset. Example follows:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void MultiRowset()
{
SqlMetaData[] m = new SqlMetaData[1] {new SqlMetaData("colname", SqlDbType.NVarChar, 5) };
SqlDataRecord rec = new SqlDataRecord(m);
rec.SetSqlString(0, "Hello");

SqlMetaData[] m2 = new SqlMetaData[2] { new SqlMetaData("another", SqlDbType.NVarChar, 5), new SqlMetaData("number", SqlDbType.Int) };
SqlDataRecord rec2 = new SqlDataRecord(m2);
rec2.SetSqlString(0, "Goodbye");
rec2.SetSqlInt32(1, 42);

SqlPipe p = SqlContext.Pipe;
p.SendResultsStart(rec);        // first rowset
for (int i=0;i<10;i++)
 p.SendResultsRow(rec);
p.SendResultsEnd();

p.SendResultsStart(rec2);       // next rowset
for (int i = 0; i < 10; i++)
    p.SendResultsRow(rec2);
p.SendResultsEnd();
}

People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form "can I use assembly System.XYZ.dll in SQLCLR procedural code" or "why do I get "assembly System.XYZ.dll is not found" when I try and catalog my own assembly that calls this one? The ones is hear mentioned most frequently is System.DirectoryServices.dll (Active Directory support) or System.Management.dll (WMI support) or System.Remoting.dll et al. The only way you use these is to run CREATE ASSEMBLY on them yourself, which involves using PERMISSION_SET = UNSAFE. And cataloging all the dependencies. Not for the faint of heart.

I explain that there is a hardcoded list of assemblies that SQL Server will load from the file system and these assemblies have undergone an extensive code review to ensure they don't destablize SQL Server when they are running in process. Since some assemblies contain both crucial and "unsafe" (to SQL Server) classes (mscorlib.dll is an example that comes to mind) these libraries must also be decorated with instances of HostProtectionAttribute. This attribute gives the host (SQL Server 2005, in this case) the final say over whether to run a method (e.g. Thread.Start in mscorlib.dll). SQL Server is very picky about when it will run an HPA decorated method, its only if the calling assembly is CREATEd with PERMISSION_SET = UNSAFE. There's more about this in chapter 2 of A Developer's Guide to SQL Server 2005.

I refer to this list of assemblies as "the approved list". You can see most of the "approved list" assemblies by creating a Visual Studio 2005 Database/SQL Server project and choosing "Add Reference". This is the entire list of approved assemblies with one exception. Right before RTM "System.Configuration" was added to the list. I'm not entirely sure why, because although you can successfully use a .NET config file with SQL Server 2005 currently, it neither supported or encouraged (see my blog articles about this).

Why the long story? Because, in SQL Server 2005 SP1 another assembly made the list: System.Deployment.dll. I'm rather puzzled by this one too, because it has to do almost entirely with click-once deployment. Not sure why someone would want a sproc to do this, unless it was used to tie SQL Server to .NET project deployment in conjunction with Visual Studio Team System. But, its in there. Hmmm...

The libraries that I'd hoped would make the list eventually, perhaps in a SQL Server service pack when Windows Vista and WinFX are released, are the Windows Communication Foundation (System.ServiceModel.dll) and Windows Workflow Foundation (System.Workflow.Runtime.dll et al). I've heard a lot of folks ask the same question, but so far there's been silence on this. Since System.WebServices (but not WSE) is aleady "in there", there might be some uses going forward. As a look toward "intent", I ran ILDASM against the just-released May CTP version of these. Not a HostProtectionAttribute in sight. Oh well.

SQL Server 2005 doesn't support assembly versioning in SQLCLR. Recently in a talk I restated this as "SQL Server 2005 will not allow two versions of the same assembly to be cataloged into the same database using CREATE ASSEMBLY". Someone promptly pointed out that they could indeed run CREATE ASSEMBLY on two instances of the same code, if one was strong-named and the other was not strong-named. That is the case, but that is not support of assembly versioning. The .NET Framework Developer's Guide states "Versioning is only done on assemblies with strong names".

As an example, if I run CREATE ASSEMBLY on an assembly without a strong name, the query "SELECT name, clr_name FROM sys.assemblies" produces the following output:

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil

Note the version in clr_name is "0.0.0.0" regardless of the version number that is stored in the [assembly:AssemblyVersion] attribute in the file AssemblyInfo.cs. Attempting to catalog another version of the unsigned assembly, code or AssemblyVersion attribute change or not, produces the error:

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

If I have strong named assemblies, I can only have one strong named version that differs by version. Running create assembly (on a strong-named "version 1") gives the following output using "SELECT name, clr_name FROM sys.assemblies"

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
v1                  versioningtest, version=1.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil   

So I DO have "two copies of the same" assembly in the database at a time. However attempting to run CREATE ASSEMBLY on a strongly typed version 2.0.0.0 yields the following error.

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=2.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

So assembly versioning is not supported. Note that you can have assemblies with different 4-part names that differ only by culture. Assemblies that include a culture other than "neutral"; these are resource-only assemblies such as localized strings for different cultures. How does SQL Server 2005 decide which culture to use? SQL Server itself really doesn't decide, but that's another story for another day.

Been on the road lots lately, not much blogging, I'm afraid. I'll be making up for that...

I "found" a new command line utility today in SQL Server 2005. Maybe everyone but me already knew it was there. Reading up on replication, I came across mention of the "tablediff" tool. You feed it and source and destination table and it will spit out a report of data differences (the table definitions must be identical), on a row level or column level. It's meant to allow you to fix problems if you perform an operation that gets databases involved in transactional replication get out of sync. But it can be used outside of replication just as well. It lives in the ...Microsoft SQL Server\90\com directory.

Nice diagnostic and synchronization tool. My favorite option is -f which generates TSQL statements to get the tables back in sync.

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: