Service Broker security is the subject of confusion even among people who think they know how it works. Some of the confusion occurs because security was tightened up in the last few CTPs.

I've read in two different places that Service Broker conversations always need to have a master key in the database(s) where the services run. Not so. You don't need a database master key (and this is in RTM) if:

1. Both services (initiator and target) live in the same database
2. You begin the conversation using ENCRYPTED = OFF in the BEGIN DIALOG statement

ENCRYPTED = ON is the default, and you do need a database master key in this case, hence the confusion.

After speaking yesterday and recanting my story (for about the 100th time) about EventData being XML as a “nefarious plot” to require DBAs to learn XML, I wrote this fairly simple XQuery (actually dead simple XPath) function to change EventData into a rowset. It works based on the fact that you've deposited EventData into a table that looks like this:

CREATE TABLE ddl_log (
 id int primary key identity,
 data XML
);

CREATE TRIGGER mytrig
ON DATABASE
FOR CREATE_TABLE
AS
INSERT ddl_log VALUES(EventData);

This makes your table into a rowset. You could also use a variation of it in your event notification handler, DDL trigger itself. Just leave out the cross apply. I thought I'd seen this before, but can never seem to ever have found it. Now that I've done this, ....learn XML why don't 'ya... There can only be more of it in future. Cheers.

SELECT id, 
 Tab.Col.value('./EventType[1]','nvarchar(50)') AS 'EventType',
 Tab.Col.value('./PostTime[1]','datetime') AS  'PostTime',
 Tab.Col.value('./SPID[1]','nvarchar(50)') AS  'SPID',
 Tab.Col.value('./ServerName[1]','nvarchar(50)') AS  'ServerName',
 Tab.Col.value('./LoginName[1]','nvarchar(50)') AS 'LoginName',
 Tab.Col.value('./UserName[1]','nvarchar(50)') AS 'UserName',
 Tab.Col.value('./DatabaseName[1]','nvarchar(128)') AS 'DatabaseName',
 Tab.Col.value('./SchemaName[1]','nvarchar(128)') AS 'SchemaName',
 Tab.Col.value('./ObjectName[1]','nvarchar(128)') AS 'ObjectName',
 Tab.Col.value('./ObjectType[1]','nvarchar(50)') AS 'ObjectType',
 Tab.Col.value('./TSQLCommand[1]/CommandText[1]','nvarchar(4000)') AS 'CommandText',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULLS','nvarchar(3)') AS 'ANSI_NULLS_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULL_DEFAULT','nvarchar(3)') AS 'ANSI_NULL_DEFAULT_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_PADDING','nvarchar(3)') AS 'ANSI_PADDING_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@QUOTED_IDENTIFIER','nvarchar(3)') AS 'QUOTED_IDENTIFIER_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ENCRYPTED_OPTION','nvarchar(4)') AS 'ENCRYPTED_OPTION'
FROM ddl_log
CROSS APPLY
 data.nodes('/EVENT_INSTANCE') AS Tab(Col)
GO

-- standalone trigger

CREATE TRIGGER mytrig
ON DATABASE
FOR CREATE_TABLE
AS
DECLARE @x XML
SET @x = Eventdata()
SELECT
 Tab.Col.value('./EventType[1]','nvarchar(50)') AS 'EventType',
 Tab.Col.value('./PostTime[1]','datetime') AS  'PostTime',
 -- rest of columns deleted for brevity
FROM @x.nodes('/EVENT_INSTANCE') AS Tab(Col)
GO

I've been going over how errors are raised in SQLCLR against the SQL Server 2005 RTM version. If you remember (search the blog on "6522" if you don't) how errors made their way from the managed to the unmanaged stack evolved throughout the beta. My goal in investigating this was to determine if you can make SQLCLR acts the same way that T-SQL acted with respect to errors. Then, you could replace T-SQL functions and procedures with SQLCLR without changing every caller.

6522 (that's a general error that means "an error in the SQLCLR stack occurred) is still with us. You get one if there is an unhandled exception in ExecuteNonQuery or ExecuteReader inside a SQLCLR proc.If you want this error to go away and want only the original SQL error (e.g. 547 referential integrity error) to be returned to the caller, the only way to do this is to use SqlPipe.ExecuteAndSend in your SQLCLR procs. If you have no catch block, both the 6522 and the "original" (e.g. 547) error will be returned. If you're called from client code, the error number is e.g. 547, the 6522 follows afterward. If you want to "lose" the 6522 altogether use ExecuteAndSend and use a dummy try-catch block in your SQLCLR code. It would look like this:

try {
...
...
SqlContext.Pipe.ExecuteAndSend(cmd)
}
catch { // no code here
}

In this case you only get the original error e.g. the 547. Note that if you use SqlCommand's ExecuteNonQuery or ExecuteReader with the dummy try-catch concept, you lose the error entirely.

Bear in mind that if you use T-SQL's TRY-CATCH in SQL Server 2005, there is no way to raise *exactly* the original error either. You can come close, but the is no RETHROW. You can use RAISERROR, but RAISERROR doesn't work with system errors. You can reformat the original error message in a user error.

Upshot of this is that if you convert to either SQLCLR or T-SQL TRY-CATCH and depend on seeing the original error number at the caller (normally the case even if you have error handling in your procs), you're going to be changing the callers' code. Or using only ExecuteAndSend.

I'm headed out on the road. For quite a while. Wanted to settle this before I went.

Looks like the implementation details of SqlDependency have changed a bit. Even since the September CTP (where they added SqlDependency.Start and Stop). Stop and Start control the (new) internal listener. Start also sets up a stored procedure and a Service Broker Dialog Timer. Every 120 seconds, the timer fires, which activates the procedure, which sets the timer again. The first time I saw this, I thought the procedure was polling. Actually its more like its trauling. But its not polling (whew).

The way SqlDependency works now is that requests for query notifications set up their own broker services to catch the notification message. The activation proc (also set up for the notifications) relays the notification message back to the client. Which calls your event handler, or in the case of ASP.NET's SqlNotificationRequest, invalidates the cache.

So what's the timer for? The timer is trauling to find services and procedures (they have GUIDs in their names) set up by SqlDependency instances and will clean them up if it finds any. This is NOT on an individual SqlDependency instance basis, but on a *per-listner* basis. That is, once for every client that calls Start.

Take home message is, now more than ever, you need to limit the number of clients that call Start. Maybe only ASP.NET (or other) caches???

I'm glad that's settled. See you on the road I'm teaching/talking about this more in person...c'mon over and say hi.

I like to read the readme files. There's often juicy little tidbits of info in there that won't show up by searching the BOL (that's why there's a readme in the first place). In SQL Server 2005 there 3 “readme” files: requirements, readme, and “addendum to the readme” (available on web via a link in the readme). In the requirements file (ok, so I'm not done yet), this caught my eye:

“Native Web Service (SOAP/HTTP) support is only available for instances of SQL Server 2005 running on Windows 2003”

Reason this interested me is that, AFAIK, the technical requirement for HTTP in SQL Server 2005 is an OS that supports HTTP.sys, that is the kernel-mode HTTP stack. But Windows XPSP2 supports this too. During the beta, it was listed as OK, and the devs even answered newsgroup questions on problems with that configuration.

So I deduce what this means is, although SOAP/HTTP would technically work on XPSP2, its not officially supported, i.e. don't call tech support with your problems when using this configuration.

Or am I reading too much into one sentence in a readme file?

Now that SQL Server 2005 has been released, I'm becoming more "at one" with the tools. I've commented on a couple of cool features as things evolved, but having worked with the product since it's early beginnings I've also learned a lot of DDL. And learned early on that, "just because you can't do it with the graphic-user interface doesn't mean it can't be done". I'm pleasently surprised of the evolution of SQL Server Management Studio. It can do almost everything...but CREATE a QUEUE or SERVICE from Object Explorer (A minor inconvenience). The cool things far overshadow the few limitations.

However...today I came across two add-ins for SSMS that I can't do without. Already. First one solves the hassle of not being able to rearrange the folder entries in SSMS Projects in alphabetic order. I always thought this would appear in the "next CTP, right around the corner. It didn't. And Jasper Smith published this nice "external tool" that I now can't do without. Thanks Jasper.

I've always loved intellisense. Not that I can't memorize tons of API interfaces and methods if I have to/use them all the time. The OLE DB interfaces and methods come to mind. But I always used Visual Studio, even in the early .NET pre-alpha days (when EMACS for Windows made a comeback) because of the intellisense. One of the interesting side-effects with intellisense is most often, if it doesn't show up in intellisense, you're pretty sure you've mistyped the class/interface name.

Second SSMS add-in I can't do without is called PromptSQL. Very (sorry VERY, VERY (2 capital “very“s)) cool add in. I use it mostly in SSMS, but it also supports Query Analyzer and Visual Studio database projects. It has one superb feature I wish they'd have in VS intellisense. The "aggresion level" of intellisense, ie, how long it wait before popping up the intellisense is configurable. Although its mostly the "aggresion level" of automatic statement completion in VS that's a pain, especially in VS 2005 it's way too aggressive.

Anyway I've a convert to the tools. And certain add-ins. I've probably created my last trivial DDL without Template Explorer. And puzzled over table names without intellisense for the last time. Back to work...it's only just after midnight. What IS ASP.NET Cache Sync doing with Service Broker anyhow?...story for another day.

After reading my last blog entry, Wally McClure points out that I did indeed meet him once. In July 2001, on Redmond campus. Amazing. And of course, now I *do* remember it, it was a .NET (1.0) authors summit. I think. He looked just like his picture. ;-) Sorry...more technical content coming soon.

The podcast I did with Wally McClure is up on his website. Here's the link to it. I did this podcast about a month ago, so some of the musings might be old news, but Wally wanted to time the podcast's release with the release of SQL Server 2005 and Visual Studio 2005. So it's available today. He's posted a really nice tag line with it...Wally, you're too kind.

Although I've still never met him in person, Wally and I go way back (to my OLE DB days) via email. He claims I'm even referenced in his new book, though I haven't got my copy (hint,hint) yet. Some day I may actually get to meet him.

In his blog posting, he mentions that my SQL Server 2005 beta book is no longer on sale. Not true, its still around...although Dan and I are rechecking every fact/code example for the new one as I write this. It will be called "A SQL Server 2005 Developer's Guide", and will be released early next year.

Although most folks really liked the "First Look" book, I did get some complaints about it being based on a beta version (beta 2, actually) of products, rather than waiting for the "golden bits". I find it interesting that there are lots of books appearing daily on SQL Server 2005/VS 2005/.NET 2.0 right now. With the lead time that most publishers have, these must ALL be based, to some extent, on pre-release versions of the software. Albeit quite a bit newer pre-release versions than beta 2.

A lot has happened since beta 2. The latest development is that some of the concepts of ObjectSpaces and X# (aka C-Omega) have morphed (with LOTS of further development) into LINQ/DLINQ/XLINQ. I'm intrigued.

So our new book will be based on the "golden bits". And it will be right up to date...up until SQL Server 2005 SP1 appears. So writing any book on software, beta or not, is aiming at a moving target to some extent. Although the beta book had lots of code, it was really meant to be a "concept" book. The base concepts haven't changed at that much, although the implementation has evolved.

The new book will have LOTS of new material. And revised material. That's because the folks that work on SQL Server, .NET, and Visual Studio have been working REALLY hard since the beta to bring you the best product. And they're still working hard right now..on V-Next. And SP1.