I'm known for my vivid imagination when making up test/exposition examples. I have a cat named Sam. So, once upon a time, I wrote:

CREATE CREDENTIAL myuser
 WITH IDENTITY = 'mydomain\myuser', SECRET = 'some56*Z'
GO

CREATE LOGIN sam WITH PASSWORD = 'meowPw!a3'
GO

ALTER LOGIN sam WITH CREDENTIAL = myuser
GO

The DDL works. Now, I'd hoped to use this alternate credential so that Sam (a SQL Server login) could use the credential to use an external_access SQLCLR procedure that reads a file on the file system. This would require (since we have a nice NTFS file system with ACLs), that the SQLCLR procedure use the WindowsIdentity property on SqlPipe and do the impersonation. Works with Windows users, now Sam could do it too. I thought.

Just lately I found out that the alternate credential will not be useable with SQLCLR. WindowsIdentity will return null for Sam, regardless. This credential is useable with SQL Agent, something folks have always wanted for SQL Agent.

So no file system access for Sam, at least through SQLCLR and CREDENTIAL object. Unless the SQL Server service account has access to it and I don't do impersonation. He'll have to walk on the keyboard until he opens the file. As usual.

I've been wondering what happened to the QueryNotification dispatcher proc that's used by SqlDependency in ADO.NET (and in ASP.NET with SQL Server 2005). The one that I wrote about in one of my MSDN articles. Lately, although the dispatcher proc and assembly didn't show up in MSDB, the function kept working. I wondered why, how, what was happening.

This morning I installed the July CTP (which hasn't been reported to work with SQL Server 2005, so I didn't try) and found why. It's been "eased out".

There is now a static method called Start on SqlDependency (and a matching Stop method) that starts off ADO.NET's dependency listener. This creates a Service Broker queue and service (by default) and starts listening on it with a WAITFOR. So the functionality is no longer a passive listener (server pushes notification) but an active listener (strange as that sounds, means client listens and pulls notification). You pass a connection string into Start, but it looks like it will multiplex listeners on the same (1) connection.

Some nice repercussions of this (offhand, there may be more) are:
 No dependency of this feature on having SQLCLR enabled on server
 No possibility of DOS attacks on client
 No firewall issues since the listener uses one "normal" connection
 
More later...back to the revising/editing table for me.

Another fantastic book for your reading pleasure this month. “Customizing The Microsoft .NET Framework Common Language Runtime” by Steven Pratschner. In-depth coverage of .NET 2.0 hosting API. But why is this relevent?

Because most every bit of it is used by SQL Server 2005, as its hosting the .NET runtime. That's why. For example, as you read about “cocoon”, the host that will only load assemblies from zip files, ask: “who does this remind me of as a host”? Hmmm...

The book was written just recently, but as always there are minor implementation deltas. The one I noticed was that SQL Server 2005 does not create appdomains per-database/schema any more but per-database/assembly owner now. Small nit.

I did meet with Steven (he's an old friend from MTS days) when we were writing our “First Look” book, but apparently didn't take notes fast enough. His coverage is about 300 pages more than ours. And since then, he's moved on to Compact Framework, his CF blog is here.

Excellent book, dude...

They always get a bit of a jump on us WRT “current events” because of the timezone difference, especially if you live in the land of Pacific Time. I always come home on new year's evening and by before it's even dinner time, I've already missed new year in Sydney. We do get “Saturday Afternoon Footie“ here and I do watch it. On Friday night.

So....over the weekend Greg Low recorded a session with me for his podcast series at http://www.sqldownunder.com/. And I'd asked that he wait until Monday, until we'd announced the company move, to make it public. I woke up this morning and it was already there at SDU4FullShow.mp3 or SDU4FullShow.wma. We'll it's late afternoon, already. Hussled up the “Times they are a-changin'” blog entry really quickly and forgot to plug his/our podcast. Go git it.

I was a little distracted during the interview over UDT IsNull property and how its handled on the server. More on that later.

I really like the feature known as “All Permissions Grantable” in which any permission can be managed using the GRANT verb, because there are no “magic” logins or users anymore. The coolest thing is that all server roles are now defined in terms of the permissions they have; this chart is in the BOL. In it, sysadmin equates to CONTROL SERVER.

But I did notice when reading through BOL that many things are defined as “this can only be done by sysadmin”. But what if I'm not sysadmin and I do have CONTROL SERVER? Can I do those “sysadmin only“ things? Or the collolary: if I am granted CONTROL SERVER, do I become sysadmin (that is, show up in the system tables as sysadmin).

At least with the first three items I tried, I can perform the “sysadmin only” function but I'm not sysadmin. Good thing to remember when looking through an instance for high privilege users, to make sure some logins don't slip through the cracks. Here's example:

create login bob with password = 'StrongPW1'
go

grant control server to bob
go

-- login as bob or
execute as login='bob'
go
create database sample
go
-- this works for bob
alter database sample set trustworthy on
go

-- bob is not a member of sysadmin server role
-- BOL says he must be
sp_helpsrvrolemember 'sysadmin'
go

Just was looking through my reported problems last night to see what the resolutions might be. Looks like the two having to do with managed provider were fixed. But not yet, ie, the fix is not in June CTP according to the report.

Calling Connection.Dispose() and Command.Dispose() directly in-proc should now work, reported fixed.

6522 Error Anomaly when SQLCLR error occurs inside T-SQL TRY-CATCH reported fixed.

Look for things to start working better soon, I will be looking too. Thanks, folks.

Today I embark on a new venture, a new part of my career. I'm joining SQLskills as Director of Developer Skills. I've worked with SQL Server since version 4.21 off and on, taught classes about SQL Server at least part of the time since version 6.0, and, for the past three/four or so years, spent 95% or more of my time in this space. It's an exciting space, growing more exciting all the time, and that's the motivation behind the move. The folks I'll be working with here are another motivation.

I've heard of Kimberly Tripp "from afar" for quite a while. When I'd show up for Ascend gigs after she'd been to town before me, the folks that attended both classes would tell me stories about how "no one knows it better". All her speaker ratings at conferences we’ve both spoken at confirm this.  It will be great to be working with Kimberly at SQLskills.

We're planning some very interesting events for the future, so keep us on the radar. In the near future, I'll be working on these projects in addition to the next version of the "First Look" book (called SQL Server 2005 Developers Guide) and generally keeping busy.

Technical content is the point of this blog, as always. After almost a month of being "blog offline" (though the research never stopped), I've got some catching up to do on the blog. Stay tuned. If you found the blog here, pass it on.

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.