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.