Be sure to visit all the options under "Configuration" in the Admin Menu Bar above. There are 16 themes to choose from, and you can also create your own.

After writing a blog entry on "How DO you change your SQL Login Password" and complaining that "neither" SSMS nor SQLCMD allows you to change this" I was using this feature on the June CTP and, lo and behold, both of these utilities allow changing your password.

SQLCMD -? shows
-z new password
-Z new password and exit

And in SSMS, if you use a SQL login whose password has expired (or is designated "must change on first login") you get a nice, GUI-based “old password/new password/re-enter new password” prompt.

Don't know how I missed this before, sorry for complaining. If you use SQL Logins with login policies, code like this really should be part of your application. Perhaps a way to change password before it expires as well, like SQLCMD does. I'm still surprised that SSMS doesn't have this option on the login screen.

Sometime between when I left for Europe and when I started reading email that wasn't addressed "question from student" (yes, I do answer all those), my "Database Geek of the Week" interview got published. By a guy from Red Gate Software named Douglas Reilly.

The way I think this works is that someone suggests you for this honor. I don't know who suggested me, I wasn't told. Then Douglas writes some nice stuff about you and thinks up some questions he'd like to ask. Custom questions, too, from the way they sounded. Cool. He sends them to you and you answer in email. And he publishes questions and answers. Interesting concept.

On "my interview" page, there are links to articles (not mine) about "ADO.NET Data Access" and ".NET 2.0 transaction model". How relevent and coincidental.

Anyway, I'm honored...that someone would take the time to write questions and nice stuff about me and blog/ezine-style publish it. Thanks Douglas.

Wow. I was going through old blog entries looking for some unique insight (TM) I had, once upon a time, about SQL Server 2005 error handling in SQLCLR. Found a bunch of stuff, not sure how unique (or insightful) it was. But...

I realized I've been blogging over a year. First entry, July 1 2004 That's amazing, because I wasn't sure I understood the zen of blogging when I started. Not sure I do now either, but its been a whole year. I realize the entries have been slowing down as SQL Server 2005 has been coming closer to release. And as I start in earnest on the update of the "First Look" book. No technical content to this entry, just mindless reflection.

I just finished up our last gig for SQL Server 2005 Ascend last week in Paris (OK... in Les Ulis). It was my first class on the June CTP, and it seemed to go without incident. Students were enthusiastic and a good time appeared to be had by all. There are a two more "rescheduled for a later date" classes still to be done, but this is for all intents and purposes the end of Ascend phase 2 for us.

The only technical surprise of the week came when I added some rows to a table containing a UDT. After I did a "SELECT * FROM udttab" from SSMS, the rows are displayed in binary (back to the original behavior a la beta1), but when the UDT column contained a NULL value, I got the well-known "Assembly ... or one of its dependencies cannot be found". After making the assembly available I received, not the expected database NULL, but the binary value that corresponded to my UDT when it was NULL. Hmmm. It sounds like a few folks have been asking about this, and its been reported as a bug already. Interesting behavior. Using SQLCMD, the value NULL is displayed as you'd expect.

I just wanted to thank the folks from Ascend and thank the students as well. It's been a great time, I've enjoyed the traveling, the technical challange, and the nice reception that I received everywhere. Au revoir!

Now on to the release...keep on watching for more information.

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.

For a long time I've been griping that SSMS projects (SQL Server Script projects) use absolute pathnames rather than relative paths. I didn't think it was going to be fixed, I'd received a reply “this works as expected”. This week, as I was explaining to students why they had the put the SSMS projects in a specific directory as always, someone noticed that it's fixed...relative path names for files in SSMS projects. You must open and save each project to get this behavior, but...works now.

I've heard rumblings on various newsgroups that our book “A First Look at SQL Server 2005 for Developers” is getting a little long in the tooth in some topics. Features got postponed in the client area, some syntax changed, the managed providers were combined, and so forth. I've been reporting on changes via this blog and also have been trying to keep a running scorecard of changes on the book's website. This should be updated more for the next CTP or so; I've been waiting for official beta3.

In the meantime, we also signed to produce an updated version for SQL Server RTM. No, it won't be called “A Second Look...”, the title is set to be “A Developer's Guide to SQL Server 2005”. It won't be exactly at RTM because this time we'd like to wait to see what the final feature set looks like “when we open the box“. We're also going to add a chapter on SMO. Thanks for supporting the first one, and taking a look at the new SQL Server features with us.

On my latest visit to the SQL Service Broker Developer Spot, I noticed that my friend and coauthor Dan Sullivan is blogging now. And that two of Dan's first three posts have been accompanied by article-sized papers. Subscribed.

Here's something I've noticed you can do with SQL Server 2005 and Visual Studio 2005.

Take a set of XML documents with the same basic structure. Load one into Visual Studio 2005. Click on the XML menu, Create XML Schema. Make any refinements to the XML schema that is produced that you want, based on your knowledge of the document content. Note that the XML schema is created without the "targetNamespace" attribute. Save to disk.

Open an SSMS 2005 (that's SQL Server Management Studio) query window. Paste in your XML schema and use it to create an XML SCHEMA COLLECTION (of one XML schema). Now you can use the XML SCHEMA COLLECTION to strongly type an XML data type, in a SQL table column, variable, what-have-you.

You can also do XQuery (strongly typed) without using namespace declarations in XQuery preface or namespace prefixes in your query text.

This works because each XML SCHEMA COLLECTION is permitted to have a single "no namespace" schema. SQL Server wouldn't recognize the noNamespaceSchemaLocation (yes, schemas with no namespace are allowed by the XML Schema spec) even if you had it because it does not resolve schemas that don't "live in" SQL Server. And the VS-produced schema specifies attributeFormDefault="unqualified" and elementFormDefault="qualified" so the queries work.

That's for the truly lazy and after all, probably evil to true schema afficianados...so go back and put a namespace in your XML Schema, and use prefixes or default element namespace in XQuery preface.

With April CTP came the new combined managed provider replaces System.Data.SqlServer with a new improved, works in-process or out, System.Data.SqlClient. I just call it "the combined provider" now. People that didn't work on the betas will look at me funny when the product RTMs; "was there ever anything other than System.Data.SqlClient?". Why yes, Virginia...

With the new provider, some of the error handling problems passing SQLCLR errors back to T-SQL resurfaced. Some work-arounds didn't work-around the same way. Some people noticed this on the newsgroups. I reported a bug on first day, but didn't want to be too "complain-y" here. And noticed what work-arounds (AKA coding practices so that things work right) still work.

The most severe problem was that if you tried to catch a SQLCLR error with a dummy try-catch block in your CLR code, AND executed your SQLCLR code inside a T-SQL TRY-CATCH, you got:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any,
should be discarded.

Oh. That was in SSMS. In SQLCMD you got nothing. No error from the CATCH, no results.

Bug is reported as fixed today. Cool. Although it was the fourth CTP after beta2, this was first *ever* release of the combined provider. They'll iron it out. Can't wait to try it in next CTP. Then I'll write about it.

BTW, transactions are MUCH improved in the new combined provider. Not only can you use System.Transactions (try rolling back in that trigger with Transaction.Current.Rollback() now), but using BeginTransaction and nesting transactions in nested stored procedures works exactly like it does in T-SQL. Excellent.

One of the nice surprises in the April CTP is that SQL Server 2005 XML schema validation errors come with a location now. That's handy. Here's an example:

Msg 6926, Level 16, State 1, Line 1
XML Validation: Invalid simple type value: '1134'.
Location: /*:Invoice[1]/*:LineItems[1]/*:LineItem[1]/*:Sku[1]

Even better would be a line and column number that points out where the processor thinks this error in a value() method happened.

Msg 2389, Level 16, State 1, Line 4
XQuery [xmlinvoice.invoice.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

I know what was wrong with my query (i.e. what the message means) its become one of my favorite error message because it reminds me that this implementation does static type checking, but WHERE is there an xdt:untypedAtomic *? I guess there's no way to tell me where in a single XPath expression I went wrong, but if there are multiple lines or FLWOR expressions, give me a hint. 

The revised (I think) error at least tells me which "value()" function has a bad query. So if there are multiple value()s in the SQL query and one is wrong, I know which one.

Of course, if I got all my XQuery statements right, I wouldn't have this problem...

Realized that I haven't blogged in a while. I been ...uh...working on stuff and traveling a lot lately. Big surprise, right? This week I'll be on vacation. Traveling. Now I understand what the term “busman's holiday“ means.

Last weekend I had dinner with Rob Steward of DataDirect Technologies at his house. The ribs were great (thanks Rob) and while they were cooking I asked him if there would be support of rich schema metadata, base classes, and the neat stuff I wrote about I my ADO.NET 2.0 series of articles in DataDirect's line of ADO.NET data providers. He said that there would be, of course. They want to show up in that neat new Visual Studio 2005 connection dialog (that lists the ADO.NET data providers rather than OLE DB providers) too. And make a good showing in Server Explorer.

Then I asked about whether their classes derived from System.Data.ProviderBase's. This isn't the base classes in System.Data.Common (they support those), but the base classes to assist provider writers. Surprise! Those classes are now protected sealed in .NET 2.0 beta2. Oh. Guess not, then.

But they will support all the same things you'd have gotten from these classes for free: ConnectionStringBuilder, Connection Pooling (they always did support this), etc. So you'll be good to go.

Also, in looking through DbProviderFactory and friends, the SupportedClasses property is removed. That was the one that told you which classes (like Connection, Command, etc) the provider actually implemented. Gone from machine.config entries too. Wonder why...

Saw another Kent post on TechEd 2005. I'll be there too (mixed marketing slogan message with SQLPass). I'm doing a talk on “SQLCLR vs. T-SQL: Best Practices for Development in the Database”. Some folks on newgroups lately think that Microsoft may have mis-positioned this feature a bit. I disagree with this assessment. Come to the talk and see why.

Catching up on blog-reading. Kent Tegels wondered if I was speaking at SQLPass Community Summit. Yep, I am.

I'm doing a 2-day pre-con on (what else) What's new in SQL Server 2005 for developers. Also a tutorial session on XQuery for the “main conference”. I got “rained out” (to put it mildly) at last year's conference, maybe I'll have better luck this year.

See you there.

Speaking of SQL Profiler brought this to mind. The number one feature that brings a smile to every DBA's face:

GRANT ALTER TRACE TO [somedev]

No longer do you have to listen to developers ask “make me SA so I can run the trace”. Actually, brings a smile to devs too, no longer do they have to beg for it. Just brings the gate over to a more granular permission level. You still do have to ask for ALTER TRACE now...

I've always liked the graphic showplan in SQL Server query analyzer. The biggest hassle with it came when you wanted to send the plan to a friend. Or maybe MS support, but support is your friend too... right? You could send screenshots (which had the annoying habit of never displaying those hover-over stats) or go back to textual showplan.

SQL Server 2005 has XML showplan and I'd once gone as far as to attempt to write a transform to display things nicely. No need. You can do the following from SQL Server Management Studio.

1. Turn on the XML showplan
   -- show estimated plan
   SET SHOWPLAN_XML ON
   GO

   -- or execute statement and show real plan
   SET STATISTICS XML ON
   GO

2. This puts out your showplan as an XML data type column. Click the hyperlink to display the file.

3. Save the XML showplan file with the magic suffix .SQLPlan

4. Now when you double-click on the .SQLPlan file, it opens in SSMS as the interactive showplan with the hover-over stats.

Cool, eh? You can do a variation of this with SQL Profiler too. In fact its easier with SQL Profiler. Now you can mail the .SQLPlan to a friend. With full fidelity.

Since .NET 2.0 beta2 was released I've received a few inquiries about what happened to tracing SNAC (that's SQL Native Client). Looking at the adonetdiag.mof file, the SQLNCLI.1 entry (that's SNAC) was removed. I also got a solution/workaround from Glenn Johnson, who asked the question, then provided the answer faster than I could fly from Portland to San Jose and figure it out myself.

The workaround for this is to:
1. Start with adonetdiag.mof that was posted in my article. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/tracingdataaccess.asp
2. Edit this .mof file to replace to entries for System.Data.SNI.B1 with System.Data.SNI.1 from the new .mof file provided with beta2 . This file is in the Framework directory where .NET is installed.
3. Also change the file "ctrl.guid.adonet.beta1" from the article downloads to put in the new GUID for System.Data.SNI.1 (replace System.Data.SNI.Beta1). Run this scripts from the article as before.

What appears to have happened is that:
a. SQLNCLI.1 entry was removed from adonetdiag.mof (SNAC is not part of .NET, so this makes sense)
b. The GUID for SNI tracing has changed.

Happy tracing. Thanks Glenn.

When I did my first demonstration with the combined SqlClient and SqlServer provider in the April CTP version of SQL Server, I was a bit surprised. I wrote a simple stored procedure to run in the server, exactly the way I've always written it to run on the client (modulo sending results back to the client):

// error handling elided
SqlConnection conn = new SqlConnection("context connection=true");
SqlCommand cmd = new SqlCommand("select * from authors", conn);
conn.Open();
SqlContext.Pipe.ExecuteAndSend(cmd);
cmd.Dispose();
conn.Dispose();

I was surprised because this produced the error:

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host

The protected resources (only available with full trust) were: All
The demanded resources were:SharedState

After a little experimentation, I discovered they what was causing my problems was using Dispose(). Interestingly, I didn't technically need to use Dispose() (all .NET instances are available for garbage collection when the procedure invocation ends) and, in addition, using the C#/VB.NET "Using" contruct worked fine.

using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("select * from authors", conn))
{
  conn.Open();
  SqlContext.Pipe.ExecuteAndSend(cmd);
}

After consulting the Reflector, the two methods are different because the "using" feature calls IDisposable::Dispose on the SqlCommand/SqlConnection itself (after casting). The direct Dispose() call generates a call to ComponentModel.Dispose. Both SqlConnection and SqlCommand inherit (eventually) from System.ComponentModel.Component. That's where the shared state (and the exception) comes in.

Watch out for this. Using "using" (that's Using-End Using in VB.NET) is your best bet.

The last blog entry brings up the question of what I mean when I say something is "unsupported" in SQLCLR. Becuase I've said that J# is "unsupported". This doesn't mean that it won't ever work or that you couldn't actually get technical support for it, given enough time, energy, and money.

Technically, its possible to run almost *anything* in SQL Server if you catalog it as UNSAFE. But if your library doesn't follow SQL Server's rules for reliability and does something that could compromise the stability of the server, its appdomain could be unloaded as a last resort. Oh. Only a subset of the BCL are supported; to see this subset, create a Visual Studio Database/SQL Server project, and choose "Add Reference". Note that only a subset of the base class libraries appear. These are the ones that have been hardened according to the SQL Server reliability guidelines. Note that this contains the support libraries for VisualBasic.NET and Managed C++ (C# uses no language-specific support libraries), but not for J#. Because of the COM interop, they'd have to almost completely rewrite it to be compliant. That's what I mean by unsupported, I don't mean that it technically isn't accomplishable. Note also that there's no J# Database/SQL Server project in Visual Studio. That's a clue. And although *managed* C++ is a supported language, you have to compile with a special /safe switch, which enforces reliability limitations.

On the other hand, it's always been my contention that the most unsafe CLR code is safer than an extended stored procedure. Extended stored procedures are analogous to tweaking with the kernel of an operating system; there has to be extended test/maintanance plan because, unless you're the SQL Server team, you don't "own" the code you're running under. There's now notes in the BOL, under Programming Extended Stored Procedures, that read: "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work" and "Use CLR Integration instead.".

Looking at the Feb CTPNotes file again, there's another change that caught my eye. DROP ASSEMBLY has changed with respect to dependent assemblies. In past, you had to drop assemblies one at a time, so if assembly A called assembly B, you first dropped A then B. Now dropping A drops B automatically if B has the "is_visible" flag is false for B.

Although the looks like a convenience change at first, it actually solves a problem people run into when they go outside the mainstream of the supported BCL assemblies. If, for example, you used a library of your own that had a reference to System.Drawing (it's a library that draws an icon, but you don't intend to use this portion in SQL Server), this triggered a set of BCL references, some of them circular references. The only way to catalog something like this to SQL Server entailed cataloging as UNSAFE and (because of dependencies) also cataloging many unsupported base class libraries. But you couldn't drop it all because of the circular references.

Another example of this is using the J# language. Because J# support libraries use COM interop, the most trivial J# program (I just added two numbers together) must be cataloged as UNSAFE and results in 4 J# support libraries and 6 or so unsupported base class libraries being cataloged as dependencies. It's now possible to drop the J# assembly and have it drop all the associated assemblies at the same time.

The data access team (known as DataWorks) has started up a team blog. Some of the individual team members, like Angel Saenz-Badillos and Sushil Chordia, have been blogging for a while, this one has posts from all members.

One of the first posts was information about the upcoming SqlClient and SqlServer provider unification by Pablo Castro. The unified provider isn't in Feb CTP, but will be in an upcoming release soon. Pablo mentions that SqlConnection and other classes that can be shared between providers will be, but that SqlContext will still be retained for in-database- specific classes. By my calculations, that leaves (as in-database-specific):

SqlPipe - encapsulates a data stream back to the client
SqlTriggerContext - provides information in a SQLCLR trigger
WindowsIdentity - used for impersonation when accessing external resources (e.g. files) where you need a Windows identity

The only thing that I'll miss is the SqlDefinition/SqlExecutionContext classes. I'd been told those won't be in for this time; hopefully they're in the next major release.

They'll also be an upcoming in-depth article when the unified provider ships.

Thanks, Pablo! And Alyssa!

In this last entry on Service Broker enhancements I inadvertantly referred to the new poison message handling as poison conversation handling. Well, maybe it wasn't so inadvertant. So what's the difference between Service Broker's poison message handling and traditional poison message handling?

A poison message is a fact of life in transactional messaging. When a message is received from a queue, often some database action occurs as part of the same transaction. If the database action fails (say, insert of a row based on a field in the message that happens to be a duplicate key) the message is put back on the queue. Where it is received again... If the database condition that caused the first rollback to happen hasn't been resolved, the transaction will roll back again..and again..hence the term posion message.

Usually poison message handling shunts the message off to a dead letter queue. Where it can be safely ignored while the application goes on. Oh. The problem with this is: suppose the message you are ignoring is a million-dollar order. Or the executive's December check. The database transaction may have rolled back because overflow occurred on an internal variable (especially with extremely large dollar figures). I've personally seen the “executive December check overflows payroll counters“ one, back in the days of COBOL. They used fixed point decimal just like SQL/RDBMSs do today.

Since the primitive concept of Service Broker is the conversation, not the message, the message should not be ignored.
You could lose the million dollar order. Or produce cranky executives. The programmer who designed such an app (and didn't watch the dead letter queue) could be fired. There's something wrong with the conversation, it should be shut down.

The new "posion message handling" actually goes further than that. After 5 receives of the same message, Service Broker shuts down *the queues on both sides of the conversation*. You can recover from this by:
1. Either end the conversation or recieve the message without a rollback
2. And reenable the queues

You can still implement your own poison message handling, using any of the suggestions we described in our "First Look" book. You have 4 retries to do something on your own, before the automatic poison behavior kicks in.

I've been doing some experimenting with the new SQL Server Service Broker features in Feb CTP. You can read about them in the CTPNotes.doc file; I won't repeat the information here. The features are:

1. Improved Endpoint Security - authentication option NONE is not longer supported
2. DEFAULT message type and contract
3. Poison conversation handling

DEFAULT message type and contract came about due to feedback that the DDL to create a simple broker applications consisted of too many pieces. You needed to define MESSAGE TYPEs, CONTRACT, QUEUE, and SERVICE to define the simplest application. The first time this behavior change was described to me (it was some of my students among those who complained about the complexity after all), I thought they were going to loosen things up a bit to work without a contract. But broker uses contracts to enforce conversation integrity. In order to receive a message, a service has to be defined with a contract that's enforced when messages are being put on the queue. No contract, no user messages can be received. Hmmm...how would they do it?

You can now define a broker SERVICE by only defining QUEUE and SERVICE objects. However, the SERVICE must be defined to use a new built-in contract named [DEFAULT]. This contract specifies that a built-in MESSAGE TYPE, also called [DEFAULT], can be sent by either side (by ANY). When you issue a BEGIN CONVERSATION DIALOG without a contract, it uses the [DEFAULT] contract, not NO contract. When you SEND a message without an explicit MESSAGE TYPE it sends the [DEFAULT] message type.

So you're NOT using contract-less and message type-less conversations, you're using a specific contract and message type called [DEFAULT]. You just don't have to define them yourself.

There's a code example is the Feb CTPNotes.doc file (which is why you should always “read the readme file”), try it out for yourself and see.

When reading the CTPNotes file from the new Feb CTP build I stumbled across the fact that the XML schema that contains SQL data types (http://schemas.microsoft.com/sqlserver/2004/sqltypes) is now built-in to the server. Although this may not mean much to most people, it gave me the chance to try something that Dan Sullivan thought up for the first rev of our SQL Server 2005 class. It works now.

One of the enhancements to SELECT...FOR XML is ability to request that the XML it produces be prepended by an XML schema that describes it. A recent change allows you to choose the namespace for that schema. Dan's idea was to add the prepended schema to create a schema collection. After storing the FOR XML outside in an XML schema-valid column, you could make updates to the column that would be validated by the schema. You'd set this up like this:

declare @x xml
select @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('*[1]')
create xml schema collection authorsxsd
as @x
go

create table authorsxml (
 id int primary key identity, -- primary key required if XML index needed
 authors xml(authorsxsd))
go

declare @x xml(authorsxsd)
set @x = (select * from authors for xml auto, type, xmlschema('urn:authors')).query('/*[position()>1]')
insert authorsxml values(@x)

The XML Schema produced in the first step will now validate any information entered or updated in the table.

Why the Feb CTP change makes this work is FOR XML....XMLSCHEMA uses the SQL data types schema that's now built in. In previous betas, you could use this schema (error: not built in) or add the schema manually (error: it is built in [but it wasn't]). Thanks SQL Server 2005 XML folks, for this.

In the new Feb CTP release, how your implement a table-valued function in SQLCLR has been re-architected. This is in the readme (CTPNotes) This was done because implementing ISqlReader was quite complicated and overkill for most scenarios. Chapter 3 of our book "A First Look at SQL Server 2005 for developers" contains a very simple TVF (Bernoulli) implemented using ISqlReader. It contains over 400 lines of code. Many of the methods are stubbed-out because they are never used, but must exist to satify the interface definition. Using the new implementation this method would be less than 15 lines of code.

The new TVF implementation requires three pieces:
1. The SqlFunction attribute with the new field FillRowMethodName.
2. This attribute is applied to a method that returns either IEnumerable or IEnumerator.
3. FillRowMethodName points to a DIFFERENT method (in the same class) that has a special signature.

The methods in steps #2 and #3 have to be public static. Many of the collection classes in the BCL (e.g System.Array) implement IEnumerable or IEnumeration already, or you can write your own implementation.

The FillRowMethodName method has the following signature:

public static void FillIt(Object o, out int col1 , out int col2...)
   where the first arg is object returned by method in step #2
   where the varargs arguments (col1, col2....) are the columns that will be returned.

MoveNext is called on the underlying IEnumerator (in each case) until it returns false. Each time MoveNext returns a value, the FillRowMethod is called. This generates the rows. The number of columns is determined by the exact signature of the FillRowMethod. In this example, a 2-column table is returned.

Interestingly, the 2-nth arguments in your FillRowMethohd must be declared as "out" variables in C#. In my cursory testing, if they are declared as "ref", the method failed with the error: "argument n cannot be NULL" when the TVF implementation calls your FillRowMethod. This is interesting for VB.NET programmers because there is no direct variable qualifier keyword that corresponds to C#'s out. Or is there?

When .NET was first released a friend of mine, Jose Mojica, published "The C# & VB.NET Conversion Pocket Reference". And it names the following VB.NET equivalent for "out":

Imports System.Runtime.InteropServices
' signature of a FillRowMethod
Shared Sub FillIt(o as Object, <Out()> ByRef col1 as Integer, <Out()> ByRef col2 as Integer...)

Works great, Jose, my VB.NET TVF is working fine. If you're doing cross language work in .NET, I highly recommend this book.

Hi all. Not much blogging out of me lately. I've been on vacation and, in between, I've been teaching SQL Server 2005 at Microsoft Sydney to some of Australia and New Zealand's finest, including Russell Darroch, Greg Low, Chris Hewitt and Brent Challis. During the class Greg worked up his all-encompasing trigger to prevent cataloging objects in the master database. Probably partially inspired by seeing my do this a few times by mistake in demos. Some other folks in the class worked on *their* application specific features inspired by the encryption built-ins (e.g. encryptbykey), XQuery functionality, FOR XML PATH, and Service Broker. Thanks folks, I had a great time.

More people answer the question "what's the feature you most want to hear about?" asking about Service Broker each class. It's amazing to see Broker's "recognition curve" increase steadily since I started teaching SQL Server 2005 in...uh...August 2003. And to watch momentum building for this release in general.

By now, I'm sure you've heard that there's a new CTP (Commnunity Technology Preview) released this week. I've got the CTPNotes file. Be SURE to read this one carefully. There's a lot of new stuff in this build. I'll be home mid-week to start on it in earnest.

Right now I'm just back from watching the sun go down at Manly Beach. Morning was spent navigating the waves and getting myself sunburnt, then it started sprinkling rain around 3. Cleared up right after dinner. It's going to be hard to leave summer.. oh that's right.. its autumn here already.

More technical content shortly. Got some blog comment responses to catch up on too...later.