In the April CTP of .NET 2.0, I chanced upon some changes to SqlNotificationRequest, using my favorite tool, .NET Reflector and my one my favorite investigative techniques, called “follow the error message”.

It turns out that two properties in SqlNotificationRequest the id and Service properties are about to be replaced. They still work but are marked “do not use, to be removed”. They'll but replaced by the UserData and Options properties. UserData appears to be a straight replacement for id, probably mandated by the .NET naming police. The naming police are possibly the same folks who replaced SqlContext.GetPipe and GetWindowsIdentity methods with the Pipe and WindowsIdentity properties. Making things consistent is nice, but its hard on folks who write things like demos, labs, slides, books, and articles.

SqlNotificationRequest.Options is more interesting. While the Service property only let you specify the Service Broker Service name, options gives you...you guessed it, more options. You can also specify which database the broker service lives in or even the Broker indentifier.

Suppose you wanted to listen for query notifications on a query on a table in the pubs database, using a service named “MyService” that also lives in the pubs database in your local SQL Server instance. Using the soon-obsolete Service and Id properties it would look like this:

SqlNotificationRequest not = new SqlNotificationRequest();
not.Id = Guid.NewGuid().ToString();
not.Service = "MyService";
not.Timeout = 0;
// now hook it up to the right SqlCommand

Using the new syntax would look like this:

SqlNotificationRequest not = new SqlNotificationRequest();
not.UserData = Guid.NewGuid().ToString();
not.Options = "service=MyService;local database=pubs";
not.Timeout = 0;
// now hook it up to the right SqlCommand

You can even use the Service Broker identifier GUID (look it up by “select name, service_broker_guid from sys.databases”) in the Options like this:

//NB: Service Broker service names are case sensistive!
//not.Options = "service=MyService;local database=pubs";
not.Options = "service=MyService;broker instance=CE086F11-C691-47F1-A8B6-1B7BD59EA6AE";

This property gives you the option of pointing at a service in a different database in the same instance, or even a different instance, subject to sercurity, of course. Happy query notifying. I gotta go fix a paper. And a book chapter. And a slide. And a lab. And... geez.

While at TechEd, I purchased the first computer book I've actually had to shell out hard cash for in quite a while, The Rational Guide to SQL Server 2005 Service Broker by Roger Wolter. Congratulations, Roger, you've got a winner here! Weighing in at just under 250 pages, this book is an explanation of Service Broker from its raison d'etre to the most intricate details. The first chapter says it's meant to compliment the info in the BOL, and the material fits right in while BOL leaves off.

I've had the pleasure of having Roger explain the Service Broker to me in person, quite a few times, but the book lays it all out as though you'd hung out with him for months. A great explanation of this extermely powerful but often misunderstood feature of SQL Server 2005. You might have to wait in line for this one, but it's worth waiting for. I got mine autographed too.

Are there any superlatives I've left out? Well if so, fill 'em in yourself.

I meant to blog from TechEd but with all the activity, I never got the time. The talk on when to use T-SQL and when to use SQLCLR drew an overflow crowd, and folks seemed to really like it. Thanks to all of you who attended. 

To attempt to summarize in one sentence, the “official” T-SQL vs SQLCLR positioning exists in the paper, “Using CLR Integration in SQL Server 2005” by Balaji Rathakrishnan, Christian Kleinerman, Brad Richards, Ramachandran Venkatesh, Vineet Rao. Since this is the list of folks who designed the product, it lays out the trade-offs and intended usage. Although the specifics have changed a bit since the paper was written (combined provider replacing SqlServer provider, new UDF coding style, etc) the paper's main points are just as valid today as they were when it was originally written, according to the authors.

Other than the talk, I spent a lot of time at the SQL Server cabana, hanging out with Kent Tegels and the folks on the SQL Server team, answering and asking questions. I especially enjoyed the “SQL Server 2005 BI Power Hour” talk, showing off use cases for SQL Server Integration Services, Reporting Services, Analysis Services, and Notification Services. My favorite demo showed how Reporting Services could be used to design a template for a Notification Services message by Shyam Pather. Very cool, Shyam. I hope we'll be seeing many more ways to integrate the various SQL Server 2005 product features in future.

I also spent quite a lot of time catching up with many old friends who I haven't seen in a long time. And also making some new friends. I don't do a lot of conferences as a rule, but this one was well worth it.

I commonly do a demo when teaching SQL Server 2005 where I write a SQLCLR UDF that's returns the string "Hello World". The define it, sans VS autodeploy, like this.

CREATE FUNCTION somefunc()
RETURNS NVARCHAR(4)
AS EXTERNAL NAME MyAssembly.MyClass.MyFunction

When invoked, it returns "Hell", silently truncating the string the CLR sent it. UNTIL Apr CTP. Now its returns "Truncation Exception". Surprise, surprise... After reporting this as a bug, I was told that "That's the way its supposed to work. To prevent silent data loss."

I agree. Except now its works differently than this T-SQL function:

CREATE FUNCTION somefuncT()
RETURNS NVARCHAR(4)
AS
BEGIN
  RETURN N'Hello World'
END

Correct again, it does work differently. The idea is the T-SQL one still silently truncates data, but the SQLCLR one does "the right thing". The T-SQL one still works the way it does for backward compatibility only. Maybe in the next release they'll work the same.

So what's your preference, backward compatibility or lack of silent data loss (and exceptions)? Just curious...

SQL Server 2005 will, by default on Windows Server 2003 systems, enforce password policies for SQL Server logins as well as Windows logins. Nice feature, but this means that your SQL Server login password can expire. So how do you change it? Well certainly the DBA can change it as (s)he always has, but you'd hate to bother your DBA every 42 days. Never mind what the DBA would think of that... And the user interface programs, SSMS and SQLCMD don't yet provide that feature. Neither does Visual Studio 2005 Server Explorer.

The functionality does exist in the supported database APIs. That is ADO.NET, OLE DB, and ODBC. With ADO.NET you have to be using 2.0, with OLE DB and ODBC the new SNAC providers are required. In ODBC, there is a new connection option SQL_COPT_SS_OLDPWD. In OLE DB there's an Old Password connection string parameter. In ADO.NET 2.0 SqlClient it's a static method on SqlConnection called (amazingly enough) ChangePassword. It takes to strings as input and here's how it works.

You change your connection code to use a loop, like while conn.ConnectionState == ConnectionState.Closed. Loop as many times as you like, most folks will probably loop twice. Bracket your calls to Open with a try-catch block. In the catch block, look for the following error codes:

18487 - Password Expired
18488 - Must change password on first login

If you get one of these call ChangePassword. You'd think that the parameters are "old password, new password". They are not. The first parameter must have enough information to connect to the server, including at minimum server name, your userid and your old password. The second parameter is just your new password. This changes your password, now change your connection string and Open again.

There are a couple of repercussions/refinements to this:

1. You obviously shouldn't even think about keeping password in the program, if you ever did this before. Check out my MSDN article for the built-in place to keep connection strings now.
2. This can only be used to change passwords on a SQL Server 2005 server. And only SQL Login passwords, naturally. It requires that the server and client be using the new network libraries.
3. There is no standard "New Password", "Old Password" GUI box. You need to make one yourself. Standard cavaets for passwords in GUIs apply.
4. There is no way to currently tell with standard SQL Server calls, how soon your password will expire. Think "Your password will expire in N days" message we all know and love.

So that's it. I have a "rough and ready" code example (that I wrote on a bet with Larry Chestnut at an Ascend gig a while ago) I'll probably clean up and post on my website eventually but this gives you the basic idea. And BTW, this isn't meant to push SQL Logins on anyone. If you can use only Windows logins in SQL Server (any release) and forgo SQL Logins entirely, PLEASE DO.