SQL Server SP2 was released last Monday. The links were posted on many blogs, so I won't repost any but the main one here. SP2 is a "major" service pack because there are a few "must have" features in addition to some rather useful improvements and quite a few bug fixes. The ones I'd consider must haves are:

1. Support for running SQL Server 2005 on the Windows Vista operating system. Vista will inform you of the SP2 requirement when you install SQL Server 2005. You install the "base" SQL Server 2005 first and apply SP2 immediately afterward, except with SQL Server 2005 Express. Express ships an SP2-specific product.
2. Support for business intelligence features of Office 2007 in Analysis Services. Unless you have SP2 installed, these features will be greyed-out in Office 2007.

There are some brand new features too like Sharepoint Server 3.0/SQL Server Reporting Services integration, the vardecimal storage format, and multi-schedule/multi-server Database Maintanance Plans.

But with that many changes, there are bound to be some incompatibilities. Be careful with these.
1. If you enable vardecimal storage on a database, you can't restore or attach an SP2-post vardecimal format database backup on an SP1 instance.
2. You can't use SP2 Analysis Services backups and data folders on pre-SP2 instances of Analysis Services
3. Analysis Services databases with linked measure groups can have problems on SP2. The most likely ones will be databases migrated from AS2000. See the SP2 Readme file for specifics.
4. You can't use Database Maintanance Plans from an SP2 instance will an SP2 instance, because the SP2 maintanance plans can have multiple schedules.

So be sure and be aware of these to have a successful upgrade.

My SSIS whitepaper, which was available in draft form on the SSIS wiki, is live on the Microsoft web site today. For anyone who is confused by all of the whitepaper announcements lately, here is a list of titles and direct links.

This one is about SSIS:

Connectivity and SQL Server 2005 Integration Services
http://download.microsoft.com/download/2/7/c/27cd7357-2649-4035-84af-e9c47df4329c/ConnectivitySSIS.doc

And these two (from last week) are about scaleout technologies and solutions. That is Service Broker, Scalable Shared Database, Peer-to-Peer Replication, Query Notifications, Distributed Partitioned Views, and Data Dependent Routing.

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/ImplementingScaleOut.doc

Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/InternalsTroubleshootingScaleOut.doc

Enjoy!

The companion whitepaper to my "Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005" whitepaper (see yesterday's post) is available. This whitepaper is called "Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005", with as much about internals as I could cram in 50 pages. Again, I don't have the "main" link; the direct link is here. Enjoy.

Thought I'd answer one last question or two from the last time I did the talk on SQL Server 2005 plan guides. If you're asking at this point "what is a plan guide", reference the SQL Server 2005 BOL or this blog entry.

Q. Can you use a plan guide on an encrypted procedure? I want to put a hint on encrypted vendor-supplied code.
A. No, per books online you cannot create an OBJECT plan guide on any object that is itself encrypted or references an encrypted object. You get error message 10512 if you try.

Q. Can you create a plan guide on the SQL statement (rather than an OBJECT plan guide) on a SQL statement that I know exists as part of an encrypted procedure and have the guide be used?
A. You can create a SQL-type plan guide on a statement in a procedure, but it won't be used in any case; you need to create an OBJECT-type plan guide for it to be usable inside a procedure. You can confirm its "non-use" by looking at the showplan output from an un-encrypted procedure with a SQL-type plan guide; you can't see an XML showplan for statements in an encrypted procedure.

Q. Can I have more than one plan guide? For example to ENABLE/DISABLE different guides at different times of the day? Can I have two guides enabled at the same time?
A. You can't create more than one plan guide on a single statement/module-or-object combination, whether they are DISABLEd or not. You get error message 10502 if you try. You'd have to DROP the first plan guide before you can create the second one.

Here's another blog posting to answer a question from over a month ago.

With separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA named after the user and assign that SCHEMA as the user's default schema. Question was, "does sp_dropuser do the right thing and drop the schema named after the user?"

Short answer is "yes, it does". Pretty easy to prove. Actually, sp_adduser can eventually call sp_grantdbaccess and sp_dropuser eventually can call sp_revokedbaccess, so...

create login bob with password = 'A^#DNEfdfhkWD#*iubdwc )000ks1'
go

use adventureworks
go

sp_grantdbaccess 'bob'
go

select * from sys.schemas  -- bob schema is there
go

sp_revokedbaccess 'bob'
go

select * from sys.schemas  -- bob schema is gone
go

The more intriguing thing is when you check this by using the OBJECT_DEFINITION system function:

-- creates schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_grantdbaccess'))

-- drops schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_revokedbaccess'))

Sure enough, there is nicely commented code in sp_revokedbaccess to drop a schema. BUT rather than using the DROP SCHEMA DDL statement, it looks like this:

EXEC %%Owner(Name = @name_in_db).DropSchema(OwnerType = 1)

Hmmm....

It's been over a month since I've blogged, confirmed by the previous blog entry when "it's sunny in Barcelona". Thought I'd blog about a question that someone wrote to ask me a few weeks ago.

What are the limitations/requirements for using a SQLCLR function in an indexed view (aka materialized view)?

Some of the requirements came from the books online, but can be verified with a simple sample, but there were a few surprises.

1. The VIEW must be created WITH SCHEMABINDING. This is a "normal" requirement of an indexed view, but usually when creating VIEWs WITH SCHEMABINDING that reference UDFs, the UDF must be defined WITH SCHEMABINDING as well. SQLCLR UDFs can't be explicitly be defined WITH SCHEMABINDING, but they can be used in views defined as WITH SCHEMABINDING.
2. The VIEW can't use a SQLCLR derived column as part of the index key unless the derived column is declared as PERSISTED in the base table.
3. The SQLCLR function must be declared Deterministic and Precise, and do no data access using SqlCommand, etc. DataAccess = None, SystemDataAccess = None is the default in SQLCLR functions anyway. And no external access is allowed.
4. An indexed view cannot contain a SQLCLR user-defined aggregate (UDA) function.

To demonstrate, I wrote two functions AddOne in SQLCLR, TSQLAddOne in TSQL. You can use AddOne in an indexed view. You can use AddOne as a key column in an indexed view if its defined as persisted in the base table. Note that the TSQLAddOne can be used as an index only if its defined WITH SCHEMABINDING.

This is fairly similar to the restrictions for TSQL functions and indexed views, except that you can access data in TSQL function and use it as the key, only if the TSQL function is defined with schemabinding. SQLCLR functions can't be defined with schemabinding, because there is no way to figure out which tables, etc, it's accessing. So because you can declare a TSQL function with schemabinding, you can probably make it part of the key if you access data. But, as when using a SQLCLR function in a VIEW WITH SCHEMABINDING, this shouldn't be an issue when your SQLCLR function does no data access. Hmmm...

Check out "table3 and view3". It uses SQLCLR function in a persisted computed column, and then as the KEY in an indexed view derived from the table.

Last week at TechEd I was showing off Query Notifications. When I showed using the preprovisioned queue (overloads on SqlDependency.Start and SqlDependency constructor), a delegate asked about using the same queue with more than one subscriber. He repeated the question when I showed the low-level SqlNotificationRequest.

I did some tests over the weekend and the answer is no. You need a separate queue for each subscriber, otherwise the SqlDependency listener gets "confused". If you use the dynamically created queues like most people do (SqlDependency.Start with only a connection string), you get a new queue and service for each subscriber. So you won't notice this. I thought I'd have better luck with the SqlNotificationRequest because I'm reading the queue myself, but without going to the system tables (and perhaps introducing race conditions) its not possible there either.

Queue are cheap to create, so there is really no reason not to create a separate pre-provisioned queue and service for each notification subscriber. But, at least for the time being, its a requirement.

I've been doing my talk on try-catch in T-SQL for a while and whining about not being able to "rethrow" (via RAISERROR) a system error. Even went as far as writing a RegEx decoder so that I can copy the error into a user error message text and parse the text back on the client. Two weeks ago in a class, and again this week at TechEd, someone suggested the simple, relatively low-tech solution; "Just add 50000". RAISERROR to rethrow and subtract 50000 at the client. Unless you have already defined user errors in the range that can't be rethrown + 50000 (up to 13000), this should work fine. Thanks for the suggestion.

Sorry, I know that slogan has been used already. Thanks to everyone who showed up for the SMO/Powershell chalk talk at TechEd Barcelona today. Here, as promised, are the demos. Thanks especially to Jeffrey Snover, the "dad" of Powershell for showing up and showing me some neat shortcuts... as I typed. Many of the scripts were written "on the fly" and really need better argument validation and error checking, but they seem to do the job. Some of these come from the SMO chapter in Dan Sullivan and my "A Developer's Guide to SQL Server 2005 " book. Enjoy!

I'm in Barcelona in TechEd and tomorrow I'm going to be doing a chalk talk on SQL Server Management Objects (SMO). Although I'd usually done my SMO coding in C#, I decided (based on my co-author Dan Sullivan's blog post on the subject) to try out using Powershell along with SMO. So, although when most folks think of SQL "scripts" being lines of code written in T-SQL, with SMO and Powershell the term "SQL scripts" takes on a somewhat different meaning. If you're at TechEd, stop by and we'll write some new-fangled "SQL scripts". Although some things that SMO does actually generate registry changes, the "real" T-SQL equivalents for most things SMO are still available using the SMO scripter.

When I discuss separation of users and schemas in SQL Server 2005, I usually think of ownership chains (that follow the object owner which is always the schema owner unless you change it) or object resolution (which now goes: special name, then default_schema/procedure schema, then DBO). Another item that now follows schemas rather than users is query plan reuse with 1-part object names. Create two users:

-- logins already created
CREATE USER bob FOR LOGIN bob
CREATE USER mary for LOGIN mary
GRANT SELECT ON authors TO bob, mary

The query plan for the following query (executed by bob or mary)

USE pubs
GO
SELECT * FROM authors -- note the 1-part object name

would not normally be reused for both bob and mary. In SQL Server 2005, it can be reused if bob and mary have the same default schema. BTW, this is adhoc SQL, rules differ in procedural objects like stored procs. To see the reason for the query reuse, you can obtain the plan handle (reference this blog entry), but leave out querying the plan itself because you don't need it here) and pass it in to the sys.dm_exec_plan_attributes dynamic management function. This function shows the attributes of the plan and includes a second column, is_cache_key. If the cache_keys match, the plan is reused.

One of the rows produced by sys.dm_exec_plan_attributes is named user_id. Interestingly, for the query plan for bob or mary against SELECT * FROM authors, the user_id is 1. DBO. Hmmm... User_id is a misleading name for this column. It's not the user_id of the user who executed the query (and caused the plan to be created) but that user's DEFAULT_SCHEMA's owner's user_id. Looking at the CREATE USER DDL statements above, because I didn't specify a DEFAULT_SCHEMA, both users' DEFAULT_SCHEMA is DBO. So, for adhoc SQL, all other things being equal, the plan will be resued for two users as long as they have the same DEFAULT_SCHEMA. The rules for query plan reuse follow the rules for object resolution, which makes sense, once you think about it.

Of course, now that you know this, ALWAYS use 2-part names. User_id with 2-part names' plans has a special value of -2 which ensures the plan is resued regardless of DEFAULT_SCHEMA of the user who executes it.

When installing previous versions of SQL Server, I'd always keep a list of the exact privileges that a SQL Server service account would need. I'd make up a new user and give them exactly these rights. The SQL Server installation program would grant the appropriate permissions during the install, and I'd be set. Principle of least privilege, run SQL Server and associated as the minimum privilege account needed. If I thought about changing the service account, I'd call up knowledge base article Q283811 and sigh at the list of work items. And make sure that I used Enterprise Manager, as the article recommends.

Things are a lot simpler in SQL Server 2005. When you install SQL Server, the process creates Windows groups with exactly the minimum privlege that you need for the appropriate services. The user you specify during the install (which should be a "normal", low privilege user and NOT administrator or LocalSystem, is plunked into the appropriate group. There is a Windows Group for each SQL Server-related service, all nicely decked out with least privileges (file system DACLs, security policies, registry key access, etc). NO more privilege lists to keep around, although they are doc'd in BOL should you need to refer to them.

If you should need to change the service account, things are even better. Just put your new Service Account user in the appropriate Windows groups, and voila, no long list of tasks. Very cool. Or use Configuration Manager.

Twas' not all sweetness and light, however. an FAQ during the early days was "why does SQL Server setup "clutter" my directory with groups?" That's why. First time you need to change service accounts, you'll appreciate the "clutter".

I've noticed that, as more companies want to run SQL Server applications 24x7x365 interest of clustering has increased dramatically. One of the most asked-for papers about SQL Server 2005 is an update to the clustering whitepaper. Well, its available NOW; an over-100 page treatise by Allan Hirt and Dave Whitney. It covers not only clustering SQL Server 2005 but also Analysis Services 2005. A MUST read. Look for "Failover Clustering for Microsoft SQL Server 2005 and SQL Server 2005 Analysis Services" at Microsoft download site.

Even now, although SQL Server 2005 has been for about 3/4 of a year, when I teach people about the new developer features, I'm still asked about the zen of the new features. Do they follow a common theme? Is there a usage pattern that emcompasses all of the things like Service Broker, SQLCLR, XML data type and query, Query Notifications, Event Notifications, HTTP Endpoints?

Here's an academic paper by Dave Campbell that explains at least one usage: towards a Service-Oriented, Active database. This paper, released in 2005, ties together most of it.

I've been just pointing out the paper when folks ask me the "why" question for some time now. Making a database Service and asynchrony-capable is one of the ways to build a scale-out solution. I'll never have to ask "what happened to my application state when [mid-tier host of choice here] was temporarily offline" again.

There's an interesting article that caught my attention in this month's (July 2006) MSDN magazine by John Mollman about building the MSDN aggregation system. The system uses SQL Server 2005 Service Broker for reliable messaging, activation procedural written in T-SQL, SQLCLR for interactions with the world outside the database and the XML data type and schema support built in to SQL Server.

I found the use of SQL Server Service Broker because of its scalability potential, the data dependent routing implementation, and the plug-in object and interface based provider model most interesting. Check it out.

SQL Server 2005 implements quite a few technology pieces to help database developers and administrators acheive scale-out while acheiving the appearance of (or actually accomplishing) application or server federation. The information is scattered throughout the books online, but you can read a nice whitepaper by Roger Wolter on how to choose among the features here. I'll be doing some events with SQLskills about scale-out applications in Chicago and NYC, and partners in Reading UK, and I didn't want to miss anything. One technology piece that was a big hit in SQL Server 2000 stayed mostly the same in SQL Server 2005, but I wanted to qualify the word "mostly". That feature is distributed partitioned views and distributed queries.

Distributed queries and distributed partitioned views use OLE DB as a communication mechanism. OLE DB access is built-into the query engine, data can be retrieved by the same basic mechanism from the SQL Server storage engine or from a "remote storage engine". When you're talking to another SQL Server, the OLE DB provider used in SQL Server 2000 is SQLOLEDB. In SQL Server 2005 the OLE DB provider changed to SQLNCLI (or SQL Native Client). There are some subtle differences (remote errors, ability to run out of process) between providers, and, if you upgraded to SQL Server 2005, all your Linked Servers now use SQLNCLI.

SQL Server 2005 does, however provide nice tracing information in SQLProfiler regarding which OLE DB calls the engine makes to the linked server. Because OLE DB is interface based, you can even see the query interface calls, if you want to go to that level of abstraction. You can trace these on either or both sides of a SQL Server linked server; outgoing trace works regardless of the destination provider. You do have to grok the OLE DB interfaces to understand the events, but it's like the query plan, there are various levels of understanding.

Distributed partitioned views remote part of the query tree to the remote database. In SQL Server 2000 the portions of the query would never execute in parallel. In SQL Server 2005, the startup commands can execute in parallel and accomodations are made for caching the remote connections and using overlapped I/O to optimize things. And SQL Server's OLE DB providers have always been able to remote "statistics rowsets" to the other side to help the query processor out.

For distributed query, there's a version of the SQL EXECUTE statement that implements passthrough queries using an AT keyword. You could execute passthrough queries in previous versions by using OPENQUERY, but this required the results to be treated as a rowset (SELECT * FROM OPENQUERY(...)). I've gotten EXECUTE AT to support remote queries that use the "MAX" data types and CLR data types (the assembly must be catalogued to both remote and local database), but the XML data type isn't yet supported for remote queries even though the BOL says so. So cast it to a MAX type and cast back when you get it back on the local server.

That's enhancing one piece of the puzzle, real federated servers. Although this won a TPC benchmark (in the SQL Server 2000 release timeframe, with 32 federated servers) there's LOTS more. As always, the devil is in the details, that's where I usually come in.

Started my part of the festivities today with a chalktalk mentioning SQL Server DDL triggers and Event Notifications. But explaining Event Notifications meant explaining Service Broker services, queues, and contracts. And the fact that both Event Notifications and DDL Triggers use a XML format. That you can change to a rowset format using the XML.nodes method. Which meant talking about XML.nodes. Showed a DDL trigger for CREATE ASSEMBLY that had to be written in SQLCLR.

Went on from there to setup a cross-database event notification, which (the way that I did it) entailed using the TRUSTWORTHY database property. And what TRUSTWORTHY means (I'll do a blog entry on that in future). And because cross-database service broker uses session keys (BEGIN DIALOG CONVERSATION uses encryption by default), each database must have a database master key. Explained what database master keys are.

Finally, I cross-instance event notification. Needed to explain what ENDPOINTs were and the ENDPOINT abstraction. And explain service broker ROUTEs. A good portion of the new "infrastructure type features" (I don't think they're only for developers any more) in one talk. That's some (fairly useful, I think) scope creep.

On to the WMI provider for events, and how this works with SQLAgent. Watching it use WAITFOR (a queue) and RECEIVE. Uh, oh...

I've been looking at the DLINQ syntax a bit more (I'm participating in a BOF at TechEd next week), and its always struck me how, at its most basic, its reminiscent of XQuery FWOR. Yes I did leave out the L (Let) on purpose. I guess its the relative placement of the SELECT part of the query:

FWOR:

for $x in /customers/customer/address
where $x/city = "Portland"
order by $x/zipcode
return $x

DLINQ:

from a in db.Address
where a.City == "Portland"
orderby a.Zipcode
select a

I've done the FLWOR/SQL comparison quite a bit. Just replace "for" by "from" and "return" by "select". The ordering of the keywords in DLINQ just makes it a bit more obvious. Or maybe its the "in" keyword in the from/for clause. The superficial similarity kind of ends there, however.

Been on the road lots lately, not much blogging, I'm afraid. I'll be making up for that...

I "found" a new command line utility today in SQL Server 2005. Maybe everyone but me already knew it was there. Reading up on replication, I came across mention of the "tablediff" tool. You feed it and source and destination table and it will spit out a report of data differences (the table definitions must be identical), on a row level or column level. It's meant to allow you to fix problems if you perform an operation that gets databases involved in transactional replication get out of sync. But it can be used outside of replication just as well. It lives in the ...Microsoft SQL Server\90\com directory.

Nice diagnostic and synchronization tool. My favorite option is -f which generates TSQL statements to get the tables back in sync.

This one ought to be titled "Your error message is such a tease".

I've always liked the fact that SQL Server 2005 error messages are verbose (modulo security considerations for login error for example). I always point out at least one during classes, start to read the message...and about 50 words or so later, tell students to "check out the entire text of the message offline". Excellent! Of course message boxes in applications that display these verbose errors must be made bigger or scrollbars added. Such is life.

So, in SP1, my perfectly working WMI alerts stopped working. Looking at the job history, I found the following:

Unable to start execution of step 1 (reason: The job step contains one or more tokens. For SQL Server 2005 Service Pack 1 or later, all job steps with tokens must be updated with a macro before the job can run.). The step failed.

Great. Except what's the magic macro??? What a tease, everything I need to fix things except for the most vital piece of info. Downloaded the April Books Online update, which had a TON of great new and revised content. But nothing about this problem (well they can't catch everything right away). Then I remembered RTF-readme file. Went to the latest "KB 913090 - A List of bugs that have been been fixed" and found IT had been updated with a reference to "KB 915845 - SQL Server Agent jobs fail when the jobs contain job steps that use tokens after you install SQL Server 2005 Service Pack 1". Yep that's the one!! The magic macros are:

$(ESCAPE_SQUOTE(token))
$(ESCAPE_DQUOTE(token))
$(ESCAPE_RBRACKET(token))
$(ESCAPE_NONE(token))

There's even a nice script that catalogs a procedure to update your existing jobs. What could be easier? Except, attempting to catalog the procedure yields "A fatal scripting error occurred. Incorrect syntax occured while parsing '. Huh? That's too sparse of an error. What now, the saga continues....

Turns out this error occurred because I had SSMS working in SQLCMD mode by default (see "What's fixed in SP1 blog entry") and because there was a token $anything in the code, it was being processed by SQLCMD. Ohhhh. Turned off SQLCMD mode and it worked fine. Jobs fixed and working. The jobstep SQL now looks like this:

INSERT INTO DeadlockEvents(AlertTime, DeadlockGraph)
  VALUES (getdate(), N'$(ESCAPE_NONE(WMI(TextData)))')

So that's "the whole story". Hope it saves you some grief. That said, I REALLY like the WMI and Event Notification idea a lot. It's like having the database "tell me" when there's a problem that I want to know about. In real-time or queued and saved as I sleep for resolution "at my leisure". My leisure...ha, how about "the next morning"? I'll take that.

Lately I've been working on using WMI events in conjunction with SQLAgent alerts and jobs to notify operators, queue up notifications for the DBA's resolution offline, and other uses. Setting up the alert and the job is fairly straightforward, where I stumbled at first is referencing WMI variables (and even knowing what variables were available) in the job that processes the WMI event. The varaibles are doc'd in BOL in conjunction with event notifications, which is really what the WMI provider for Events uses underneath.

You use a WQL query that references the event to set up the WMI Alert, using "SELECT * FROM <my_event>" syntax (BOL example is SELECT * FROM DEADLOCK_EVENTS). And reference in job by using a WMI variable, like so:

-- BOL example
INSERT INTO DeadlockEvents(AlertTime, DeadlockGraph)
  VALUES (getdate(), N''$(WMI(TextData))''')

So, when you set all this up, why doesn't it work? It always produces "TextData not available". Hmmm...The "magic switch" to success is found under SQL Server Agent "Properties" menu entry under SSMS. You need to turn on (in the Alert System tab, at the bottom of the page) "Replace tokens for all job responses to alerts". They aren't WMI variables, they are WMI tokens. Success! Took me a while to find that one...

But, in SP1 it "breaks" again. Sigh. More about this in next blog entry.

You've likely heard by this time that Service Pack 1 for SQL Server 2005 was released earlier today/yesterday depending on what timezone you're in. Rather than go into how to get it, there's two KB articles that are really useful, KB 913090 - A List of bugs that have been been fixed and KB 916940 - A list of the new features and improvements. I noticed a couple tiny ones in SSMS that weren't listed in either document, so I'd bet there are likely more unlisted improvements and fixes.

Displaying the word "NULL" for a NULL instance of a user-defined type in "Results to Grid" mode works now, without having to install the UDT in the GAC or SSMS directory. This is even improved over the fix in SP1 CTP, where it worked in the UDT was installed. Prior to the SP, you could display it in "Results to Text" mode, but Grid mode threw an exception. Fixed now.

If you choose the "By default open new queries in SQLCMD mode" in Tools/Options Query Execution, the setting "sticks" now when you close SSMS and reopen. I know at least one person that will be happy with that.

That's all for now.

I've been intruiged for a while with the idea of using SQL Server Express edition as a gateway from HTTP messaging to Service Broker messaging. That is, receiving web service messages through HTTP endpoints and turning them into Service Broker messages to take advantage of the robustness of the Service Broker protocol. Went to set one of these up today...but...HTTP endpoints are not supported on Express edition. Or on Workgroup edition for that matter, you must have Standard edition to define and use them. So you must have at least Standard edition to setup such a gateway.

I don't know anyone that likes to wait for an application to respond. This is especially a concern with web applications; you start to wonder if the web infrastructure has a problem. Should you hit by "Order" button again?

Because waiting is subjective it's nice to be able to know, when a user calls, how long they have actually been waiting. Or at least how long the database has been waiting. SQL Server 2005 will tell you, at least when locking is involved.

There is a configuration parameter "blocked process threshold" that controls if and when to send notifications whenever a process is blocked for N seconds. You set this through sp_configure; its an advanced option so show advanced options must be on too. It's set to 0 (never notify) by default. You can catch these notifications through WMI/SQLAgent alerts or the new event notifications. Very cool.

So now when a user reports "I've been waiting for this database query for 10 minutes" but the query response is immediate when you check it (the "works for me" answer that users hate), you can see (after the fact) if its lock contention that caused the waiting. “Well, you weren't waiting for locks” might be a better answer. Maybe. 

Got the word today. Officially supported. Error in readme file. Reference this blog entry.  Good to hear this.

I'm sure there's something about this in BOL somewhere, but this cost me a few more than a few ticks yesterday. Hoping to save you a few ticks. This one deals with semantic models used by Report Builder (part of SSRS 2005).

Suppose you have a semantic model deployed on server A that you want to move to server B. You don't have the source code. Perhaps it "arrived" pre-deployed on a VPC, as an example. You need the .smdl file. But it doesn't exist standalone, its been deployed to the Report Server. Or at least I didn't find it. I could probably write a program to extract it using the admin web service interface. But I'm too lazy.

So, eventually, I brought up the web interface, selected the model, then, under "Model Definition" chose "Edit". A nice dialog box asked me where I wanted to put the .smdl file. Great, just what I wanted.

BTW, bringing up SSMS, and browsing to the model in Object Browser, the model had a nice dropdown menu item, "Script As...". Problem was, it didn't seem to script anything. I tried all three script options, nothing happened.

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

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

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

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

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

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

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

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

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

-- standalone trigger

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

And I'm only the fiftieth person to blog about it. That's SQL Server 2005 and Visual Studio 2005/.NET 2.0, of course. I considered not blogging and saving network bandwidth 'cause everybody already knows, but...naaaahh.

CONGRATULATIONS folks! Must be quite a party going on in Redmond. The MOST exciting set of products to come out in quite some time. One man's humble opinion, as always.

Mary always accuses me of “living entirely in the future”...hmmm...what to do now that the future is here. You've already seen my wish list for V-next. Maybe I do live in future, she's right. As always.

The last five blog entries were a lot of "off-the-top-of-my-head" list writing. Mostly remembering what people had said or the "I wish there were" comments. Just so no one misunderstands the tone....

The SQL Server 2005 version of SQL Server has more new features by order of magnitude (at least from a developer perspective, though I think a rewritten SSIS and the UDM concept qualify too) than any other delta of SQL Server. It took a herculean effort by a large number of people. We know who you are. THANKS.

There had to be some things postponed, especially with radical features, to see how relatively popular the feature would be. As an example, I've yet to see the "killer user-defined aggregate" that we couldn't ever have lived without. If you have one, let me know. I AM NOT complaining that the product doesn't have every one of these features now (in most cases), just enumerating some constructive suggestions for futures. That were solicited.

I've also been told that I don't like to throw away "abusable" features, features that are powerful but can cause problems if used incorrectly. Yep. User (programmer and DBA) education, is the solution to this. Why should users who know when to use a feature (and when not to) be penalized because some folks will potentially abuse a feature?

For the long list of things that I liked about SQL Server 2005 see the early blog entry here. I actually wrote a book (something I said I'd never do again), am working on an update to the book, and changed the company I worked for, to spend more time with this product and people who are associated with it. That's my testimonial.

Use the lists for what you will. Cheers.

OK, SQLCLR wish list comes last. Mostly because I'm tired of being accused of being a SQLCLR bigot; I think it was because our book covered those topics first. Oh well.

Default parameter values in SQLCLR procedures when called by T-SQL. You obviously can't/shouldn't support these when called from-CLR-to-CLR. Right now, they work, but the metadata doesn't recognize them. Nor does the SMO scripter.

Better exception/error handling in SQLCLR when called by T-SQL. See this blog for my 6522-saga of entries.

.NET 2.0 nullable type support for parameters. People always ask.

Overloaded methods support. As an alternative, let me define each overload with different T-SQL names. Or just define one of them.

Better integration with System.Transactions for transaction nesting.

Bring back SqlExecutionContext and SqlDefinition.

UDTs and UDAggs of greater than 8000 characters. This is a big one.

Support of IComparible (maybe in UNSAFE mode, I know why its off currently), operator overloads, inheritence, and multiple sort orders for UDTs.

More framework BCLs that comply with the "SAFE spec".

UDAggs with more than one parameter, and that support .NET generics. The more than one parameter is more important of the two.

Either support UDAggs' IsInvariantToOrder=false or remove the property from the attribute.

SSMS should display a NULL UDT value like SQLCMD does.

That's all the wishes for now. Likely some that I left out. Cheers.

Transact-SQL has been around for a lot longer, the list will be a bit smaller. But the T-SQL enhancements in 2005 were numerous but, of course, left some folks wanting more. As always.

More error handling improvements - TRY-CATCH is VERY nice. For next time, how about FINALLY and RETHROW? The ability to rethrow system errors, rather than having to convert them to user-defined errors would be nice too. The error levels and semantics could be made more consistent too, as Erland Sommarskog's dissertation on the subject points out. A personal request would be to implement some of the ANSI SQL error handling constructs, or subtly change the syntax to come closer. But no one doesn't like TRY-CATCH over @@ERROR, that I'm aware of.

An option for catalog time object resolution for procedural code. It would make sysdepends more dependable too.

Subselect support for the IN clause of PIVOT.

BEFORE TRIGGERs. I didn't ever use them much when I worked in [database that had them] but it would at least help conversion.

UPSERT (insert or update in a single statement).

When using the new OUTPUT clause, a way to get row values before or after triggers fire. You only get "before" now.

ANSI DOMAIN support.

Support for SQL/MED syntax (well, it IS a wish list, right?)

Explicit windowing functions. The new Windowing has inline only.

Support for "caching part of a rowset in memory". I'm unsure what the formal name for this is, all I keep hearing is that database [some popular database] has it.

Less restrictions on what can SQL be in INDEXED VIEWs. There is a set of restrictions that are similar for INDEXED VIEWs, Query Notifications (obvious why) and Recursive CTEs. Probably an engine optimization. The one that folks seems to ask for most is UNION support in these.

Ability to debug T-SQL procedural code in SSMS.

Using FOR XML with hierarhical CTEs. Didn't know where to put this, here or part 1.

SQLCLR is next, while I'm on a roll.

This one is about SQL Server Service Broker. It's not necessarily a cool or sexy feature, but may be the most revolutionary feature in there. The problem with Service Broker is that most people don't get it. They think YAQS (yet another queuing system). It's more than that. It's already used for 3 features inside the server (query notifications, event notifications, and mailer), there could easily be more. A SQL Agent replacement comes to mind. But I digress, this is supposed to be a wish list. If you still don't get it, think harder. Or come talk to me. Or listen. Here's wishes.

SSMS support. Currently only does display functionality. A "route, security, and message testing" piece and configuration would be nice too.

Monolog support. So I won't have to keep explaining why it's "BEGIN DIALOG" but "END CONVERSATION".

Broker support on SQL Server Mobile. For obvious reasons. It could be a discreet subset.

Object model that works for internal activation, external activation, and clients. There's a nice one in engine samples; it should be officially supported. It would be cool if it was design type similar to JMS. Plus extensions.

Supported auto-deployment. For each side of a service. Again, its been done, but not officially supported.

Same (official support) for external activator.

WCF channel. For transactional messages, broker doesn't use DTC. Makes it faster. Faster still when messaging combined with database operations.

HTTP channel. Although that can be built by third parties too.

WCF and WWF assemblies should be able to be marked as "SQL Server safe". Or they'll have to run in the external activator.

Hmmm...many/most of these contained the word "support" somewhere.

More wishes to come.

OK, because it was Michael that asked originally, XML/XQuery wishes for SQL Server 200x. In no particular order.

Full compliance with "standard" W3C XQuery. If the standards committee finishes before SQL Server 200x ships. Else it will become a "standard" no one follows. Hmm...like SQL. Reason for this is twofold.
1. Folks who get a "generic" XQuery book at the store are now totally confused. Many give up after the third or fourth "not supported" message.
2. RCG (recent college grads) who may learn "generic" XQuery in college. There's a reason why C# looks somewhat like another popular programming language. Because it makes it easier to switch environments. And code.

XQuery editor. That does schema or schemaless. Strong typing. Files or the SQL table row of your choice. They had a good start in the beta, but had to finish VS XML tools.

Client side XQuery. How can I write code that might run on server or client, when it can't run on the client? XLINQ is nice, but it's not a substitute. Should be compatible with server, modulo input and ouput.

Output in encodings other than UTF-16. A CONVERT with extra parm, like DATETIME has.

Full support for XSD schema productions. Yes, I know that its techinically outrageous. But its another source of confusion/frustration. Get an industry standard schema (even Office 2003 schemas). Can't store it without changing "lax" to "skip". But then its not the same schema if I change it.

Standard grammer for composition and decomposition. Now, I'm a big fan of SELECT...FOR XML. It's easier and more powerful than the alternative. But let's talk conversions again. Just layer on XmlElement(), XmlAttribute, etc. Kind of like CAST vs CONVERT in TSQL, they implement both.

A XML.validate method.

A more powerful modify method. But again, this could wait (but not long) for the W3C. Most people don't know that modify IS based on a standard proposal. But XDR and XML Patterns were proposals too. So maybe implement "update" (in addition to insert, replace value of, and delete) for next time. Bet SQL Server is finished before W3C. Multiple node inserts/updates/replace value ofs.

XQuery collection() semantics over rows in a table. That could be considered a collection.

XQuery 1.0 and XPath 2.0 data model (or ANSI SQL 200x data model) support. They're relatively close now.

FOR XML works with UDTs.

Ability to specify DOCUMENT on well-formed data. Like it works on schema-valid. This is a nit, I can do my own constraint. Arguably.

Finally, Node-level locking or Node-level versioning. Yes, I know. Technically outrageous. But it IS a WISH list after all.

It's just a few weeks until the launch of SQL Server 2005. I'm been hiding under a rock since returning from Hong Kong. TechEd there was a great time, biggest attendence they've ever had. Security and SQL Server 2005 were the hot topics. Yes, even after PDC in September.

It's been over three years since I got my first look at SQL Server 2005, in September 2002. I knew then it was a major undertaking; kudos to everyone who worked on it so hard for so long. I've been teaching developer topics (its hard to make a hard distinction, for example, is snapshot isolation a developer or administrator topics?) for over 2 years, since the first class of 45-or-so Microsoft folks up in Redmond. Everyone I've presented features too has been really excited. Or pretended to be excited because I was.

They're already talking about the next version and Micheal Rys (of XQuery and XML data type fame) was asking what folks would like in the next version. Thought I'd take a shot at it, at least from the developer perspective. No better time...so here goes. I'll do multiple blog entires, starting with infrastructure. It's a big WISH list, I don't expect all of them. Or even most of them. Mostly, they are customer wishes, but some are mine too.

In this arena, we could use Row-level security. It was in beta 1 for a while, and folks always asked about it. Certain business sectors require it.

Next, true ANSI DATE and TIME data types. We had them too, for a while, but they were written using SQLCLR. Some folks didn't appreciate that, they were cut. They're needed to provide a conversion path from databases that use them. Like [main competitors names go here]. Frankly it didn't bother me as much as most *how* they were implemented just that we had them. I'm still looking for the promised source code so we could compile 'em and use 'em ourselves. Whatever happened to that? You could implement them yourself using SQLCLR, but a supported version would be better.

A hint/set of options so you could keep the default READ COMMITTED locking behavior, but use READ COMMITTED versioning (aka statement level snapshot) through a hint. Oh, and while we're at it, a separate “rollback database“ instead of using TEMPDB for old versions when using versioning.

Finally, some folks asked about DML event notifications (using Broker), FILESTREAM data type, higher capacity MAX data types, and there's a set of people who always ask about bitmapped indexes. You know who you are...

XML/XQuery wishes are next.

After writing about a lot of new security features that were added since we published our "First Look at SQL Server 2005 book" its nice to report on one that we had in there (at beta2), but never appeared in the product. Until now.

In the September CTP version on SQL Server 2005 (I think its probably the last CTP), you need special permissions to CREATE an ASSEMBLY with UNSAFE permission set. You must have either one of the following:

1. DBO has UNSAFE ASSEMBLY permission and database has TRUSTWORTHY property on.
or
2. ASSEMBLY is signed with an asymmetric key or cert that has a LOGIN with UNSAFE ASSEMBLY permission.

We'd written about the second choice. Here's one of the combinations that works:

1. Create a strong named key in c:\temp\assm.snk
2. Sign the assembly unsafe1.dll with this strong named key
3. Make a SQL Server LOGIN for the key.
4. Give LOGIN the appropriate permissions
5. Catalog the unsafe assembly

In code, it looks like this:

-- master key in master database
USE master
go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword1'
go

-- keyfile generated by VS or .NET command line utilities
CREATE ASYMMETRIC KEY assm FROM FILE='c:\temp\assm.snk'
go

CREATE LOGIN snk FROM ASYMMETRIC KEY assm
go

GRANT UNSAFE ASSEMBLY TO snk
GO

USE somedb
GO

CREATE ASSEMBLY unsafeassemblyex FROM 'c:\temp\unsafe1.dll'
  WITH permission_set = unsafe
GO

That's only one variation of it. You can also use the key stored in the assembly (CREATE ASYMMETRIC KEY FROM EXECUTABLE FILE=...) or an assembly already cataloged inside the database (CREATE ASYMMETRIC KEY FROM ASSEMBLY...). You can do the same thing with certificates.

So one of the SQLCLR security features we wrote about over a year ago has come to pass.

This one's been around for a while, but don't think we'd mentioned it in the book. In addition to Windows logins and SQL logins, you can have SQL logins mapped to a certificate or to an asymmetric key. Same with database users. One of the reasons you might want to do this is to implement Service Broker transport security. That is, create a certificate in master, then create a login mapped to the cert. Now use the cert in the CREATE ENDPOINT ... FOR SERVICE_BROKER.

To allow your business partners to authenticate with your endpoint (in their implementation of transport security) you BACKUP the cert to a file (the default is to BACKUP public key portion only) and then send the cert to your business partner. They install it in master to use to authenticate your endpoint.

There a variation of CREATE USER foo FROM CERTIFICATE that's caused a lot of discussion on one of the newsgroups lately. That is "CREATE USER ... WITHOUT LOGIN". This is used with Service Broker too, but with full dialog security, rather than transport security. This USER has only the permissions it needs (usually only SEND on the SERVICE/ or RECEIVE on the QUEUE in question). So this is used for the following scenario:

1. Create a user without a login
2. Create a cert owned by the user
3. Backup the cert (public key only) to a file, send to business partner
4. They create a user without login
5. They install your cert from a file
6. They GRANT the user the appropriate access
7. Your BEGIN DIALOG uses full dialog security (ENCRYPTION = ON is the default).

Folks on newsgroups have said it (USER WITHOUT LOGIN) is undoc'd, but I remembered seeing this before. Then I remembered where. It's in Neils' remote Service Broker examples here.

You've probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in "CREATE PROCEDURE foo WITH EXECUTE AS OWNER". But EXECUTE AS can also be used at a session level,like this:

EXECUTE AS USER='fred'
-- some T-SQL here
REVERT

This is meant to replace the SETUSER verb because you might want things executed as a specfic login as well as user. So there's also EXECUTE AS LOGIN='freds_login'. You can aribtrarily nest EXECUTE AS blocks and there's subject to the nice, granular, IMPERSONATE privilege. Originally, I'd heard there was going to be a "REVERT ALL", but there's security mischief that might happen with this, so it never happened.

BUT.
There are two variations of EXECUTE AS that are interesting, based on security considerations. First one is "EXECUTE AS USER='fred' WITH NO REVERT". Exactly what it sounds like.

Next one is EXECUTE AS WITH COOKIE. The idea is similar to what I described in sp_unsetapprole. You don't want just any ol' level of impersonation to do REVERT. So EXECUTE..WITH COOKIE gives you a cookie to use with REVERT.

One of the things folks would always ask during the Ascend program was "anything new for application roles? do they support connection pooling yet?". Well, it the most recent CTP (June, July?) there is.

You can unset application roles now in addition to setting them. To unset you need to create a cookie, using an alternate form of sp_setapprole. It looks like this:

sp_addapprole 'myapp', 'StrongPW1'
GO

DECLARE @theCookie varbinary(256)
EXEC sp_setapprole 'myapp', 'StrongPW1',
     @fCreateCookie = true, @cookie = @theCookie OUTPUT
-- Check user, should be myapp
SELECT USER_NAME()
-- now, unset it
EXEC sp_unsetapprole @theCookie
-- Check user should be original user
SELECT USER_NAME()
GO

So does it now support connection pooling with ADO.NET or OLE DB/ODBC? I'd think the capability exists, but because this is a recent feature addition and they'd have to store the cookie in the pooling code somewhere, to use with sp_reset_connection, I don't think its built in yet. But, if you remember to store the cookie and unset it yourself...

In July 2004, encryption built-ins and key management had just been introduced in SQL Server 2005. Now its old news. They'll be a good-sized section about it in the book revision. You're probably heard of:

EncryptBy (Key/Certificate/PassPhrase) and
DecryptBy (Key/Certificate/PassPhrase)

But there's also the less well-known:

SignByAsymKey/SignByCert
VerifySignedByAsymKey/VerifySignedByCert
HashBytes

and also the aptly-named: DecryptByKeyAutoCert and DecryptByKeyAutoAsymKey

The "auto" versions are for convenience. When you're doing encryption or decryption, you have to remember to open the keys that have been used to encrypt the data you want to read. And to close them when you're done. The "Auto" versions do this for you, when you're decrypting, so you can provide a decrypting VIEW, for example. I'll have more to say about this soon.

While working on the new edition of our book for after RTM, I've been looking around for security features that I missed or that didn't exist when we wrote it (May 2004 and before). Here's one:

In SQL Server 2005, you can DISABLE a LOGIN by using ALTER LOGIN. You can also change the name of any LOGIN (including 'sa'), making things more like Windows security. You would change the name of the sa account to give hackers one more item to guess. Here's how.

ALTER LOGIN sa WITH NAME = fred
go

-- fred (sa) on vacation
ALTER LOGIN fred DISABLE
go

-- When the sa returns from vacation
ALTER LOGIN fred ENABLE
go

About a week or two ago, there was a LONG discussion on the SQLCLR beta newsgroup about the fact that the IsNull property that you use on CLR UDTs won't return TRUE or FALSE inside the server. It returns FALSE or NULL. Turns out that, although you use this property to *indicate* to the engine that your instance is NULL (database NULL, not null reference/value), the engine will optimize things by storing the fact that your instance is NULL. And so, a method called on a NULL instance yields NULL.

This was posted by the SQL CLR team here with a workaround if you *really* wanted this to work right, even inside the server. The workaround was to decorate your IsNull get method with:

[SqlMethod(OnNullCall=true)]

I tried this, it didn't work any better. But it WILL work if you use the correct field on the SqlMethod attribute. It's

[SqlMethod(InvokeIfReceiverIsNull=true)]

The difference is OnNullCall indicates whether a method will be called if any of its input parameters are NULL. This (OnNullCall=false) allows you to use non-SqlTypes as method parameters in your .NET code and not crash if someone passes in a NULL value. InvokeIfReceiverIsNull indicates whether the method will be called if the instance of the class itself is NULL. Obviously, not null class (you can't call a method on a null reference, for example), but database NULL.

This does work as advertised:

CREATE TABLE UDTTab (theUDT sometype);
go
INSERT UDTTab VALUES(NULL);
go
SELECT COUNT(*) FROM UDTTab where theUDT IS NULL
SELECT COUNT(*) FROM UDTTab where theUDT.IsNull = 1
go

both counts return 1.

I suppose its much easier "best practice" to remember is always use the SQL IS NULL in SQL statements. Because it's FASTER. They don't have to instanciate all those NULL UDT instances, just to confirm that IsNull is, indeed, true. And mark your “get” method for those who forget.

I've been working on a student question about using Impersonation inside of a stored procedure. This one's worth sharing.

You can do impersonation using the .NET SqlClient data provider using code roughly like this:

WindowsIdentity w = SqlContext.WindowsIdentity;
WindowsImpersonationContext c = w.Impersonate();
// do something here
c.Undo();

The rule is that in the "do something here" part, I'm allowed to do things like access the file system and these happen using the correct identity. But I'm NOT allowed to do data access. I'd always thought that "data access" meant using the classes in System.Data.SqlClient to access database data. But using the System.Data.SqlTypes.SqlXml class (which uses XmlReader) is also considered data access. So this code fails:

public static void LoadSomeXML(SqlXml thexml)
{
// impersonate
// do something here is:
   XmlDocument doc = new XmlDocument();
   doc.Load(thexml.Value);
// undo
}

interestingly, this code works:
public static void LoadSomeXML(SqlString thexml)
{
// impersonate
// do something here is:
   XmlDocument doc = new XmlDocument();
   doc.Load(thexml.Value);
// undo
}

because it doesn't use XmlReader to do the load. So if you pass in a SqlXml type parameter and use this class inside an impersonation context, it will fail. The error message says "Can't revert thread token in UDF/UDP..." so I wonder if this isn't related to some other threading issues reported using the impersonation context.

A good rule of thumb is to only do the minimum number of operations required while in the impersonation context and revert back (Undo) as soon as possible. In this case, all I really wanted to do was call doc.Save("somefile.xml") to save to the filesystem. If I move the declaration of XmlDocument and doc.Load() outside the impersonation context, doc.Save() works perfectly.

In answering a question about schemas, users, and objects (search on "schemas" to see the blog series I, II, III), I realized I never posted the portion about object resolution. Here goes.

When SQL Server resolves a one-part object name, the object resolution is slightly different if you're inside a stored procedure.

If batch or dynamic SQL:
1. Look in 'sys' schema for system objects
2. Look in user's deafult schema
3. Look in dbo schema

Note that if the user owner 100 schemas, SQL Server 2005 only looks in the default schema. If the user's  default schema isn't named after him, SQL Server 2005 never looks for name.object either.

If procedural code:
1. Look in 'sys' schema for system objects
2. Look in *procedure* schema
3. Look in dbo schema

Note that, in a stored procedure for example, SQL Server 2005 won't look in the user's default schema. Only the schema where the procedure lives.

Here's a code snippet that (hopefully) make this clearer:

create login ed with password='StrongPW!'
create user ed for login ed with default_schema = edstuff
go
-- default
create schema edstuff authorization ed
go
-- named after ed
create schema ed authorization ed
go
-- another schema for procs
create schema edprocs authorization ed
go
grant create table to ed
grant create procedure to ed
go

execute as user='ed'
create table edtable (id int, description varchar(100))
create table ed.edtable (id int, description varchar(100))
create table edprocs.edtable (id int, description varchar(100))
go
insert edtable values(1, 'im in edstuff')
insert ed.edtable values(2, 'im in ed')
insert edprocs.edtable values(3, 'im in edprocs')
go
-- procedure not in default schema, but in edprocs
create procedure edprocs.geted
as
select * from edtable
go

-- i'm in edstuff
select * from edtable
go

-- i'm in edprocs
execute edprocs.geted
go

drop table edprocs.edtable
-- invalid object name 'edtable'
execute edprocs.geted
go

drop table edstuff.edtable
-- invalid object name 'edtable'
select * from edtable
go
revert
go

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.

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.

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...

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.

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.

My cohort, Dan Sullivan, has released the Service Broker Explorer on his Service Broker Developer's Spot website. It a graphic user interface for Service Broker that has some “topology map” features and configuration features and some management features for Service Broker objects. According to Dan:

“It lets you drill into Sevice Broker and add and control elements of Service Broker with a GUI. It's just meant for use to learn about Service Broker, it is not for use in a production system.“

Version 1 of what promises to be a very cool utility.

Just catching up on my blogging before a little vacation next week.

Browsing through the SQL Server BOL from the December CTP, I came across some information on something called "plan guides". There is info on some stored procedures that create and manage plan guides, a database option (in ALTER DATABASE) and a system view that lists plan guides. However, none of this these are active yet in the actual product. Let's hope this is another example of documentation being ahead of things (a la EXCEPT and INTERSECT support), because these sound interesting. According to BOL...

A plan guide is a database object that associates query hints with certain queries in the database. You can create a plan guide (using sp_createplanguide) for a SQL statement or batch. The statement can be standalone or specified to be part of a certain stored procedure. The plan guide specifies an OPTION clause specifying query hints to be applied whenever the statement is executed.

Plan guides must first be "enabled" on in a database (using ALTER DATABASE) before they can be used. Then you turn them "on and off" by sp_controlplanguide enable/disable. When a matching query is detected the hints are automatically “put in place“.

Sounds VERY cool for query plan afficianados. You can have configurable query hinting without touching your queries in the application code. And turn it on or off at will. Only thing is, NONE of it works in the December CTP. Any of the stored procedures produce "not found" message, as does the ALTER DATABASE keyword and the system view. Maybe the BOL IS a little ahead again.

I had a few spare cycles to do some reading recently, and thought I would check out the new Unified Dimensional Model (UDM) that can be used with Analysis Services 2005. I started by listening to a webcast by Amir and Ariel Netz. Interesting stuff about datamarts, data warehouse, and specialized metadata model proliferation. And the strengths of reporting against both relational and OLAP data. Although MOLAP cubes are still with us, AS2005 seems to be becoming a reporting clearinghouse, a "UDM server".

The only thing that struck me a bit strange was the concept of using live RDBMSs to feed UDM data caches as an adjunct to or replacement for datamarts and data warehouses. I've been spending a lot of time lately talking to DBAs who are concerned that features such as SQLCLR and in-database web services might blur the "focus" of a database, and make management more complex because of resource contention/sharing. I'd think that a UDM connection to a live database (rather than a reporting only database copy) might complicate management, sharing, and contention issues even more.

Reading more about this in SQL Server BOL, there IS a section on using database mirroring and snapshots to support reporting. So maybe they're not talking about a reporting connection to a live OLTP database, something that hasn't been done (with OLTP performance in mind) for a while. Maybe it's all done with mirrors.

In the last blog entry I talked about using System.Transactions in SQLCLR code. But don't try this yet, the keyword here is *will* be used. I base this on a few bugs that I filed on System.Transactions/SQLCLR being closed as “this will be fixed in beta 3”. And a statement on a public newsgroup by Pablo Castro (who would know better than Pablo?) that you'd roll back in a SQLCLR trigger by using: Transaction.Current.Rollback().

But don't try this yet. Even in the latest CTPs, using SQLCLR and System.Transactions yields some nasty messages referring to methods in EnterpriseServices.dll and fails. If I had to guess, this support would be completed about the same time as the merge of the SqlClient and SqlServer data providers. Watch this space.

There's been a lot of interest in the new System.Transactions.dll assembly lately. Especially from users of SQL Server 2005. This is based around two functionality points.

The first point of interest is that you will use System.Transactions to use transactions in SQLCLR procedural code in SQL Server 2005. In the beta 2 implementation of the SqlServer data provider, transactional coding had to use two different code paths based on whether a transaction was already started before your procedure was called. There was a section in the "First Look at SQL Server 2005 for Developers" book on this, transaction handling seemed rather complex. Using System.Transactions will make this simpler and more elegant.

The second point is that SQL Server 2005 has a feature known as promotable transactions. When you use a single connection to SQL Server 2005 and a System.Transactions TransactionScope, a local transaction is started. If SQL Server 2000 is used, or more than one database connection is used, the same TransactionScope starts a distributed transaction. Which is a few times slower than a local transaction.

After starting a local transaction with SQL Server 2005, another connection is opened in the same TransactionScope, the original local transaction is promoted to a distributed transaction, because now a distributed transaction is needed. Hence the name promotable transactions.

It is important to remember, however, that the transaction is still scoped to the *connection*. The usual cool TransactionScope demo shows a local transaction on SQL Server 2005 instance #1 being promoted to distributed when you open a second connection to a *different* database instance. It will be also be promoted if you open a second SqlConnection to *the same instance*.  Each connection has a different transaction space (lock space), even if you are using promotable transactions. Therefore, you need a distributed transaction with two connections to the same database. Even if the connection string and other environment is exactly the same.

To "knit" two lock spaces togther you'd need something fairly drastic, a la sp_getbindtoken and sp_bindsession. And they're not doing that.

The reason why this is puzzling (I was recently reminded by a student from a recent class) is that, in MTS/COM+ you could flow transactions by composing method calls, like this:

void DoTransfer(int accta, int acctb, double amt)
{
  DoWithdrawal(accta, amt);
  DoDeposit(acctb, amt);
}

Both DoWithdrawal and DoDeposit would open a connection in MTS/COM+. System.Transactions has some COM+-like transaction composition properties. But if both DoWithdrawal and DoDeposit each open a separate SqlConnection with enlist=true in the connection string (its the default), promotable transactions won't help, they'll be running a *distributed* transaction. If you really want promotable to mean: multiple operations, one database == local transaction, you'll have to pass the SqlConnection object around too. This makes things complex, because SqlConnections aren't "agile". They don't pass from process to process, for example.

Transaction is scoped to the connection (modulo sp_bindsession).

People (especially DBAs) want to see what those pesky appdomains are doing in SQLCLR.

Back in beta1 there was a system function, master.sys.fn_appdomains(), that showed which appdomains were running and which assemblies were loaded in the appdomains, number of bytes used, etc. In beta2 this view stopped working and, although you can watch appdomains being created and destroyed in the SQL Server log, I'd always missed master.sys.fn_appdomains().

You can get this information and more in the Dec CTP build:

-- appdomains
select * from sys.dm_clr_appdomains
-- loaded assemblies
select * from sys.dm_clr_loaded_assemblies

-- You can even get managed code execution statistics for currently executing queries
select command, exec_managed_code from sys.dm_exec_requests

master.sys.fn_appdomains is still around, but it doesn't return anything any more. Look for more CLR statistics in the dynamic management views (and elsewhere) in future betas.

Here's the answers to the question from Fun With static XQuery evaluation - 2

-- start with a schema collection

CREATE XML SCHEMA COLLECTION ages AS
'<xs:schema
   xmlns:xs="http://www.w3.org/2001/XMLSchema"
   targetNamespace="urn:ages"
   xmlns:tns="urn:ages">
<xs:element name="age" type="xs:int"/>
</xs:schema>
'
GO

DECLARE @x xml(ages)
SET @x = '<age>12</age>'
-- fails ??!
SELECT @x.query('string(/age)')
GO

This fails because there can be more than one <age> element and fn:string requires a singleton or empty sequence.

--- These work ---

-- this query restricts it to the first age element
DECLARE @x xml(ages)
SET @x = '<age>12</age>'
SELECT @x.query('string(/age[1])')
GO

-- this restricts the variable to XML documents. Fragments disallowed.
-- This means there can be only ONE (or zero) age elements.
-- No subscript is needed on the query then.
DECLARE @x xml(document ages)
SET @x = '<age>12</age>'
SELECT @x.query('string(/age)')
GO

The second one was a bit harder if you haven't run across the (document schemacollection) construct. Remember that XML data type can contain documents or fragments. Putting "document " before the schema collection name in any typed XML declaration restricts instances to an XML document (ie, single root element). The default is "content" so:

declare @x xml(content ages)    -- use ages xml schema collection, allow fragments or documents
declare @x xml(document ages) -- disallow fragments; documents only
declare @x xml(ages)               -- equals using "content"

Note that you can only enforce "document only" using this keyword with TYPED XML. It's not supported on untyped XML instances. You can do the equivalent enforcement with an untyped XML column in a table by using an XML check constraint, like this:

create table foo (
  xmlcol xml constraint mycontr
         xmlcol.value('count(/*)', 'int') = 1 and xmlcol.exist('/text()')=0

Hope you've enjoy this foray into static typing and XQuery. Because this is a "implementation decision" you won't find much about this in the W3C spec. The best information about this is in the excellent XML Best Practices for Microsoft SQL Server 2005 document.

BTW, in case you collect W3C specs for your own offline reference (like I do), bear in mind that the final SQL Server 2005 implementation of XQuery will be aligned with the W3C July 2004 XQuery spec series. XQuery is still a W3C "work in progress". SQL Server 2005 implements a subset of the functions and operators, adds functions to access T-SQL variables and SQL columns, and also implements static typing. So it's not a 1-to-1 match with the spec, but if you like W3C specs, July 2004 is the one you want. For now.

After the last two entries, you might be thinking "I guess I can never use text() as a node test with typed XML again". Not so. The error message reads: 'text()' is not supported on simple typed or 'http://www.w3.org/2001/XMLSchema#anyType' elements. So what's left? Mixed content, for one thing. Mixed content consists of a mixture of text and also embedded subelements.

If we change the schema to allow mixed content (this schema also allows a particular subelement):

CREATE XML SCHEMA COLLECTION mixedage AS
'<xs:schema
xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="urn:ages"
xmlns:tns="urn:ages">
  <xs:complexType name="age" mixed="true">
    <xs:complexContent mixed="true">
      <xs:restriction base="xs:anyType">
         <xs:sequence>
           <xs:element name="dogyears" type="xs:int"/>
         </xs:sequence>
      </xs:restriction>
    </xs:complexContent>
  </xs:complexType>

<xs:element name="age" type="tns:age"/>
</xs:schema>
'

Then the text() node test works with typed XML just fine:

DECLARE @x xml(mixedage)
SET @x = '
<ag:age xmlns:ag="urn:ages">This is the age in dog years<dogyears>3</dogyears></ag:age>'
-- now it works OK
SELECT @x.query('
declare default namespace "urn:ages";
/age/text()')
GO

Reference back to the previous entry. Now that we know the rules, let's try them out:

-- snip --

Data(),text() and string() accessors

XQuery has a function fn:data() to extract scalar, typed values from nodes, a node test text() to return text nodes, and the function fn:string() that returns the string value of a node. Their usages are sometimes confusing. Guidelines for their proper use in SQL Server 2005 are as follows. Consider the XML instance <age>12</age>.

Untyped XML: The path expression /age/text() returns the text node "12". The function fn:data(/age) returns the string value "12" and so does fn:string(/age).

Typed XML: The expression /age/text() returns static error for any simple typed <age> element. On the other hand, fn:data(/age) returns integer 12, while fn:string(/age) yields the string "12".

-- snip --

Try this:

DECLARE @x xml
SET @x = '<age>12</age>'
-- works as expected
SELECT @x.query('data(/age)')
GO

DECLARE @x xml
SET @x = '<age>12</age>'
-- fails
-- Msg 2211, Level 16, State 1, Line 6
-- XQuery [query()]: Singleton (or empty sequence) required, found operand of type 'element(age,xdt:untypedAny) *'
SELECT @x.query('string(/age)')
GO

Oh. This message looks familiar. It turns out that XQuery functions are strongly typed also. Here's the definition of fn:string and fn:data:

fn:string($arg as item()?) as xs:string

fn:data($arg as item()*) as xdt:anyAtomicType*

The "item()*" means that data takes a sequence of 0-n items. "item()?" means that string only takes a sequence of 0-1 item. Let's fix it then.

SELECT @x.query('string(/age[1])')

Cool. Here's the test for comprehension. Let's try this with typed XML.

-- start with a schema collection

CREATE XML SCHEMA COLLECTION ages AS
'<xs:schema
   xmlns:xs="http://www.w3.org/2001/XMLSchema"
   targetNamespace="urn:ages"
   xmlns:tns="urn:ages">
<xs:element name="age" type="xs:int"/>
</xs:schema>
'
GO

DECLARE @x xml(ages)
SET @x = '<age xmlns="urn:ages">12</age>'
-- fails as expected
SELECT @x.query('
declare default namespace "urn:ages";
/age/text()')
GO

DECLARE @x xml(ages)
SET @x = '<age xmlns="urn:ages">12</age>'
-- works as expected
SELECT @x.query('
declare default namespace "urn:ages";
data(/age)')
GO

DECLARE @x xml(ages)
SET @x = '<age>12</age>'
-- fails ??!
SELECT @x.query('string(/age)')
GO

Why does the last query (against strongly typed XML) fail, even though there is a schema? How can you fix it? There are two different "right answers".

There's been lots of puzzled faces lately when I try to explain doing XQuery against strongly typed XML (XML typed by a SCHEMA COLLECTION) vs untyped XML. The largest FAQ is why when I have this document:

<person>
  <name>bob</name>
  <age>51</age>
</person>

using the value function (after assignment to @person) @person.value('/person/age', 'int') returns my favorite error:

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

Huh? Although you know by looking at the document that there is only one age element, the XQuery parser uses static evaluation. It doesn't read your document (or read your mind) and assumes there can be more than one age element. After all, there's no schema to enforce the singleton age element, I could have 3 or 4 ages. It doesn't want to guess and be wrong at execution time. Using:

@person.value('/person[1]/age[1]', 'int')

works. I can see why age[1] is required, but why person[1]? Doesn't XML have a single root element? Actually, no. SQL Server 2005 supports fragments (well-formed, multiple root) as well as documents. Fragment support is part of the XQuery 1.0/XPath 2.0 data model.

Most people get by that. The real fun starts when you do examples using untyped XML and XPath expressions with the text() node test. text() works just fine when using untyped XML, but fails against typed XML with simple content. Here's an example (the result of a discussion with Dan Sullivan):

CREATE XML SCHEMA COLLECTION root AS
'<xs:schema
   xmlns:xs="http://www.w3.org/2001/XMLSchema"
   targetNamespace="urn:geo"
   xmlns:tns="urn:geo">
<xs:element name="Root" type="xs:string"/>
</xs:schema>
'
GO

-- UNTYPED
-- this works
DECLARE @x  xml
set @x = '<g:Root xmlns:g="urn:geo">asdf</g:Root>'
select @x.query('
 declare namespace g="urn:geo"
 /g:Root/text()')

-- TYPED
-- Msg 9312, Level 16, State 1, Line 4
-- XQuery [query()]: 'text()' is not supported on simple typed
-- or 'http://www.w3.org/2001/XMLSchema#anyType' elements,
-- found 'element(g{urn:geo}:Root,xs:string) *'.

DECLARE @x  xml(root)
-- same document
set @x = '<g:Root xmlns:g="urn:geo">asdf</g:Root>'
select @x.query('declare namespace g="urn:geo"
/g:Root[1]/text()')

But why? Isn't text() a node test that returns the value of a text() node. After casting about in XQuery specs, and SQL BOL, I finally came across this in the XML Best Practices paper.

-- snip --

Data(),text() and string() accessors

XQuery has a function fn:data() to extract scalar, typed values from nodes, a node test text() to return text nodes, and the function fn:string() that returns the string value of a node. Their usages are sometimes confusing. Guidelines for their proper use in SQL Server 2005 are as follows. Consider the XML instance <age>12</age>.

Untyped XML: The path expression /age/text() returns the text node "12". The function fn:data(/age) returns the string value "12" and so does fn:string(/age).

Typed XML: The expression /age/text() returns static error for any simple typed <age> element. On the other hand, fn:data(/age) returns integer 12, while fn:string(/age) yields the string "12".

-- snip --

Well, that was confusing. But now I think I get it. When does a element not have a text() node (or more preicsely, not allow the text() node test)?? When it's a strong-typed query using a simple type element...that's when. But why? Although I know the rules now, I'm still somewhat baffled.

This is getting pretty long, more on this topic in a bit...

A new feature of SQL Server 2005 that has been fairly well publicized is the ability, on Windows 2003 operating systems, to enforce password stregth, expiration, and lockout policies on SQL Server logins, as the operating system enforces them on Windows logins. The way that this works is that SQL Server calls NetValidatePasswordPolicy, a Win32 function available on Windows 2003. So if I have a machine policy (either standalone or more likely inherited from a domain policy) that a password must be at least 8 characters long, the following DDL will fail:

CREATE LOGIN bob WITH PASSWORD = 'bob'

you need:

CREATE LOGIN bob WITH PASSWORD = 'bob000000'

However, did you realize that password on other secrets will follow policies as well? For example:

CREATE APPLICATION ROLE somerolename WITH PASSWORD = 'aaa'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'aaa'
CREATE CERTIFICATE foo WITH SUBJECT = 'foo', ENCRYPTION_PASSWORD = 'aaa'
CREATE SYMMETRIC KEY skey WITH ALGORITHM = DES ENCRYPTION BY PASSWORD = 'aaa'

will all fail for the same policy reasons. The lone straggler, at least as of Dec CTP is ASYMMETRIC KEY. This works...

CREATE ASYMMETRIC KEY akey WITH ALGORITHM = RSA_512
 ENCRYPTION BY PASSWORD = 'a'

One of my students last week noticed that using a Service Broker object name (like a CONTRACT, SERVICE, and MESSAGE TYPE name) with the wrong case caused an error message. That's because Service Broker object names are case sensitive by deisgn. Because these identifiers can go over the wire, and you can't predict the collation of the database instance on the other side, they have to go by binary collation. Even when the objects are defined in a single database, you can't assume that's the only place they'll be used. Thanks to Roger Wolter for clarifying this...

Keep this in mind when you define a SERVICE (with the required associated contract) for query notifications or event notifications, as well as in your own broker apps.

Haven't been blogging as much recently, as I've been teaching and travelling quite a bit. Last week though, I received an interesting query on XQuery query plans (that's more uses of the word query in one sentence than I've seen before). We were looking at SQL query plans when XQuery is involved. I'd remembered that:

1. The primary XML index builds a node table over an XML column
2. If there is no XML index, the node table must be built at query time

This seems to quite a big effect on number of estimated rows. Take a 1-row table with an untyped XML column. The row contains a fairly simple document from the W3C XQuery use cases. Let's do a simple query (like /BookStore/Book) over the document.

Without the primary XML index, the execution plan contains 3 UDX expressions, two of them have a large number of estimated rows, 1000 and 10000 estimated rows. The plan step that estimates 1000 rows returns 3 actual rows; the 10000 estimate step returns 114 rows. On the other hand, once the primary index is added, these two UDX expressions are replaced by clustered index seeks, with a fairly close estimate of rows vs the actual rows.

Looks like, if you're going to be doing any non-trivial amount of queries against the XML data type a primary XML index is pretty close to being a requirement. Building it as an index bodes much better than building it at execution time.

I've been lying pretty low lately, doing research on some topics in a slightly different space (but at least obliquely SQL Server 2005 related). Came across a list of interesting student comments today, and I was back trying out (picking at?) one of my favorite spare-time topics to answer a question about. SQL Server 2005 metadata. The question was whether or not the INFORMATION_SCHEMA views were security-sensitive, and...of course they are. There are now (at least) 4 different ways to list the user tables in a database (all subject to security) and they are:

select * from sysobjects where type = 'U'
select * from sys.objects where type = 'U'
select * from sys.tables -- I always wanted a systables in earlier versions
select * from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE_TABLE'

The Dec CTP closed the last loophole; using system procedures like sp_help is now permission sensitive.

But what if you WANT a user to be able to list all of the tables in the database? The “smallest“ permission you can give a user is VIEW DEFINITION privilege on a specific object. As an example, if I had a user named 'fred' that I wanted to 'see' the authors table, I would give:

grant view definition on object::authors to 'fred'

A specific resource is called a securable. Securables exist in a hierarchy. So although there's no straightforward way to give VIEW DEFINITION on all of the tables (that I can see) in a specific schema or database in a single statement, I could give a user access to all of the objects (Table, View, Function, Procedure, Queue, Type, Rule, Default, Synonym, Aggregate, XML Schema Collection) in a particular schema with this one statement.

grant view definition on schema::dbo to 'fred' -- all in dbo schema
grant view definition on database::dbo to 'fred' -- all in the database

Note the VIEW DEFINITION is the “smallest“ permission; it gives a user permission to see that an object exists without being able to see the data. For fred's case, “select * from authors“ would still fail. Giving SELECT also gives view definition privilege, so if you can SELECT against an object, it also shows up in your metadata list. Makes perfect sense. You can tell I'm liking the new metadata views...and the new “all permissions grantable“ concept. Wait until the first user calls up and asks “where did all of the tables in my dropdown list go?“.

HTTP Endpoints in SQL Server 2005 are normally a way to support SQL Server clients that speak the SOAP protocol. It turns out that, although the HTTP endpoint requires a SOAP request, the response need not necessarily be SOAP or even XML. You can return something other than the vanilla document-literal SOAP format; the way to accomplish this is to use FORMAT=NONE in your WEBMETHOD declaration.

Usually HTTP endpoints return an XML complex type, SqlResultStream; you get this behavior with FORMAT=ALL_RESULTS, the default. FORMAT=ROWSETS_ONLY produces only SqlRowSet complex types, this shows up in .NET clients as a DataSet. FORMAT=NONE is the alternative. When you specify NONE, SQL Server doesn't even bother to wrap the result in a SOAP packet. You can use this for sending *anything* back to the client. Although your response can be a nicely formatted SOAP packet, it can also be any XML, more complex SOAP (e.g., additional SOAP Headers). The response doesn't even have to be XML at all, anything can be streamed back to the client, although this has a potential for surprising the receiver.

You use FORMAT=NONE with a stored procedure that has no input or output parameters. It must return a resultset with a single "column" with the special name "XML_F52E2B61-18A1-11d1-B105-00805F49916B". This name is actually an indicator to the underlying protocols that the result should be streamed back to the client, rather than formatted into columns and rows. You might be wondering where you've seen this column name before; if you use SELECT...FOR XML queries, this is the “column” that these queries return. You can also return raw SELECT...FOR XML output using FORMAT=NONE.

When I first heard of this option, the use that immediately came to mind was support of advanced web service procotols. Although I can *output* non-vanilla SOAP using FORMAT=NONE, there doesn't currently seem to be a way for a "webmethod" stored procedure to get ahold of the raw *input* message to an HTTP Endpoint. So, right now, I can't send complex input and have the stored procedure respond to it. Hmm...

I'm still assimilating little tidbits of information about the Nov-Dec CTP build. Put this one in your pubs database in the CTP:

create procedure deletejob (@id int)
as
begin try
 delete jobs where job_id = @id
end try
begin catch
 -- you knew about these
 print error_number()
 print error_message()
 print error_severity()
 print error_state()
 -- these are undoc'd but work in the CTP
 print error_line()           -- line number
 print error_procedure()  -- procedure name
end catch
go

execute deletejob 2
go

It turns out that the week before last, the SQL Server and ADO.NET teams (they may be part of the same team) made public the decision to merge the SqlClient data provider and in-proc SqlServer provider code into a single provider. Details are sketchy currently, but the reasoning behind this is that it's easier for programmers to use a single coding style. You'll be able to distinguish whether you're running in-server by either a bit switch or a connection string parameter. Even though I expressed my preferences to the teams, here's a couple of things I wanted to reiterate.

1. Keep SqlDefinition and SqlExecutionContext for the in-proc provider if at all possible. It's a nice way to optimize execution on the server, even if folks do tend to deduce (incorrectly) that it's related to prepared statements.

2. I've gotten to like SqlCommand's ExecuteSqlScalar method. This currently exists on the SqlServer provider but not on SqlClient. It should exist on both/merge. Here's why:

If I have an aggregate or scalar that can return NULL, it takes something like this code to use this with ExecuteScalar.

// this return NULL if no rows in table
SqlCommand cmd = new SqlCommand(
 "select max(id) from test", conn);

SqlInt32 i;
Object o = cmd.ExecuteScalar();
if (o.GetType() == typeof(System.DBNull))
  i = SqlInt32.Null;
else
  i = new SqlInt32((int)o);

Here's the code using ExecuteSqlScalar in SqlServer provider:

SqlCommand cmd = SqlContext.CreateCommand();
cmd.CommandText = "select min(id) from test";
SqlInt32 i = (SqlInt32)cmd.ExecuteSqlScalar();

Much cleaner, yes?

Syntactic changes are coming to the SQL Server in-proc provider. Compiling a stored procedure with the latest Dec CTP bits yielded the following message every time I used a method that started with "Get" from SqlContext:

warning CS0618: 'System.Data.SqlServer.SqlContext.GetPipe()' is obsolete: 'Will be removed soon'

Looking further with Reflector, it appears that SqlContext will now expose read-only static properties for Connection, Command, Pipe, etc, rather than using static methods named GetXYZ. So:

GetCommand -> replaced by CreateCommand
GetConnection -> Connection property
GetPipe -> Pipe property
GetTransaction -> Transaction Property
GetTriggerContext -> TriggerContext Property
GetWindowsIdentity -> WindowsIdentity Property

The old methods are still there (for now), but there may need to be a "mass rewrite" of beta code, samples, etc, soon. Just to let you know...

Another little thing I'd found had changed in Dec CTP. I'd reported a bug on this one and knew it was gonna be fixed eventually, but better sooner than later. I came up when Dan Sullivan suggested his "universal web service" based on SQL Server's XML data type and XML Schema Collections. Here's the prototype:

CREATE PROCEDURE some_ws (
 @somexmlinout   OUT xml(someothercoll))
AS
-- your code goes here
-- or your external name does

In previous versions, the HTTP Endpoint that defined this web service generated WSDL that, when uses with VS-proxy-generator, looked (coersed into objects) like this:

some_wssomexmlinout i = new some_wssomexmlinout();
some_wsResponsesomexmlinout j = new Responsesomexmlinout();

Object[] o = endpoint.some_ws(i, out j);

Representing the SQL OUTPUT variable as two variables not only made this more complex, but freaked out VB.NET proxy which doesn't really have a built-in concept of output-only parameters (though its possible to do with an attribute).

In Dec CTP, the "universal web service" builds proxies that look like this:

some_wsTypesomexmlinout i = new some_wsTypesomexmlinout();

Object[] o = endpoint.some_ws(ref i);

That's better. Even VB.NET likes it. Now if only I could provide a schema collection (or indicate in the endpoint definition) so that the WSDL would reflect (the only) two types in the namespace. Rather than it's (completely correct) definition of this as "any" from a collection of namesapces (those defined by the schema collection).

Well, unitl that happens, there is always custom WSDL generation...

Notice that this MUST have been a fix to HTTP Endpoint WSDL-generator, because my version of Visual Studio web service proxy generator (Oct CTP) was unchanged. Cool.

I answered a question on the newsgroups on how *exactly* inheritence works when you use it implement UDTs in SQL Server 2005. Also wanted to record the explanation here....

It doesn't work like you'd expect inheritence to when to use T-SQL, because SQL Server is blissfully unaware of the inheritence relationships (they're not recorded in the system views).

So if the class Tiger inherits from the class Cat. Passing an instance of Tiger to the T-SQL stored procedure FeedTheCat:
create procedure FeedTheCat (@thecat Cat) .... -- T-SQL here --
wouldn't work.

-- (Msg 206: operand type clash dbo.Tiger is incompatible with dbo.Cat)
declare @t tiger
execute feedthecat @t

-- or this (Msg 529: explicit conversion ... is not allowed)
declare @c cat
set @c = cast(@t as cat)
execute feedthecat @c

If the Cat class has a public instance method called FeedMe and a public field called pawcount (both are inherited by Tiger), this wouldn't work in T-SQL:

-- this wouldn't work
declare @t Tiger
print @t.FeedMe()
print @t.pawcount

-- this would
declare @c Cat
print @c.FeedMe()
print @c.pawcount

You could, however, access these fields/methods on Tiger from .NET code, As in:
// works fine
Tiger t = new Tiger();
int paws = t.pawcount;
// so does this
SqlString s = t.FeedMe();

Calling it in .NET code *through SqlCommand.ExecuteReader* (CommandText = "select sometiger.pawcount from zootab") wouldn't work.

OK, I couldn't help myself. The Dec 2004 CTP-specific readme had quite a number of things that are new. There are many improvements in SSIS, for example. The “real“ readme and the known issues file (sqlki.chm) still have July dates on them. But there are always a few little things I have queued up to try with a new build. Here's some that took 5 minutes after installing to test:

1. "Smiley face" XQuery comments still don't work. {-- comment --} still does.
2. INTERSECT and EXCEPT still not there. They are however, still in BOL.
3. FOR XML still does not work with UDTs. However, convert UDT to XML type DOES.

create table ctab (cnum complexnumber)
insert ctab values('1:1i')
insert ctab values('2:2i')

-- works! 2 rows returned with nicely formatted XML in them
select convert(xml, cnum) from ctab

-- fails, Msg 6865, FOR XML does not support CLR User Defined Types
select * from cnum for xml auto

I wonder why one works and not the other. Should use almost the same codepath for serializing UDT. Oh well. That's all for now. More later.

Now, back to our regularly scheduled technical content. About schemas, users, and owners.

Although Ed originally created the table, since Fred is the schema owner, Fred owns to table. Ed can get ownership of the table in either of two ways.

1. Someone with authority can alter the table's owner
2. Someone with authority can give Ed "take ownership" permission on the table

Until Ed has "take ownership" permission, he does not and cannot "own" the table he just created.

There are two ways to tell who owns a table. If you know who the schema owner is, "select * from sys.tables" produces a column named principal_id. If the principal_id is NULL, then the table is owned by the schema owner. If the principal_id is not NULL, the table has a specific owner. The other way is to use the OBJECTPROPERTY function and look for the property 'OwnerId'. This gives the exact owner, whether or not it's the schema owner.

This matters because, if you change the schema owner, the owner of the tables with NULL in prinipal_id changes. The owner of "specific-owner" tables does NOT change. So if the schema owner changes, say, to DBO, then DBO owns all the tables in the schema. BUT does not own Ed's table.

This whole thing is made possible because of the interesting meld that had to happen between a SQL Server-specific feature (ownership chains) and a new SQL2003-compliant feature (separation of users and schemas). Is this clear as a bell, now?

Just in case you don't believe it, code below (picks up where other code left off):

-- snip (when I left off, I was Ed)
-- ed cannot get ownership of table
-- this fails
alter authorization on object::fredstuff.edtab to ed
go

-- back to dbo
setuser
go

-- dbo can give the table to ed
-- alter authorization on object::fredstuff.edtab to ed
-- go

-- or dbo can give ed 'take ownership' permission
grant take ownership on fredstuff.edtab to ed
go

setuser 'ed'
go

-- now this works for ed, because he has 'take ownership'
alter authorization on object::fredstuff.edtab to ed
go

-- now ed can SELECT the table
select * from fredstuff.edtab
go

-- ed creates another table in the schema
create table fredstuff.table1 (id int)
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.database_principals
select * from sys.tables
go

-- owned by 'fred' (schema owner)
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed'
select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

setuser 'fred'
go

-- so can fred SELECT both tables
-- because fred is the schema owner
select * from fredstuff.edtab
select * from fredstuff.table1
go

setuser
go

alter authorization on schema::fredstuff to dbo
go

setuser 'fred'
go
-- no access for fred on this table
select * from fredstuff.edtab
-- access for fred on this table
select * from fredstuff.table1
go

setuser
go
setuser 'ed'
go
-- access for ed, he's still the owner
select * from fredstuff.edtab
-- never had access to this table
select * from fredstuff.table1
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.tables
go

select * from sys.database_principals
-- owned by 'dbo' (schema owner), this changed
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed', this did not change

select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

-- snip --

The code for the answer is below. Greg Low is, of course, correct. Fred owns the table, but he owns it by virtue of being the schema owner. Now here's part 2. Do the following:

1. Alter the authorization on Ed's table so that it is owned by Ed.
   (Interesting aside, can Ed do this himself?)
2. Create another table (using either Fred or Ed) in the schema.
   Call it "fredstuff.table1" (my creativity for making sample names is legendary)
3. Alter the schema so that it's owned by another user (say, dbo)

Who owns each table now? How can you tell?

BTW, why does this matter? Because ownership chains go by object *owners*, not by schemas.

-- snip --
create database test
go

use test
go

-- make two users
-- fred has a default schema, ed does not
create user fred for login fred with default_schema = fredstuff
create user ed for login ed
go

-- create the schema for fred
create schema fredstuff authorization fred
go

-- fred and ed can create tables
-- ed can only create tables in fred's schema
grant create table to fred,ed
grant alter on schema::fredstuff to ed
go

setuser 'ed'
go

-- ed creates a table in fred's schema
-- who is the owner?
create table fredstuff.edtab (id int)
go

-- fred (schema owner) is the owner. not ed.
-- ed cannot even SELECT against the table he just created, this fails
select * from fredstuff.edtab
go
-- snip --

UI can't stay away from the separation and users and schemas feature. I want to make sure I have it cold, and following up on information I got from Girish Chandler's talk at Win-Dev, I did the following experiement. Posted in the form of a multi-part puzzle. Answer to the first part (with code) tomorrow.

1. You have two logins, Fred and Ed. Neither one has any special privileges
2. You create users for them in a database:
   Fred has a default schema of Fredstuff, which he owns
   Ed has no default schema
3. You grant both of them CREATE TABLE
   And Grant Ed ALTER priviledge on the Fredstuff schema (this lets him CREATE and ALTER objects in the schema).
4. Now Ed issues a CREATE TABLE statement to create a table in the Fredstuff schema

Who owns the table?

A few weeks ago, I'd posted links to ADO.NET 2.0 articles I'd written for MSDN online. At that time, I mentioned there would be one more article in a series, but I'd save the subject as a "surprise". Well, the article just appeared: Tracing Data Access in ADO.NET 2.0.

Did you know there was built-in tracing in ADO.NET 2.0 and SQL Native Client? Were you surprised?

Also there's another nice whitepaper on when to (and when not to) use SQLCLR in SQL Server 2005. By the folks who brought you both SQLCLR and T-SQL enhancements in the upcoming new SQL Server release.

I was demonstrating SQLCLR appdomain usage (see previous post) to a class last week.Later on, I mentioned a different concept, that of dependent assemblies. This brought up the following question:

If user A owns assembly A and B owns assembly B, what happens if B contains a routine that calls A? Are two versions of assembly A available, owned by different owners? Or does this situation produce an error at CREATE ASSEMBLY time? Or at runtime?

The answer is that CREATE ASSEMBLY fails for assembly B, but with a fairly surprising error. The error is:

Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'Depends1' failed because assembly 'B' failed verification.  Run peverify on the assembly to determine the cause of failure.

Of course, running PEVERIFY on B works correctly, the error indicates that the dependent assembly 'A' could not be loaded into 'B's appdomain. This causes CREATE ASSEMBLY to fail.

It appears that you cannot work around this even by cataloging multiple versions of the assembly A, one version owned by A and the other owned by B. Even if there are multiple versions of the assembly in the database, SQLCLR only attempts to load the version that is cataloged so that the SQL Server name matches the name in the assembly manifest. As an example, even if I create assembly A owned by B and name it A1, attempting to catalog B fails with message 6218 above.

So if assembly A calls a method in assembly B, A and B must have the same owner.

A little-known behavior of SQLCLR (although we did mention it in our book) is that SQLCLR creates one appdomain per assembly owner, not one appdomain per database. One appdomain per database was the observed behavior in beta 1, although its important to remember that the algorithm for appdomain creation/destruction in SQLCLR is technically undocumented and subject to change.

This is NOT one appdomain per user, but one appdomain per assembly owner. This means that if user A owns assembly A and user B owns assembly B, A and B load in separate appdomains. Since remoting is not currently supported using SQLCLR, this makes allowing A and access B's classes and methods problematic. One workaround is to ensure that all assemblies that wish to share are owned by the same user, role, or application role.

BTW, one way to "see" the appdomain usage is to have a look at the SQL Server log. This contains messages for each appdomain creation and unloading.

Every time I demonstrate using XQuery against a table that contains an SQL Server XML data type to a new class, a get about one out of four people with puzzled looks. And they're the ones who know XPath and XSLT. The part that seems weird to them is that a XQuery against what they see as "a collection of documents in a table" produces a one-row answer for each row in the table. You can tell they were expecting a single document (or fragment) answer. So,

SELECT xmlcol.query('/somequery') FROM xmltable

produces a rowset with a single column; there is one row in the "answer" for each row in the table. Eventually someone pipes up with: "well, I want just one answer". Can't you do that?

You can actually get just one answer in a few different ways. The easiest is to query the entire table using SELECT...FOR XML and do the query on the result:

DECLARE @x XML
SET @x = (SELECT xmlcol from xmltable FOR XML RAW, TYPE)
SELECT @x.query('/somequery')

The only problem you can run into with this is the maximum size of an SQL Server XML data type instance, which is ~2gig. You also have to take the "extra" elements that FOR XML generates into account in your final query. You can also do the original XQuery, then do a FOR XML to combine the results together.

But, you can even cut it finer than that. Using an XML.nodes query with CROSS APPLY or OUTER APPLY, you can generate one row in the answer for each node in the sequence that XML.nodes returns (including or ignoring empty-sequence rows, that's what CROSS vs OUTER apply does). These "intermediate rows" from XML.nodes actually contain "virtual documents" with the context node (starting point of the XQuery) possibly pointing somewhere other than the root. You then do XQuery against the virtual document on the left-hand side of the CROSS APPLY. Like this:

SELECT custid, tab.col.query('itemno') --get itemno subelement of lineitem
  FROM xmlinvoices
  CROSS APPLY (SELECT * FROM
    xmlinv.nodes('//lineitem')) as tab(col)

In this case, if you have a 10-row table, but the 10 XML documents in the table contain 42 lineitem elements, you get 42 rows in the answer. Cool 'eh? If you currently use OPENXML, learn XML.nodes, because this will eventually replace OPENXML in our toolbox. It's MUCH less memory-intensive than OPENXML.

Finally (someone asked this yesterday), if you DO want to start with 1-row/answer per row in table and just throw away the rows that contain empty sequence answer, a simple subquery will do:

SELECT * FROM
  (SELECT xmlcol.query('/somequery') AS col FROM xmltable) AS x
  WHERE col <> ''

There. Exactly the answer you want...whatever the problem. Have fun.

Looking for some cool SQLCLR and/or security-related features to show off. Before I hit the road again Friday, for a long stint in “airplane-land“ on way to Europe. Browsing through the SqlServer provider in Community Preview beta version of SQL Server 2005, I think I've found one. Came across SqlContext.GetWindowsIdentity.

With this call you can find out the Windows identity of a code's executor (remember procedures execute as caller by default) and also impersonate the caller (in SQLCLR) for the purpose of calling outside or inside the SQL Server process. By default, impersonation does not occur, this is a way to specifically make it happen. Only appears to work if assembly is cataloged as UNSAFE, though it would appear that it could be useful in EXTERNAL_ACCESS assemblies as well. Subject to SQL and Windows permissions.

It's even more interesting when used in conjunction with a SQL Server Login (which has no Windows credentials to speak of) and mapping Windows credentials to a SQL Server login with CREATE CREDENTIALS/ALTER LOGIN. The credential mapping appears to be a way to allow SQL logins to have an identity (all managed by the DBA and system administrators of course) in the underlying operating system.

More on user-schema separation. In SQL 2000 and previous versions, granting someone CREATE TABLE privilege meant that they could create tables (no surprise there). The tables were "named after them" (e.g. bob.sometable) unless they were DBO. Because of user-schema separation in SQL Server 2005, that's no longer accurate.

I always explain this in terms of what you may not have the ability to do. And that, unless you have a default schema, your default schema is DBO, which you probably can't write to. There's a more polite/positive way of expressing this, courtesy of Dan. To be able to say...create tables, you need:
1. CREATE TABLE permission
2. A schema in which you are allowed to create objects. Or a schema that you or one of your roles own.

BTW, if this doesn't jibe with your experience, you're probably using the GUI (SSMS) or the legacy system stored proc sp_adduser, which creates a schema for you (for backward compatibility) rather than the new, cool (is security cool? yes, I think so), DDL statement CREATE USER. If you're using SSMS, to see what I mean:
1. Go to Security/logins in Object Explorer.
2. Choose to create a new login. Type in a login name/password
3. Click on database access
4. Permit access to a database by checking the Permit checkbox
5. Note that "default schema" and "user" get filled in with your userid
6. Click in the "default schema" cell. Oh.

NOTE that *default schema is a dropdown list*. You get to choose your default schema, or choose not to have one at all, in which case it's DBO.

People always ask...if ownership chains work the way they do, why do they not work with dynamic SQL? And how about .NET procedures and ownership chains?

Dynamic SQL is supported in nearly every database I've run across, but bad dynamic SQL has "issues", to put it mildly. If you create your dynamic SQL via string concatenation rather then parameterization (there are parts of the SQL statement that cannot be parameterized) and your strings come from user input, you will run up against a security problem known as SQL injection. Users can enter strings in applications, that, when used with concatenation, do things that you never intended your statement to do. You need to be VERY aware of the hazards of SQL injection before even *thinking* about dynamic SQL.

SQL Server procedural code (stored procedures, UDFs, and triggers) runs as the caller of the procedure. Because dynamic SQL can be dangerous, it doesn't go by the ownership chaining rule. Access to database objects in dynamic SQL is always checked. Against the original caller's permissions. In SQL Server 2005, the EXECUTE AS clause can allow procedural code to run as a principal other than the caller, which permits a way to address this behavior (other than the usual way, which was to yell “don't use dynamic SQL“ loudly).

When .NET procedural code uses the SqlServer provider to issue SQL statements, these are *dynamic* SQL to the engine. Ownership chains do not apply. I've had difficulty using EXECUTE AS with .NET code in betas, hoping that the new betas fix this. This mostly matters for procedures and triggers. You usually don't do data access in UDFs and user-defined aggregates don't have an EXECUTE AS clause at all; you shouldn't be doing data access in UDAggs anyway.

Separation of users and schemas is another cool SQL Server 2005 feature, but it has some interesting behaviors that folks may have to get used to. One is database object resolution, another is ownership chains. Say I have a user FRED.

FRED is the owner of a schema named FRED
FRED is a memeber of the role PAYROLL
FRED's default schema is PAYROLL

Even though FRED is the owner of a schema named after him, resolving a one-part object name like some_table uses a simple_algorithm: look in default schema first, then look in dbo schema. Even if you own another schema, only your default schema is used to resolve a 1-part name. BTW, the "sys" metadata schema complicates this a little bit, but I'm ignoring that for now. So for FRED, if the following tables exist:

fred.some_table
dbo.some_table
payroll.some_table

the statement "select * from some_table" executed by FRED, selects payroll.some_table. If payroll.some_table is dropped, it selects dbo.some_table. If FRED leaves the payroll department (and is removed from the role), it still selects dbo.some_table. Only when you do:

ALTER USER FRED WITH DEFAULT_SCHEMA = FRED

will it even attempt to resolve the 1-part name to fred.some_table.

I'd always wondered about how this affected ownership chains, too. A simplistic explanation of these is: authorization of a database object is only checked when an ownership chain is broken. So if procedure A uses table B, authorization is only checked if the owner of procedure A is different from the owner of table B.

So does user-schema separation change this? Is "ownership" defined as the user who owns the object or as the schema the object lives in? This is an easy one also...owner is still not object's owner, NOT the schema the object lives in.

This can have some interesting twists because you can GRANT other users the right to create objects in a schema you own:

GRANT CREATE TABLE TO ALICE
GRANT ALTER ON SCHEMA::FRED to ALICE

means ALICE can create tables in the FRED schema. But that's a subject for another day....

I was browsing through some of the SQL Server 2005 code samples today (the ones that come with the product) and came across one that was a really nice idea. It's a library to encapsulate SQL Server Service Broker T-SQL calls in an object model, called ServiceBrokerInterface. Some of the other Service Broker samples are written using it. I've always told folks when they ask about using Service Broker on the client to use raw T-SQL from SqlCommand, but this is a nice wrapper. It can be used to write a client program that processes Service Broker messages or "service programs", that is, a stored procedure inside the server that is used as an activation procedure for a Service Broker queue. Works either way. Kudos to the SQL Server sample-writing team, and that's probably not the only gem in there. Reminded me of...

My all-time favorite SDK sample by far is the OLE DB Rowset Viewer. OLE DB is a fairly overwhelming API to most folks, with a large surface area. You could use Rowset Viewer to not only understand how the OLE DB spec worked, but how provider writers implemented edge case behavior. As an adjunct to coding it yourself, of course. You could work out a complex consumer-task (perhaps involving lots of related OLE DB properties) in the tool, and then go code it up once you "got the answer". Invaluable tool, amazing aid for experimentation.

And now, for something a little technically lighter... I've taken to using a convention when writing statements that involve XML/XQuery and SQL together. I put the XML input or XQuery program on separate lines than the SQL part of the statement, this seems to make both query languages and also inline XML data easier to read when they're in the same statement. Like this:

-- SQL part
SELECT invoice.query('
{-- XQuery part, smiley face comments still unsupported --}
declare namespace inv = "urn:www-develop-com:invoices";
for $i in /inv:Invoice
return <foo></foo>
{-- more SQL follows --}
')
FROM invoices

I don't necessarily use the XQuery comments at the end (or at the beginning either), but you get the gist. If you adapt this convention, don't EVER try this:

INSERT INTO xmltable VALUES('
<?xml version="1.0" encoding="UTF-8"?>
<doc></doc>
')

Looks like a variation of the first example, but it won't work. The ?xml declaration (it's not a processing instruction) must be *exactly* the first characters in an XML document if used (its optional). The parser uses the first few characters in the declaration, if you use it, to identify the XML document, realizing that the declaration also specifies the document encoding. "Pretty formatting" XML in the example above produces a carriage return-line feed followed by XML declaration, which is forbidden. Error is:

Msg 9438, Level 16, State 1, Line 1
XML parsing: line 2, character 6, text/xmldecl not at the beginning of input

Either this:

INSERT INTO xmltable VALUES('<?xml version="1.0" encoding="UTF-8"?>
<doc></doc>
')

or leaving the declaration out if possible:

INSERT INTO xmltable VALUES('
<doc></doc>
')

will work fine.

Rereading the post about this from last night, it appears that I may have used an ambiguous analogy when attempting to figure why this feature works the way it does, and given folks the wrong idea. It has to do with whether the results of the query would be wrong or the query itself is “incorrect”. Using the invoice example from the previous post:

-- this SQL query would fail to compile
SELECT invoice.query('
declare namespace inv = "urn:www-develop-com:invoices";
for $i in /fooelement
return <foo></foo>
')
FROM invoices

-- this SQL query would compile, execute,
-- and produce correct (but not schema-valid) results
SELECT invoice.query('
declare namespace inv = "urn:www-develop-com:invoices";
for $i in /inv:Invoice
return <foo></foo>
')
FROM invoices

The reason for this is that the SQL Server “query” method on SQL Server's XML data type always produces *untyped* XML by definition. So the first SQL statement fails because the query itself is wrong, not because the results would be incorrect.

In the analogy to SQL, the statement: “SELECT foo FROM invoices” fails not because there can't be a “foo” column in the output rowset but because selecting foo on the input in invalid. Actually, you can produce a “foo” column in the output:

SELECT invoiceid AS foo FROM invoices

so perhaps that wasn't such a bad analogy after all.

Dan Sullivan pointed this out to me a couple of weeks ago. I'd forgotten about it, but it came back to bite me in a demo last week. It's a feature.

If you have an XML column, variable, or UDF return value that's tied to an XML SCHEMA COLLECTION, any XQuery against that XML will be checked for compliance to the schema as part of SQL query parsing. That's right, *XQuery* code will be checked. Here's an example:

I have an XML SCHEMA COLLECTION named invoice_xsd that contains one XML schema that defines the types in an invoice. The schema contains no definition for a "foo" element, just "invoice-like things" like PartNumber and LineItem. And I have a table

CREATE TABLE invoices (xmlinvoice xml(invoice_xsd))

The following SQL statement:

SELECT xmlinvoice.exist('/foo') FROM invoices

produces an error at *query parse time*. Not the answer "false". The error is:

Msg 2260, Level 16, State 1, Line 2
XQuery: There is no element named 'foo'
Msg 9504, Level 16, State 0, Line 2
Errors and/or warnings occurred when processing the XQuery statement for xml data type method 'exist', invoked on column 'invoice', table 'invoices'. See previous error messages for more details.

The SQL query *doesn't even execute*.

Dan's original "proof" of this behavior was that this function doesn't even catalog, ie, CREATE FUNCTION fails:

CREATE FUNCTION DoSomethingWithInvoice(@a xml(invoice_xsd))
RETURNS int
AS
-- other code elided
DECLARE @x XML
SET @x = @a.query('/foo')

Now that's what I'd call *early* validation. I guess its the same as the fact that "SELECT foo FROM invoices" also fails with "invalid column name 'foo'" error. Very cool.

I've known for a couple of weeks now that XQuery and the new XML-SQL client mapping have been dropped from System.Xml in .NET 2.0. The XQuery implementation over the XML data type in SQL Server 2005 is NOT going away, of course. Just the client-side bits. Folks are encouraged to keep using XSLT 1.0 and XPath 1.0 on the client for a while. Hmmm...

After reading (more than twice) through all of the reasons for this decision, the one that makes the most sense to me is the product schedules. The reason that doesn't quite ring true to me is "folks are happy with the XML DOM". I can't help thinking that folks were quite happy doing everything through cursors in the early days of SQL because they didn't quite grok where the power of the relational model was yet. SQL cursors were "comfortable", as the XMLDOM is to XML programmers today. The schedule argument is more reasonable. If XQuery spec won't be "done" until next year, there is hesitation about producing an implementation based on an "in progress" spec that could change at the last minute. If you remember XSL Patterns and XDR schemas in Microsoft APIs you'll understand why. But...

This week at Win-Dev folks "across the hall" were lecturing in earnest about the WSE 2.0 offering. The WSE (web service extension) offerings are supported add-ons, outside of the "core" .NET APIs, and mostly implementations of various WS-* specs-in-progress. They're not guarenteed to be compatible with future offerings or with Indigo, the next generation WS-*++ implementation. In fact, some of the specs that were supported in WSE 1.0 have already completely vanished from the WS-* landscape. There's *way* more churn in this space.

Got me thinking...why not a similar model for XQuery? That is, a supported add-on implementation of the current specification with namespaces that begin with Microsoft.* rather than System.Xml.*. Guarenteed to change, at least subtly, but existing to get folks used to using it. The alternatives, that is, using Saxon.NET or working on a community implementation of XSLT 2.0/XQuery 1.0/XPath 2.0 are already happening. How about it...Microsoft.Xml.Query/Microsoft.Xml.Mapping anyone?

One more thing about getting an error 6522 wrapper from SQLCLR procedures. the workaround I spoke about last week (dummy catch block) works to propagate SQL Server errors to the client without wrapping them in the 6522. But it doesn't seem to work if you want to throw your own user error (error > 50000). I've been throwing them by using a SqlCommand with the CommandText property set to "RAISERROR ....". You get 6522 wrapper here too.

This week I found out that you can lose the 6522 wrapper for your custom errors if you user SqlPipe.Execute on the SqlCommand with the RAISERROR SqlCommand rather than using SqlCommand.ExecuteNonQuery as I did. Thanks for Pablo Castro for this information. No word yet on whether either of these methods are the "official correct way" to accomplish throwing errors going forward.

There's a new Community Technology Preview of SQL Server 2005 available on MSDN for universal subscribers. I'm sure you've all heard of it by now, I was out of the country last week with limited bandwidth and just downloaded mine yesterday. I was meandering through the BOL, looking for interesting things, when it dawned on me that the BOL title bar read "Microsoft SQL Server 2005 Beta 3". Oh.

I'd heard rumors that the SQL set operations INTERSECT and EXCEPT might be added in beta 3. So I went to my handy BOL Search tab (it took some time to get used to Search being a Tab in the main page), and entered INTERSECT. They were doc'd! Cool.

Brought up SQL Server Management Studio and hammered out my example tables for the test. Then entered:

SELECT id FROM table1 INTERSECT SELECT id FROM table2

drumroll...answer is: "Incorrect syntax near the keyword 'intersect'."

Darn...maybe next CTP release. Either the rumor was mistaken or BOL is just a wee bit ahead of the implementation/testing. It happens.

For more on INTERSECT and EXCEPT check out the BOL. Or your E.F. Codd or ANSI SQL-92 book. Looks like it will be coming. For those of us that remember set theory (I studied it in the public school system in the 60s in 4th grade, does this fact date me?) it's how sets "always worked". For SQL relational calculus afficiandos we've been missing these keywords, although it was relatively straightforward to get the same results with using EXISTS and NOT EXISTS. Yet another feature from the standard that's implemented. I'm sure there's more to it than that, such as interesting questions like "can I use INTERSECT and EXCEPT with indexed views? or in common table expressions? or updateable ADO recordsets?"

So in this version of SQL Server there's the complement of relational calculus operations in T-SQL. And with .NET procedural code, you can whip up UDFs with formulas from differential and integral calculus that run fast, too. [sorry. couldn't help it]

A few weeks ago, I was surprised by an error message when attempting to create an HTTP endpoint with CREATE ENDPOINT. The error was "You do not have permission to perform this operation". The reason I was surprised was that SQL Server 2005 was running in a domain environment and I was logged on as domain administrator at the time. This meant I had sa-level database privledges, and privileges on the OS as well. Hmmm...

The reason for this turned out to be pretty straightforward. I was running the SQL Server process as a relatively unprivileged account, principal of least privilege and all that. When you create an HTTP endpoint, SQL Server issues a "namespace reservation" for part of the HTTP namespace. The reservation is used when other applications (like IIS 6.0) use the HTTP.sys implementation at the same time. It attempts to issue the reservation *using the identity of the principal this is running the SQL Server process*, not as your currently logged on user.

The way to accomplish the reservation under these conditions is to use a system stored procedure, sp_reserve_http_namespace.
It looks like this:

sp_reserve_http_namespace N'http://mymachine.mydomain.com:80/mydir'

Note that in order for this procedure to work, you must be logged in to SQL Server as a Windows login that has OS admin privileges. And so I was, and it worked. So did CREATE ENDPOINT... FOR HTTP. However I noticed that, in my CREATE ENDPOINT DDL statement I had to use the exact machine domain name for the SITE operand, rather than the default ('*' which means "use all machine names not otherwise reserved"). Oh.

That had everything to do with my input to sp_reserve_http_namespace. Using:

sp_reserve_http_namespace N'http://*:80/mydir'

instead, set things up so that I could use '*' as a SITE operand. Whew.

About a month ago, I'd written about unhandled errors in .NET procedural code always causing error 6522 to be returned to the (T-SQL) caller (see "Error 6522 and You"). Because the "real" error (the one that caused the .NET code exception) is wrapped in a 6522 error, retrieving the value of @@error will always give you 6522, not the real error. The canonical example is:

public static void Proc1
{
 SqlCommand cmd = SqlContext.GetCommand();
 // causes error 547 - reference constraint 
 cmd.CommandText = "delete authors where au_id like '1%'";
 SqlContext.GetPipe().Execute(cmd);
}

Called from T-SQL:

execute proc1
select @@error

The error you get is:

Msg 6522, Level 16, State 1, Procedure CauseError, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'CauseError':
System.Data.SqlServer.SqlException: DELETE statement conflicted with REFERENCE constraint 'FK__titleauth__au_id__0AD2A005'. The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.

@@error = 6522

This week one of the students in SQL Server 2005 class discovered a neat workaround. Wrap the .NET code in a try-catch block and do nothing in the catch block. Like this:

public static void Proc1
{
 try
 {
   SqlCommand cmd = SqlContext.GetCommand();
   // causes error 547 - reference constraint 
   cmd.CommandText = "delete authors where au_id like '1%'";
   SqlContext.GetPipe().Execute(cmd);
 }
 catch { // dummy catch block }
}

This produces the expected error. And the expected value of @@error:

Msg 547, Level 16, State 0, Line 1
DELETE statement conflicted with REFERENCE constraint 'FK__titleauth__au_id__0AD2A005'. The conflict occurred in database 'pubs', table 'titleauthor', column 'au_id'.

@@error = 547

I'll have to try this in some other example exceptions, but it seems to do the trick in this one. Thanks to Bertil Syamken for the suggestion.

After having been immersed in .NET since the alpha version, I'd lost touch with how many products use OLE DB and ODBC. After all, when you talk about SQL Server and SQL 92/99/2003 standards, the standard API for a SQL database *is* still SQL CLI (call-language interface). And the canonical implementation of SQL-CLI is ODBC. When I spoke recently about SQL Server 2005 Web Services as a way to acheive interop between unlike client architectures for SQL Server, someone reminded me that ODBC drivers for "unlike architectures" have existed for years. Uh, yes, of...course.

Parts of SQL Server 2005 like linked servers, replication, reporting services, and DTS (renamed SQL Server Integration Services (SSIS) last week) use OLE DB and ODBC as well. Although SSIS supports ADO.NET too.

SQL Server 2005 ships with a new communication layer known as SNI and a new OLE DB provider and new ODBC driver that use it. These are bundled together in a part of the product that runs on both server and client called SQL Native Client (or SNAC for short). Folks that have existing non-.NET applications are *really* interested in:

1. How the SNAC provider/driver supports the new data types
2. Any subtle differences between the new provider/driver and the current ones

Two weeks ago, someone asked about support of the new "MAX" data types VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) in both SQLOLEDB/SQL Server ODBC and SNAC OLE DB/ODBC. For example, does VARCHAR(MAX) resemble VARCHAR or TEXT? So I took out my handy-dandy OLE DB Rowset Viewer and had a look. I looked at column metadata (DBSCHEMA_COLUMNS) and the  DBCOLUMNINFO structure (after a SELECT) of the following table:

CREATE TABLE testclob (
  avar VARCHAR(8000),
  amax VARCHAR(MAX),
  atext TEXT)

Answer is: VARCHAR(MAX) looks almost exactly like a TEXT data type (as far as the API is concerned of course; they're way different as far as TSQL is concerned), both in SQLOLEDB and the SNAC OLE DB provider. And both providers yield almost the same metadata. The big caveat there is the "almost". Here's the two almosts:

1. Both VARCHAR(MAX) and TEXT have the same capabilities/properties with respect ISLONG and MAYDEFER (ie, supports deferred fetch). But, in the Schema Rowset the character_max_length and octet_length is 2147483647 (2gb) for TEXT and 0 (that zero) for VARCHAR(MAX). Be careful using this to size to allocate buffers.

2. SNAC's DBCOLUMNINFO listed (maximum) ColumnSize as 4294967295 (4 gb?) rather than 2 gb as SQLOLEDB provider did.

I'd thought that SNAC's behavior was going to be identical to SQLOLEDB to ease migration, and it appears as though, except for the one metadata anomaly this is true. And I didn't really expect SNAC and SQLOLEDB to be exactly the same regarding *new* features; after all, that's the point of using the SNAC provider/driver, new feature support. There may be a few other subtle differences, more on this later.

I heard that when SQL Server 2005 ships, SQLCLR (the ability to execute .NET code in SQL Server) will be disabled by default. This is part of an "off-by-default" philosophy that goes "because there are so many revolutionary new features in SQL Server 2005, and DBAs and programmers may not have had time to digest the ramifications of using all of them, you'll have to enable them, one at a time, when you understand them. They'll be off until you make a conscious decision to turn them on". IIS 6.0 works this way with its programming extensions; for example, you must enable using ASP.NET or ASP classic.

Thought I'd better look up how to turn it on and off:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- turn it on
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- or turn it off
EXEC sp_configure 'clr enabled', 0
GO
RECONFIGURE
GO

That was easy. I turned it off and restarted SQL Server wanting to see what error message it would produce. Some folks I've spoken to claim to want it off even though they understand it, because SQLCLR loads the .NET runtime. This takes about 10 meg of memory, from SQL Server's "normal" memory pool (it does not use the MEM-to-leave pool as in-SQL Server COM components do). Turning SQLCLR off did not cause the CLR to be unloaded (or at least it didn't produce a log message to that effect). So I wanted to see if I could get the .NET runtime to load if SQLCLR is disabled. Started with:

CREATE ASSEMBLY foo FROM 'c:\foo.dll'
GO

Even though foo.dll doesn't exist on my machine, this statement normally causes to .NET runtime to load. Even though it errors out with the message:

Msg 6501, Level 16, State 7, Line 1
CREATE ASSEMBLY failed because it could not open the physical file 'c:\foo.dll': 2(The system cannot find the file specified.).

The reason that the .NET runtime is loaded in this case in that SQLCLR internally calls Assembly.Load("c:\foo.dll") (or some variation) to load the assembly and validate it using the reflection APIs. Both Assembly.Load and reflection are, of course, managed code, requiring the runtime.I expected a different error this time, because SQLCLR is disabled. Same error. And the log revealed that the .NET runtime had been loaded. Even though SQLCLR is disbaled. Hmm....didn't expect that. Just to see how far I could go, I got out a real assembly and ran:

CREATE ASSEMBLY MetricConverter
 FROM 'c:\types\metricconverter.dll'

This succeeded, I'd cataloged my assembly. Hmm... How about:

CREATE FUNCTION convertme(@a FLOAT)
RETURNS FLOAT
AS EXTERNAL NAME MetricConverter.[DM.EssentialYukon.MetricConverter].KilometersToMiles

This succeeded too. At this point I started to doubt that I'd actually turned SQLCLR off. Then I ran:

DECLARE @f FLOAT
SET @f = dbo.convertme(42)
PRINT @f

This failed as expected:

Msg 6263, Level 16, State 1, Line 2
Execution of user code in the .NET Framework is disabled.

So the 'clr enabled' option disables *running* user CLR code in SQL Server 2005. It doesn't diable loading the runtime. Or cataloging database objects, like assemblies and UDFs, that use SQLCLR. It's a convenience to allow DBAs (usually the only ones with this permission) to catalog these objects before allowing the actual user CLR code to be executed. If you're really concerned about the 10 meg, don't use the DDL. Note to self: test *everything* before making assumptions. I guess that goes along with “off-by-default”.

It turns out that you can force SQL Server to use FX libraries that are not on the approved list. Deploying user code that uses these through Visual Studio produces:

Error: Assembly 'system.runtime.remoting, version=2.0.3600.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog.

But that's because Visual Studio uses CREATE ASSEMBLY "from bytes", passing in the byte array. You can also use "CREATE ASSEMBLY foo FROM 'c:\types\foo.dll'. SQL Server then searches the directory specified (c:\types in this case) for dependent assemblies. When you use VS autodeploy it doesn't do this, making "automatic cataloging of dependent assemblies with IsVisable=No" difficult (impossible?) to prove when using autodeploy.

When I use CREATE ASSEMBLY with a file on my "simple remoting program", there is a more telling error:

Msg 6581, Level 16, State 1, Line 1
Could not find assembly 'system.runtime.remoting' in directory 'c:\types\'.

Oh. Copying 'System.Runtime.Remoting' to c:\types produces another "Could not find assembly". Eventually I put my assembly in the FX lib directory so it can resolve everything and set the permission_set to unsafe. This works and I've deployed it. Looking in sys.assemblies, it's taken 15 FX libraries that are not on "the list" with it. They're cataloged as "normal" libraries with IsVisible=True in the metadata. That means you can't declare catalog or use their methods from SQL Server directly, only from other user assemblies, subject (of course) to security.

It also DOESN'T imply that these libraries are unsafe in general, just when you use SQL Server as a runtime host. SQL Server likes to do things like control it's own threading, exception handling, memory allocation, etc, etc. Most/all other current runtime hosts don't. It's not going to USE most/any of these in my one-line program, mind you, it's just following dependency chains in the manifests. Things may work fine.

BTW, just in case you didn't gather this, this is NOT NOT NOT RECOMMENDED. Just meant to answer the question "can you...". I, for one, am GLAD there's a list. That they are definately looking out for reliability/scalability/etc. Very cool.

After reading Niels' comment about the framework class library list, a quick check proved he was right, System.Runtime.Remoting.dll was not on the approved list. Adding a reference to this library (using his cool Object Browser trick) and writing a one-line stored procedure:

[SqlProcedure]
public static void SomeTestProc()
{
  TcpChannel t = new TcpChannel();
}

proved this. The error message from autodeploy is:

Error: Assembly 'system.runtime.remoting, version=2.0.3600.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog.

Using this method (note that you have to *use* a type in the assembly, not just have a reference to the assembly, for this error to appear), I went through the list of libraries that are allowed and some useful ones that seemed to be disallowed. My first intuition was close, this IS the list of allowed assemblies, it's just a little out of date. By trial-and-error:

Out (but in list):
  System.Runtime.Remoting.dll
  System.Runtime.Serialization.Formatters.Soap.dll

In (but not in the list):
  System.OracleClient.dll
  System.Transactions.dll

Out (never on the list, just tried them):
  System.Web.dll (Cache class)
  System.EnterpriseServices.dll (DTC and others)
  System.Messaging.dll (MSMQ)
  System.DirectoryServices.dll (Active Directory)

To get "on the list" BTW, requires that an assembly go through a special review to ensure reliability. This would also require annotating the assembly with HostProtectionAttribute instances where needed. Note that HPAs affect individual methods at runtime, this is CREATE ASSEMBLY-time message. It has been postulated that it's the presence of HostProtectionAttribute that indicates "OK to load", but System.SqlXml.dll and System.Transaction.dll don't have a single instance of an HPA (or any other distinguishing attributes I could find with Reflector) and they don't produce the "not found" message.

Perhaps the review process is still a work in progress. After all, SQL Server 2005 is still beta. Guess that's why I tell folks the list is subject to change during the beta process. There's still work going on to further ensure SQLCLR is secure/reliable/scalable. Cool.

Back from vacation, drove from Ashland back to Portland Saturday, then Portland to Redmond Sunday. A lot of driving. The Michael Brundage XQuery book was even better the second reading. Joe Celko's book was excellent (what else?) also. So was OSF presentation of Henry VI, Much Ado About Nothing, and King Lear, but my favorite was a non-Shakespeare play entitled "Humble Boy". It was about "aha moments" in the life of a scientist.

Back to teaching and to SQL Server 2005 SQLCLR.

While teaching folks about SQLCLR, I've always said that SQL Server uses a hardcoded list of "approved" framework class libraries (libraries that are part of the .NET framework, e.g. System.dll) to determine which libraries are allowed to load. Also, although user assemblies are cataloged as SAFE, UNSAFE, or EXTERNAL_ACCESS, which FX (framework class) libraries are allowed to load does not vary with safety level. Which methods in those libraries can be called without causing a security exception does vary; however if a library is not "on the list" it won't be loaded even if referenced in UNSAFE user assemblies. Folks always want to know where the list is (ie, "prove it to me").

Lately I came across the list in a rather unique way. Create a Visual Studio B1 SQLCLR project in any language (a Database/SQL Server project). Now choose "Add Reference" either by right-clicking on references or any only means. Note the list of libraries you can choose. These include only the FX assemblies allowed to load and also all of the libraries at your safety level or below that are already cataloged in the database that autodeploy is pointing to. Also, when you change the safety level in your project, the FX assembly list doesn't change. Aha...

I'd also like to see a browse button on the AddReference dialog as well. Not having a browse button means you can't add a user library that's not already cataloged. Why I might want to do that is a story for another day.

Back to XML and schema collections in SQL Server 2005...

After my experiences with Office 2003 schemas yesterday (thanks for getting me going, Dan), last night I went out onto the Internet, prowling around at 1-2am looking for XML schema repositories. The most likely candidates are mostly gone or inactive. Microsoft's http://www.biztalk.org now links to http://www.microsoft.com/biztalk, information about the Biztalk product. No schema repository here, that I could see. James Tauber's http://www.schema.net has a "returns soon" sign on its digital door. The XML.ORG Registry seems to have the most, although less than I'd expected. There were actually some with namespace URI's for in 1999 XMLSchema namespace, and some DTDs too. Hmm...

Why all this interest and schemas and schema repositories? I'm looking for some non-trivial XML schemas (preferably interlocked sets of schemas, ie. with imports) along with exemplar documents, to try out with SQL Server 2005's CREATE XML SCHEMA COLLECTION and static-typed XQuery. If you know of any, send me links. Should make for some enjoyable times on planes in the near future, like puzzle books.

And what does this have to do with Peaches en Regalia? Once upon a time, in a "previous life", I wrote a program called SMUS2T that converted Amiga SMUS music format to a format for a popular sequencer program, Dr T's KCS. SMUS2T is so obscure that a Google search returns no hits. I then set out to find use cases in test data to break it, naturally. Settled on Peaches en Regalia (by Frank Zappa) because, if you've ever heard it, it contains almost every musical sequence that my spec had. Quintuplets, septuplets, perfect test data. And I liked listening to it, at least I did for the first 50 iterations or so. Two days later, the program could convert it and all 4 tracks played synchronized from beginning to end. And it could convert Yusef Lateef tunes correctly as well.

So now I'm looking for test data again. XML schema collections this time. To test something new. I'm sitting here listening to the digitally remastered version of Peaches from the Hot Rats album, sounds different after he re-produced it for the CD medium. Although I think my favorite track is now "The Gumbo Variations".

I just answered this question on the "public/private" sqlclr newsgroup, but now I'm wondering myself. What IS all that stuff doing in sys.assembly_files? If you've done autodeployment of a SQLCLR assembly lately, take a gander as the metadata view sys.assembly_files. There's not only the binary assembly code and the debug symbols (pdb file) but also source code. And finally, Visual Studio project file, .csproj or .vbproj.

You actually need:
  The real assembly code to run the assembly. Of course.
  The debug symbols if you want to enable in-process debugging

You might like the source code because then its stored in the database like T-SQL code is. There was a thought at one point that you'd deploy your source code and SQL Server would run a compile on it inside SQL Server. That's not gonna happen (at least this time around), and I sort of wondered why people wanted it, maybe because T-SQL stored procedures work that way, but more likely for manageability. That way the actual code is backed up with the database. Anyhow, in beta 2 the source lives in the server, but is not used by the execution engine. Interestingly, if you choose to use an external key file to codesign the assembly, maybe to deploy it to clients for deployment to their GAC, this isn't stored in the database. At least I haven't found it so far.

Still not sure why the VS project file is there though, maybe because it contains project settings you'd need if you wanted to rebuild. But you'd need the keyfile too.

BTW, if you do a "manual" deploy with CREATE ASSEMBLY only the actual binary is cataloged, of course. You can add the rest of the stuff manually with “ALTER ASSEMBLY ... ADD FILE FROM ...”. Or add any other file that strikes your fancy, for that matter.

After lots of interesting (but fruitful) experiences with the XQuery engine in SQL Server 2005, I decided that I'd rather have an XML schema collection for everything than put [1] (that's the numeric predicate meaning "item 1 using 1-based ordinals") everywhere in the XQuery that I needed a singleton. It's a good idea to use "typed XML" anyway. Dan Sullivan thought a good non-trivial use-case would be Office 2003 docs and went forth to create SQL Server 2005 XML schema collections from the publically available schema documents.

First thing he ran into is that specifying processContents="lax" now causes an error when cataloging the XML schema collection. It used to catalog without error and then use processContents="skip" behavior instead in beta1. The Office schemas use this construct, so he's created some code to change it to processContents="skip" so SQL Server 2005 would "like" it. We've run into a couple more cataloging problems, repeated attributes on the same element, etc... Wonder where you report Office schema problems.

If you're wondering what this is about technically, pulling out my favorite XML Quick Reference (by Aaron and Gudge), they define processContents (on xsd:any or xsd:anyAttribute) as "Specifies whether a schema processor should validate the elements/attributes appearing in place of the wildcard". The default is "strict", meaning validate them. Lax means the schema processor *should* (but is not required to) validate if schema information is available. Skip means a schema processor should not validate these.

BTW guys, when's Essential XML Quick Reference Volume 2 due out? There *lot's* more specs now. Maybe you could publish an Essential XML Reference Encyclopedia?

With the introduction of the XML data type (using an XML data model) comes there's a choice of pulling XML out of the database as a stream or column. Using an XML data type as a column type will get you a column of type XML. That's SqlXml to you in .NET. SELECT ... FOR XML gets you a stream. In SQL Server 2005 there a new keyword TYPE on SELECT ... FOR XML that makes the stream into an XML type. Some/most tools make the stream look like a column with up to a size of 4k per "row". SSMS in SQL Server 2005 does better with it than Query Analyzer.

There's different APIs for these too. In ADO.NET there is ExecuteXmlReader vs ExecuteReader/ExecuteScalar. In OLE DB you use a COM IStream implementation, there was a Stream class added to ADO classic. Although you can use the either API for stream or column, it appears to be a stretch to use “other one“ in both cases. As an example, using a 'SELECT xmlcol from xmltab' with ExecuteXmlReader only gets you the XML in the first row. As you'd expect, if you remember ExecuteXmlReader returns a singleton like ExecuteRow in ADO.NET 2.0 does. At this point, I'm recommending using the "right" API for the representation. And you can always translate to a string. And, using the client XML APIs, any of the supported XML data models (such as document or Infoset).

SQL Server 2005's HTTP endpoint functionality uses http.sys, the HTTP stack in the kernel, to do it's processing. Until lately Windows Server 2003 was the only OS to implement http.sys and HTTP endpoints only worked when SQL Server 2005 ran on this OS.

Windows XP SP2 shipped with http.sys and HTTP endpoints are now enabled on this OS as well. There is a difference between the two OSes that makes this feature subtely different on each one. The difference is in the version of IIS that runs on each OS. If you are running IIS at the same time, XP SP2 runs IIS 5.1 and Windows Server 2003 runs IIS 6.0. The difference is that IIS 5.1 does not use http.sys even if it is available and therefore grabs all of the HTTP traffic on whatever ports it's listening on. In most cases this is port 80 and/or 443. This interferes with SQL Server HTTP endpoints trying to use these ports. Dan Sullivan confirmed this when running the class on his laptop running Windows XP SP2 last week.

There are a couple of ways to get around this. You can specify a port other than port 80/443 for SQL Server's HTTP endpoints and run both endpoints and IIS at the same time. Another workaround is to shutdown IIS and only run SQL Server's HTTP endpoints.

Some folks have asked why I appear to use the XQuery data() function on occasion, but not always, when I'm doing demos. If you want the atomic value of an attribute, rather than the attribute node itself, data() is your friend. data() makes an atomic value from any XQuery item, but with attribute nodes it's particularly useful. SQL Server's XQuery functions don't allow returning bare attributes at the root level (bare text nodes are allowed), and the value function is always looking for a singleton atomic value. The data() function helps in these situations; here's an example:

declare @x xml
set @x = '<foo bar="baz"/>'
-- returns error: "Attribute may not appear outside of an element"
select @x.query('/foo/@bar')

declare @x xml
set @x = '<foo bar="baz"/>'
-- returns baz
select @x.query('data(/foo/@bar)')

Using the data() function is different from using the string() function. data() takes a sequence of items (nodes or atomic values) and atomizes them, ie. returns a sequence of atomic values. string() returns the string value of a single item.  Here's an example comparing data() to string():

declare @x xml
-- XML fragment
set @x = '<x>hello<y>world</y></x><x>again</x>'
select @x.query('data(/*)')

returns a sequence of two string values:
helloworld again

declare @x xml
-- XML fragment
set @x = '<x>hello<y>world</y></x><x>again</x>'
select @x.query('string(/*)')

returns a static typing error, because string() requires a singleton or empty sequence as input

declare @x xml
-- XML fragment
set @x = '<x>hello<y>world</y></x><x>again</x>'
select @x.query('string(/*[1])')

returns a single string value:
helloworld

I'll have more to say about static typing in SQL Server 2005 in the future. It can seem like it makes simple queries, where you “know” the data but don't use a schema harder to write, but I'm getting to like it.

There are a number of different ways that SQL Server 2005 checks to see whether on not you're trying to catalog or execute dangerous .NET code. This checking is enforced by SQL Server at CREATE ASSEMBLY time and also at execution time.

During CREATE ASSEMBLY, SQL Server reflects on the assembly and check for certain coding practices than can "cause problems" at runtime. It's well-known that SQL Server does not allow you to catalog an asssembly as SAFE or EXTERNAL_ACCESS if the assembly contains finalizers or mutable statics.

It's also well-known that SQL Server will throw a SecurityException at runtime if you attempt to access classes or methods marked with a Host Protection Attribute. HostProtectionAttribute was added to the .NET 2.0 runtime to allow class-library authors to annotate their code in order to inform the host (in this case SQL Server) of behaviors such as "MayLeakOnAbort" or "SelfAffectingThreading". HPAs provide protection at execution time, not catalog time.

It's a little-known fact that SQL Server also checks for the presence of certain attributes on assembly code at CREATE ASSEMBLY time. If your code contains dangerous attributes CREATE ASSEMBLY does not succeed. This is different checking than what HPAs allow. This list of "dangerous attributes" is checked at catalog time. The attributes SQL Server considers dangerous include:
  System.STAThreadAttribute and System.MTAThreadAttribute
  System.ThreadStaticAttribute
  System.ContextStaticAttribute
  System.DllImportAttribute

Doubtless there are others. Attempting to catalog an assembly that contains one of these attributes produces an error message like this:
Msg 6237, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'ProcWithAttr' on type 'StoredProcedures' in assembly 'SomeAssm' has invalid custom attribute 'System.STAThreadAttribute'.

Interestingly, I was able to find one that slipped by; SQL Server will allow cataloging an assembly with the UnverifiableCodeAttribute as SAFE. Maybe it was due to the fact that my module did not actually *contain* unverifiable code, just an attribute that said it did.

I'm amazed at the thoroughness with which SQL Server checks for code that can cause it problems as a .NET runtime host. .NET reflection is truly its friend; try to implement this type of checking on an extended stored procedure!

If you're developing procedural code, UDTs, or UDAggs in SQLCLR for SQL Server 2005, Visual Studio 2005 has these nice auto-deploy projects called SQL Server projects that you can use. Comes with templates for each SQL Server 2005 item you can develop, just choose a SQL Server and database at project creation and hit “Deploy” menu entry when you're ready. F5 debugging is even built in to them. VERY cool.

But...there are some folks that don't want any help. Or have an existing class library they want to try. Or want to start from first principals and show others how to install a .NET assembly “by hand”. In Visual Studio Alpha/SQL Server 2005 Beta1 you could do this with a default class library project. In VS Beta1, you can't because there are mutable statics in the class library project “by default”. You have to install it as UNSAFE. Or....

1. Create a new class library project.
2. Click "show all files".
3. Look under Properties (C# project) or MyProject (VB.NET project)
4. Delete Resources.resx and Settings.settings. If you are working in VB.NET project you do NOT have to delete MyApplication.myapp.
5. You're good to go. Just code it up and manually deploy as SAFE (the default).

To squeeze every ounce of performance out of the SqlServer data provider, the rule is to allocate the least number of ancillary object instances possible. In SQLCLR Optimizations 1, I mentioned a mechanism to keep from creating a SqlCommand and associated objects, just to initialize a SqlDefinition.

Another performance increase comes by not allocating/using extra buffers. When you execute a statement that produces a rowset, this means using SqlDataReader only when you need it. You need it only when you are  going to consume the results IN the .NET procedure and do some processing with them. You don't need a SqlDataReader just to pass a rowset back to the client. As an example, if I want to emulate this trivial T-SQL proc:

CREATE PROCEDURE getauthors
AS
SELECT * FROM authors

this .NET code looked pretty straightforward to me:

public static void GetAuthors1()
{
  // create and initialize command
  SqlCommand cmd = SqlContext.GetCommand();
  cmd.CommandText = "select * from authors";

  // get data reader
  SqlDataReader rdr = cmd.ExecuteReader();

  // pass results to client
  SqlContext.GetPipe().Send(rdr);
}

This actually allocates enough of a buffer in your procedure to hold a row and reads into the buffer, just to turn around and hand it to the SqlPipe. That buffer is pure overhead. A more performant way, that basically points the rowset at the SqlPipe, is to use SqlPipe.Execute. You can Execute a SqlCommand or use a SqlExecutionContext.

The SqlExecutionContext is the class that represents the volitile parts of SqlCommand, the parameters, and contains methods to execute SQL and return results. Here's the same procedure, using both SqlDefinition/SqlExecutionContext and SqlPipe.Execute.

// initialized once during class construction
readonly static SqlDefinition def =
  new SqlDefinition("select * from authors", null, null);

public static void GetAuthors2()
{
  // lookup static part of command
  // in this example, the command is entirely static
  SqlExecutionContext ec =
    SqlContext.GetConnection().CreateExecutionContext(def);

  // execute it. no SqlDataReader needed, results to client
  SqlContext.GetPipe().Execute(ec);
}

At first glance, you look at System.Data.SqlServer and see a few classes that look strange, but most everything looks the same as with the SqlClient provider. The big perf improvements, however, come from using the classes that look strange and are different. That's why they are there, to allow efficiencies that you can't get (like not allocating a buffer to receive a rowset) on the client. But you wouldn't send a rowset back to the "client" from the client, this only happens in a procedure inside SQL Server. On the server it's a whole different ballgame.

What's error 6522? It's the error that you always get from an unhandled exception in a SQLCLR stored procedure. If I write a SQLCLR error that throws an unhandled exception (let's write one on purpose):

[SqlProcedure]
public static void ErrorExecute()
{
    SqlCommand cmd = SqlContext.GetCommand();
    // everyone knows Bob can't type
    cmd.CommandText = "select * from authorss";
    SqlContext.GetPipe().Execute(cmd);
}

using it from T-SQL:

execute errorexecute
print @@error

Yields:

Msg 208, Level 16, State 1, Line 0
Invalid object name 'authorss'.
Msg 6522, Level 16, State 1, Procedure ErrorExecute, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'ErrorExecute':
System.Data.SqlServer.SqlException: Invalid object name 'authorss'.
   at System.Data.SqlServer.Internal.StandardEventSink.HandleErrors()
   at System.Data.SqlServer.Internal.RequestExecutor.HandleExecute(EventTranslator eventTranslator, SqlConnection conn, SqlTransaction tran, ClrLevelContext clrlvlctxtPipe, UrtExecutionType uetType, InternalResultSetOptions irsoOpts, CommandBehavior eBehavior, Object& objResult)
   at System.Data.SqlServer.Internal.RequestExecutor.ExecuteToPipe(SqlConnection conn, SqlT.
6522

Not that @@ERROR returns 6522, NOT 208. If you write an equivalent bad T-SQL proc:

create procedure errortsql
as
-- cant type any better in TSQL
select * from authorss
go

execute errortsql
print @@error

returns 208.

Why does this matter? Say that I have a T-SQL stored procedure (not this one, obviously) that I want to replace with a SQLCLR equivalent that say, runs faster. Say the procedure is used in 20 places in my application, each with semantics that check for specific values of @@ERROR. Won't work the same.

You can't catch the exception in SQLCLR and throw the "correct" one (using a SqlCommand with CommandText of RAISERROR(...)) either, because you can't throw a 208 error (or any other system error) with RAISERROR. So I'll have to change each one of my caller procs too when I switch to SQLCLR. The only current workaround is to change to the new T-SQL try-catch syntax

BEGIN TRY
  EXECUTE errorexecute
END TRY
BEGIN CATCH
  -- prints 208, whew...
  PRINT CONVERT(varchar(10), error_number())
END CATCH

Hopefully this will be changed or a workaround in SQLCLR will be available in the next beta. I want @@ERROR to somehow return 208, not 6522. Or this will be a great motivator for everyone to change to T-SQL TRY-CATCH.

Good T-SQL programmers are always looking to optimize their procedural code. Little optimizations can end up as big savings if the code will be executed many times or if it's used in many places in the application. Good SQLCLR programmers will be doing this too. Recently I ran into a micro-optimization that has promise.

Everyone who's used SQLCLR in earnest knows by now that it's useful to separate SqlCommand into two pieces: SqlDefinition and SqlExecutionContext. SqlDefinition is the static portion of the command, SqlExecutionContext contains the execution methods like ExecuteNonQuery and friends. Last time I'd heard rumors, about a 20% performance improvement, although it's not usually useful (or not allowed in betas) to report performance numbers. It's beta software after all, possibly with extra unoptimized code that will be removed/optimized at release. Especially SQLCLR, a brand new feature. So I didn't confirm the rumored numbers; sounded like a reasonable assumption.

SqlDefinitions are best initialized in the class constructor and stashed in readonly static (Shared Readonly in VB.NET) variables. I've done it like this:

static readonly SqlDefinition def = null;

static MyClass() {
  SqlCommand cmd = SqlContext.GetCommand();
  cmd.CommandText =
      "select * from authors where au_id like @au_id";
  cmd.Parameters.Add("@au_id", SqlDbType.Varchar);
  cmd.Parameters[0].Size = 50;
  def = new SqlDefinition(cmd);
}

Lately, I've noticed there's an even better way. There's a constructor for SqlDefinition that takes a CommandText, CommandType, array of SqlMetaData, and array of ParameterDirection. I can even put the initialization of these in the variable declarations, like this:

readonly static SqlMetaData[] md =
    new SqlMetaData[1] { new SqlMetaData("@au_id", SqlDbType.VarChar, 50) };
readonly static ParameterDirection[] pd =
    new ParameterDirection[1] { ParameterDirection.Input };
readonly static SqlDefinition def = new SqlDefinition(
    "select * from authors where au_id like @au_id",
    CommandType.Text, md, pd);

The variables must be defined in this order, as the SqlDefinition depends on the other variables being initialized first. Or I could initialize them explicitly in the class constructor.

Doing it this way saves me from creating object instances, then throwing them away to the garbage collector. Namely:
1. 1 SqlCommand
2. 1 SqlParameterCollection
3. N SqlParameters where N is the number of parms in the query. SqlParameter actually contains SqlMetaData as a member, plus "other stuff"

Because I'm only executing this ONCE (the first time the class is instantiated) in an entire SQL Server run, is this a micro-optimization? Probably. But what if I have 100 or 1000 such SqlDefinitions? Maybe not as "micro" then... what do you think?

I've been trying out the new session support in HTTP endpoints. You manipulate this through SOAP headers, and looking through the list of SOAP headers supported allowed me to understand this feature from an entirely different perspective. It looks like the session support makes this into more of an alternate client stack than a mechanism to serve web services on the internet or (more likely) intranet.

The HTTP endpoints session support is meant to overcome the limitation that HTTP is a stateless protocol, while TDS/SNI/Netlibs is a stateful one. That is, you normally login to SQL Server perform multiple operations and logoff. With HTTP endpoint sessions, you can do this. There are SOAP headers for "connection string parameters" like initial catalog and application name, there are initiate (login) and terminate (logoff) attributes. There are headers exposing concepts like database transactions and prepared statements. There are even headers for new SQL Server 2005 features like database mirroring partner (for failover) and notification request (for query notifications).

I'll post some samples to show what's possible. They use the SoapHeader definition ("include file") starter (tweaked a bit) from the BOL. Bear in mind that if you define the endpoint on your own machine (you can't come over to my house and use my machine, sorry) you need to update the web reference. Doing so wipes out the SOAP Headers that you need in the proxy class (Reference.cs) to make this work. So don't forget to add 'em back. Save the original code in case you forget. For a more detailed explanation than that, refer to BOL or come to the class and I'll show you how. Or come to Windev and Dan Sullivan will, he's doing the talk on this feature. And have a think about why this is useful, maybe I'll discuss this next.

I was talking with Dan Sullivan this week about UDTs in SQL Server 2005 and he's made an interesting discovery. About a week ago, I'd happened to mention to Dan that UDTs and UDAggs (user-defined aggregates) are supposed to have a pattern of "deserialize-do action-serialize". He thought I'd misunderstood. Then he went off to write an improved UDT/UDAgg for his talk at Windev. Dan has an engineering background and is excited about using UDTs for data types like Linear Dimension (he wrote the LDim example in our book).

What Dan discovered in attempting to implement some initialization inside the constructor is that, inside SQL Server operations, the constructor is never called! His UDT used Format.UserDefined, which means he implements the IBinarySerialize interface to Read and Write the UDT state. The engine just allocates some bare memory for an instance with the IL “newobj” instruction, then calls IBinarySerialize.Read to fill it out. What this means is that any internal fields that you would initialize inside a constructor must also be initialized when IBinarySerialize.Read is called as well. Dan found this out the hard way, reference types he'd expected to be initialized in the constructor were null when Read is called.

He's made a common implementation pattern in his UDTs: always have an Initialize or Init method and call it both in the constructor (for "ordinary" or client usage) and in IBinarySerialize.Read (for use inside SQL Server 2005). A good thing to keep in mind if you're writing a UDT.

When I asked if this wasn't against the "rules", his response was that C# always calls the constructor after newobj, but it's not a .NET requirement. Niels Berglund mentioned that this behavior is also true in Format.Native too, but all its fields are value types, so they are all initialized to zero by the runtime.

Niels also mentioned an interesting pattern he observed by tracing through a UDAgg he'd written with Format.UserDefined.UDAggs implement an Init() method instead of a constructor so that instances can be reused, for example in GROUP BY statements. The pattern Niels observed was:

1. Init
2. Write (but Accumulate hasn't been called yet)
3. Read-Accumulate-Write (for each row processed)
4. Read-Terminate

This pattern had puzzled him at the time, but alongside Dan's UDT observations this makes sense. I'd originally thought that the Read-Accumulate-Write pattern was for parallel processing, but it always happens even on a single processor. At this point we're all thinking it might a hedge based on how thread scheduling works in SQL Server.

So don't forget Dan's "Initialize in Read or constructor" pattern when you write Format.UserDefined UDTs. I think I have the good fortune to work with some pretty observant folks.

As Mark Fussell will tell you, one of the coolest things in System.Xml 2.0 is the static creator methods on XmlReader and XmlWriter. It made his top 10 list, albeit at number 10.

I've been looking into how the client side handles SQL Server 2005's XML data type and was bemoaning the fact that System.Data.SqlTypes.SqlXml had a CreateReader method that probably didn't set what I wanted set - ConformanceLevel.Fragment. Sounded like just the ticket for SQL Server 2005. Checked with Reflector, and lo and behold, they *do* use XmlReader.Create with ConformanceLevel.Fragment. Very cool, and the information was helpful to Kent Tegels on the beta newsgroup as well, just this morning. My research timing karma seems pretty good lately.

Now, I want an overload that looks like SqlXml.CreateReader(XmlReaderSettings). And something that handles sequences of atomic types. Maybe in .NET 2.0 beta2. Hey Mark... maybe I'll hit him up for it at WinDev.

Came across something recently that I thought was odd (or a bug) when dealing with UDTs in SQL Server Management Studio.

If I have a UDT, say ComplexNumber, I deploy it to SQL Server 2005. Use it as a column in a UDT table, complextab. Insert a row:

INSERT complextab VALUES('1:1i')

Then attempt to SELECT all of the values:

SELECT * FROM complextab

I get the following error message:

An error occurred while executing batch. Error message is: File or assembly name 'ComplexNumber, Version=1.0.0.0, Culture=neutral, PublicKeyToken=17177e16a4b86577', or one of its dependencies, was not found.

Scratched my head and thought about it for a few minutes. Reason for this is that SQL Server Management Studio (SSMS) is just another client of SQL Server that uses ADO.NET. In order to use user-defined types with ADO.NET, you must deploy the assembly to the client. So putting the ComplexNumber assembly in the GAC or in the directory that contains SSMS fixes this "problem". It's not a problem, it’s by design. The SSMS will call ToString() ON THE CLIENT SIDE and display the value just fine.

Note that:
1. If you change it to: "SELECT complexcol.ToString() FROM complextab" it works because ToString is being called on the server, not on the client.
2. If you run the same command from SQLCMD it display the binary value of the ComplexNumber column. That's because it uses OLE DB to talk to SQL Server, not ADO.NET. Interestingly, this is what SQL Workbench in Beta 1 did also. Guess the folks at SSMS decided to nicely format it for Beta 2.

I’m a person who always likes to know where things live in the OS, database, or whatever product I’m dealing with. Being able to point to a specific DLL or configuration file or registry entry gives me something tangible to hang my hat on, rather than think that things happen “by magic”.

So I was intrigued by the apparent disappearance of the network libraries in SQL Server 2005. Start with these points:

1. SQL Server 2005 B2 does not ship a new version of MDAC
2. SQL Server 2005 B2 does ship with SQL Native Client (codename was SNAC) with new OLE DB provider and ODBC driver. It also appears to cover network connectivity.  In the new client config utility, all (four) supported protocols list netlibs as “SQLNCLI.dll”. That's SQL Native Client.
3. System.Data.SqlClient does not use MDAC any more
4. System.Data.SqlClient did use DBNETLIB.dll to talk to SQL Server in the past

So does .NET SqlClient use SQL Native Client? Dan, Niels, and I did some experiments and it doesn’t appear that SqlClient calls SNAC at all. We even installed .NET 2.0 on a machine without SNAC and did tests that include MARS and async (these require new protocol features). Worked fine, no SQLNCLI or DBNETLIB loaded or in sight. So how’d they do that?

Looking in System.Data.dll, it appears that there are some calls that start with the letters SNI. There are no calls to DBNETLIB (as there was in System.Data.dll in .NET 1.1). A quick search of Books Online yielded only references to SNI associated with Service Broker protocol (as in “the SNI connection string for Service Broker...”).

So it appears that both System.Data.dll and SQLNCLI.dll implement SNI calls directly rather than calling a specific library. And that the SNI model is used by more than just client-server protocols. It is possibly used for everything that is covered by CREATE ENDPOINT. System.Data.SqlClient doesn’t use MDAC or SNAC, but both System.Data.SqlClient and SNAC are compatible with previous versions of SQL Server, because I could use the OLE DB Native Client provider with SQL Server 2000 just fine. Hmmm.

Stay tuned...

I've been looking at the SMO (SQL Server Management Objects) model recently. Because the amount of data they retrieve is potentially large and potentially expensive to produce (say sp_spaceused on every database), they use most/all of the *data transfer optimization* techniques: both sparse and eager population, lazy instanciation, caching, deferred/chunky writeback, and locality of reference. Reasonable, documented defaults with progammable overrides.

What's this got to do with object-relational mapping? Well, nothing. And everything. These techniques are data optimization techniques. They are the same whether you talk about them in terms of SQL SELECT statements or objects. The fact that pieces of data in a database are mapped to properties of classes in an object model (data model mapping) is only peripherally related to data access optimization, at best.

Before I go forward, I'll state that I don't consider myself an O/R mapping (or XML-relational or XML-object) bigot on either side, either for it or against it. Really. But many folks, especially when they are bashing mapping, use the data transfer optimization techniques (along with another data access concept - locality of reference) to justify their arguments. That's like me blaming the inventor of the knife and fork for my current extra weight. Data optimization, badly implemented, will kill a data-centric object model. But you can accomplish the same thing with bare SQL statements and DataReaders/Rowsets.

For an easy to picture example, imagine paging through a large resultset in ASP.NET. You can:
1. Cache the entire resultset (eager population + caching)
2. Use TOP queries to SELECT on a per-page basis using a custom ASP.NET pager
3. Send the whole bunch to client for paging (eager population + locality of reference)
4. Fetch entire resultset each time and throw all but one-page away. (bad perf).

You can accomplish all this, including the bad design, with DataReaders only, using DataSets for your cache. Not an object-mapped-to-a-relation in sight. Another easy-to-picture example is the Windows file explorer, with plus signs next to each directory. Sparse population/lazy instanciation. The “show details“ setting: sparse property population choice.

Here's a few more quick examples, using ObjectSpaces (technology on-hold, but this was the latest implementation I'd studied) and SQL statements.

// baseline
ObjectReader<Customers> or = os.GetObjectReader<Customer>("");

SqlCommand cmd = new SqlCommand("select * from customers");
SqlDataReader rdr = cmd.ExecuteReader();

// eager population
ObjectReader<Customers> or = os.GetObjectReader<Customer>("", "Orders, Details");

SqlCommand cmd = new SqlCommand(
 "select a.*, b.*, c.* from customers a
  inner join orders b on a.custid = b.custid
  inner join details c on b.orderid = c.orderid");
SqlDataReader rdr = cmd.ExecuteReader();

// sparse population
// customers contains a customer photo
// sparse population in mapping file, can be overriden on query
ObjectReader<Customers> or = os.GetObjectReader<Customer>("");

// leave customer photo out of the SELECT
SqlCommand cmd = new SqlCommand("select customerid, customer_name... from customers");
SqlDataReader rdr = cmd.ExecuteReader();

SO...is mapping among data models a bad idea, or is not knowing/designing for/using the data optimization techniques a bad idea? Mapping objects to data *badly* (or having a model so narrow there are no choices) is just as bad as writing bad SELECTs. You have to know where your data lives, how expensive it is to produce it, and how you are going to use it in ANY case. O/R mapping DOESN'T ABSOLVE YOU from that responsibility. But bad designs are the result of uninformed designers, not a specific technology. Thinking that a specific technology is a panacea and now you don't have to consider design, is also dangerous.

There have been a number of questions recently (well OK, three) on the beta newsgroups about SqlDependency problems. It's been suggested that SqlDependency doesn't work in Whidbey Beta 1.

The reason for this is that SqlDependency (and, of course, its lower-level cousin SqlNotificationRequest) requires a “valid” notify-able SQL query to work. What's a “valid”  notify-able query for a NotificationRequest? Subscriptions for query notification in SQL Server 2005 use the same underlying mechanism to be notified of resultset changes as indexed views do. Therefore the rules are the same as the rules for indexed views.

Since the example in our “First Look at SQL Server 2005 for Developers” book also uses an invalid query (it doesn't use a two-part table name), I posted the rules for indexed views (and therefore for SqlNotificationRequest/SqlDependency on the book website, under “changes since the book shipped/chapter 12”. It seemed that you could “get away with” not always following the rules until SQL Server 2005 Beta2. The list of rules is from the SQL Server 2005 Beta 2 Books Online.

By the way, if you submit an invalid SELECT statement with a SqlDependency, you'll get an immediate notification with the reason (in SqlNotificationEventArgs) “invalid”.

Just when I thought I'd found all the new cool features. An Ascend Phase 1 participant once asked me “can the DBA get rid of unwanted query notification subscriptions”? In Beta2 you can.

SELECT * FROM sys.dm_qn_subscriptions

-- pick the ID of the subscription that you want, then
-- say its ID = 42
KILL QUERY NOTIFICATION SUBSCRIPTION 42

Cool. Your subscriber app doesn't appear to get notified its been killed, though, (as it does when almost anything else affecting the subscription happens) either when using SqlDependency or SqlNotificationRequest . That's why this should only be used for “pesky” subscriptions.

Sometimes you have to hit me over the head to make me aware that something's changed. I've been working with Visual Studio 2005 beta 1 for about 10 days now, and just noticed that creating a SQLCLR project (that's [Language of Your Choice]/Database/SQL Server Project) no longer includes a reference to Microsoft.VisualStudio.DataTools.SqlAttributes.dll.

This was the DLL that included deployment attributes for Visual Studio to use when it auto-deployed its SQLCLR assemblies to SQL Server. I only realized this when I saw the [SqlFunction] attribute decorating my new user-defined function, created by Add/NewItem. No more explaining the difference between [SqlFunc] and [SqlFunction]. Hooray! Besides combining those two attributes, all of the attributes are now in System.Data.dll in the System.Data.Sql namespace. And there's a new one (knew about that), SqlFacet.

There are some other interesting things which bear mentioning:
1. The SQLCLR classes generated with add/new item are now partial classes in C#. There's a nice MSDN article that talks about what partial classes are.
2. The security level of the assembly (SAFE/EXTERNAL_ACCESS/UNSAFE) is now a setting in project properties. No more hardcoded EXTERNAL_ACCESS.
3. "Add References" produces a dialog with a subset of base CLR assemblies; they look to be only the CLR assemblies that are "allowed to load" in SQL Server. All other assemblies must be referenced through the "Project" tab, if you want them.

The only drawback to this is that it requires that I write a few more entries in the
"SQL Server 2005 changes since book publication (by chapter)" pages on the "First Look At SQL Server 2005 for Developers" website. Fell off the bleeding edge again, geez. But the degree of improvement in the changes make it worth it.

When you get a brand new tool, programming language, or API, the first thing most programmers use it for is to write their favorite "rite of passage" program. This is an example that is so simple, it's only purpose is to illustrate the that compiler or tools are installed and working correctly and that "the system knows about them". One of my old friends would call them "the canonical examples".

Two of these that I always revert back to are "Hello, World" and "Add two numbers together". Hello World is the first thing that you write with any new programming language. Popularized by K&R, perhaps, maybe before. Add two numbers together is the starter distributed technologies example, the "original" DCE/RPC came with this one, on the premise that, if you had to add two numbers together, it would be quicker to do it on the Cray Supercomputer across the world than to entrust it to the wimpy little CPU on your workstation. Another such program I'd just heard of (so I'm culturally deprived), via a Mark Fussell blog posting is the "99 bottles of beer" program, especially useful for recusrive languages. And of course, my habit of writing a new data provider when the model changes.

Being a database programmer, when using SQL Server, the rite of passage program is to do what amounts to "select * from authors" in the pubs sample database. I was saddened to hear that the pubs sample database wouldn't ship by default with SQL Server 2005, but already had my "instpubs.sql" script stashed away for a rainy day.

Here's my three favorite "rite of passage" programs, written as SQLCLR stored procedures/user-defined functions.

[SqlProcedure]
public static void SayHello()
{   // the 'H'  and 'W' must be capitalized. Exclamation point is required.
    SqlContext.GetPipe().Send("Hello World!");
}

[SqlFunction]
public static SqlInt32 AddTwo(SqlInt32 x, SqlInt32 y)
{
    return x + y;
}

[SqlProcedure]
public static void GetAuthors()
{
   SqlCommand cmd = SqlContext.GetCommand();
   cmd.CommandText = "select * from authors";
   SqlContext.GetPipe().Execute(cmd);
}

and the T-SQL to invoke them:

USE pubs
GO

EXECUTE SayHello
GO

DECLARE @answer int
SET @answer = dbo.AddTwo(2,3)
PRINT @answer
GO

EXECUTE GetAuthors
GO

About a month ago, there was another chapter in the long discussion about DataSets and web services. An old cohort Scott Hanselman fired off the first salvo, which was taken up by Ted Neward. Doug Purdy responds that Purchase Orders are root of all evil and lots of other chimed in. I'm just catching up...

This comes up again as I work with SQL Server 2005 web services. They too produce “SqlRowset == Schema + Any" in WSDL. Both XML and object afficianados tend to dislike this, but it exactly reflects the relational resultset, rowset, whatever you'd like to call it.

Relational results are columns and rows, where each row contains the same number of columns and each column contains the same data type for each row. Been around for a while now. The resultset is a "generic container class" or special 2-dimensional array if you want to think in those terms, in which each instance can contain different types, but the shape is rectangular. Resultsets, looking at an Open Data Services program which makes the protocol explicit, are preceeded by a sequence of describe packets that contain the name, data type, and other information about each column. So "Schema + Any" means exactly that. *Instance* schema, followed by the instance it describes. Corresponds quite nicely with relational databases, where the majority of corporate data is stored.

The problems stem from the fact that lots of web services toolkits, that are doing XML-to-Object mapping, don't recognize this paradigm. They are looking for an individual (static) schema, not an instance (dynamic) schema. In addition, the XML Schema spec states that how you find a schema from an instance of an XML document is implementation dependent. It mentions a hint (xsi:schemaLocation) and some location strategies for schemas with namespaces that are URLs, but says processors don't have to support the hint. Most WSDL that uses "Schema + Any" doesn't provide the hint. With Microsoft XML processors, this may be because "Schema + Any" is part of the implementation, an "implementation dependent" location strategy.

There's a few ways to resolve this, if you're looking for strongly typed objects at compile time. One way is strongly-typed DataSets, which fetch out the information at compile time, make a DataSet wrapper class, and, for web services, write WSDL that amounts to "Any from a specific namespace", and import the namespace schema, which includes the strong type. Another way is to write your own WSDL for each resultset.

If you write a stored procedure in SQL Server that looks like this:

CREATE PROCEDURE getauthors
AS
SELECT au_id, au_fname, au_lname FROM authors

you know exactly what that resultset will look like. Unless the DBA changes the column type, in which case you'll have other problems. Even if you use the "SELECT * FROM ..." concept (which is not usually recommended) this works unless the DBA adds or deletes a column, or changes the order of definition. I haven't been able to find a metadata table in SQL Server (or in the ANSI/SQL spec) that lists the shape of all resultsets returned by stored procedures (there is one for *parameter* information) , so you have to use your knowledge of the resultset to handcraft the WSDL. In ADO.NET, you can use the DataSet's "WriteXmlSchema" method as a starter. Or use "SET FMTONLY ON;SET NO_BROWSETABLE ON;EXECUTE...". SQL Server 2005 web services that map stored procedure invocation to web service methods will let you specify a custom WSDL-generating stored procedure. Statically store the metadata (instance schemas) in the database or dynamic generate them at WSDL generation time.

BTW, this mapping of resultsets to XML schemas amounts to relational-to-XML mapping. So web service toolkits can accomplish their XML-to-object mapping. I'll return to this theme in future...

If you've recently installed the SQL Express version of SQL Server 2005, you may or may not have noticed SQL Native Client (SNAC). If you want to use the new features of SQL Server 2005 like multiple active resultsets or snapshot isolation from OLE DB, ADO, or ODBC, you're going to need SNAC.

SNAC is meant to separate SQL Server's OLE DB provider and ODBC driver from MDAC. Currently the SQL Server OLE DB provider (SQLOLEDB) and ODBC driver ship as part of MDAC (Microsoft Data Access). A new version of Microsoft Data Access (MDAC 9.0) was planned in order to support the new SQL Server 2005 functionality. MDAC 9.0 won't happen. Instead, the current components of MDAC (2.8 SP1?) will ship as part of the Windows family of operating systems. MDAC will change infrequently, changes will not be tied to new SQL Server functionality. SNAC will be versioned with new versions of SQL Server and ship separately.

Once you've installed SNAC, you should see a new OLE DB provider (SQL Native Client) and a new ODBC driver (SQL Native Client). You need to use them instead of the older versions to get the SQL Server 2005-specific functionality. Note that they need to be coded into the connection string (OLE DB, ADO, or ODBC) or ODBC DSN. The SQLNCLI provider does not support XML like the SQLOLEDB provider does.

If you're using ADO.NET's SqlClient data provider, it has been rearchitected so it doesn't use MDAC. Or SNAC, as far as I know. If you've used SqlClient with SQL Server 2005 beta 1, you won't need "USE MDAC9" in connection strings as MDAC 9.0 isn't shipping with .NET 2.0 beta 1/SQL Server 2005 beta 2. SNAC is provided for vendors that still use OLE DB, ADO, and ODBC in the products. Nice feature. I have a soft spot in my heart for OLE DB (having taught provider writers for a few year), and having MDAC as a part of the OS is overdue. Not shipping MDAC for SQL Server changes will be a good thing.

I was checking some of the SQL Server 2005 features on the way over on the plane. The latest build that I have is SQL Express build, fairly recent. Many of the XQuery features have been updated to Nov 2003 XQuery spec. Very cool. But comments wasn't one of them. A nit, I know.

The {-- a comment --} style comments still work when using SQL Server XQuery but the new style, smiley-face comments (: a comment :) don't. They generate a syntax error.

Now I *liked* the old style comments (don't like to see my code grin at me, call me sensitive), but the smiley-face ones are part of the spec revision, unless I'm missing something. And they are supported in the client-side System.Data.SqlXml's XQueryCommand dialect of XQuery. That's even more puzzling because the two dialects (SQL Server and System.Data.SqlXml) are synchronized in other ways. For example, System.Data.SqlXml no longer supports the XQuery “LET” verb and has compile-time strong type checking like SQL Server.

I lag in reporting things I find, because I know beta 2 is still a work in progress. Thought it would be added. All of the book examples have the smiley face comments, just do “find and replace” if you run 'em. Beta 2 still isn't out yet, maybe... :)