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 seem to have lost the gift, if I ever had it, for what some of my old collegues would call (with a smile on their face) "shameless self-promotion". Last week at Win-Dev attendees at my talks pointed out to me that I didn't have a slide that advertised my email address, or web site of samples, or this blog. I didn't even have a slide plugging our SQL Server 2005 For Developers book, even. Sheesh... So I crufted up a slide for day 2. I even forgot business cards. Yep, really.

Reason this topic comes to mind is, when I mention you can find more information of some of the new SQL Server 2005, SqlClient and ADO.NET 2.0 topics in an article series I wrote for MSDN online (Data Access and Storage Center), folks ask for references. Here they are:

1. ADO.NET 2.0 Feature Matrix
2. Generic Coding with the ADO.NET 2.0 Base Classes and Factories
3. Edit/Build/Publish: Schemas in ADO.NET 2.0 - This is about DbConnection.GetSchema(), a.k.a. information schema functionality ++
4. Using Query Notifications in ADO.NET 2.0
5. XML Data Type Support in ADO.NET 2.0: Handling XML from SQL Server 2005

There's one more article in the series coming in another month or so. I'll keep the suspense going for now.

I've also built my "do-very-little-for-exposition" .NET Data Provider using the new ADO.NET 2.0 provider base classes (System.Data.Common and System.Data.ProviderBase) , a ProviderFactory, and provider “registration”. An article about the process of building got published in MSDN magazine, a few years ago; this is an update.

There. Was that too shameless for ya?

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?

Got back from Win-Dev last night...and immediately crashed. It was a great show, a little exhausting because some of the folks that I'd lined up for the data track bowed out and I did some alternative talks. This is the first year Win-Dev had a database-data access centric track and, if the comments were any indication, folks liked the idea a lot.

Interesting thing happened at Win-Dev...because I made a comment about having worked with IMS and IDMS (well-known mainframe databases that do not follow the relational model), someone asked how old I was. I did the math (born in '53) and came up with 52 years old. Well...I'm really 51 and the point is that I'm doing computations as if the current year *is* 2005 already. Turns out that I've been teaching SQL Server 2005 and Visual Studio 2005 for a while and have started thinking it's already 2005. I live in the future, I guess.

Two more months and I'll be right...and sometime after (well, within the year) these products will ship. And I'll be able to compute my age quickly again.

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.

Made it to this conference, came a day early just in case (see I'm at SQLPass...not). No hurricanes at this one, but fairly nasty cold and rain yesterday.

Conference is in Boston (well, actually Quincy, MA) this week. We have a database track this year, and I'll be doing a day of SQL Server 2005 / ADO 2.0 and some related talks as well. When the taxi pulled in to the hotel parking lot I was surprised to see a fleet of police motorcycles and a big crowd in the lobby. Rumor was that the baseball players were staying here since the world series is in town this weekend. Lots of partying after the games in any case.

The lobby was packed all weekend and since I saw someone signing autographs for a few children (this doesn't usually happen at software conferences that I'm aware of) looks like the rumors were true. Things ought to quiet down a bit since everyone's on their way to St Louis today. Or...all the action will be in the conference rooms rather than in the lobby.

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.

The new SNAC (SQL Native Client) OLE DB provider handles naming of parameters a tad differently than SQLOLEDB. Let's say that I have the parameterized query (using the ODBC-style question mark parameter markers):

SELECT * FROM authors WHERE au_id LIKE ?

And I call OLE DB's ICommandWithParameters::SetParameterInfo to set some parameter information. SetParameterInfo takes a parameter name as one of the parameters. In the case of the question mark parameter, what should I put for the name. Using the SNAC OLE DB provider and using a dummy name (say..."Dummy") produces the error:

DB_E_BADPARAMETERNAME - "Parameter name is unrecognized"

In SQLOLEDB the error is not produced. Difference between providers.

Looking at the OLE DB specification, although it's different behavior, it appears that the SNAC provider is "more correct". Here's the first part of the description of DB_E_BADPARAMETERNAME.

DB_E_BADPARAMETERNAME
In an element of rgParamBindInfo, the pwszName element specified an invalid parameter name. The provider does not check whether the name was correct for the specified parameter, just whether it was a valid parameter name.

SQL Server supports named parameters and positional parameters. Positional parameters have no name. Using a null pointer as the parameter name works, both in SNAC and SQLOLEDB. If you use named parameters in a SQL statement the parameters must start with an at-sign (@) like this:

SELECT * FROM authors WHERE au_id LIKE @auid

So valid values for SQL Server parameters are "null pointer" (no name) and names that start with at-sign. Names that do not start with at-sign SHOULD produce an error. Perhaps SQLOLEDB's forgiving behavior was to accomodate conversion from the OLE DB provider for ODBC (MSDASQL), where anything could be used as a parameter name. Nonetheless, it appears that SNAC provider's behavior is "more correct" they are checking that parameter names begin with at-sign.

Interestingly using either null pointer or @anyname as a parameter name works (ie SetParameterInfo does not produce an error) when you use SNAC. Even if its the wrong name. Note the spec says "The provider does not check whether the name was correct for the specified parameter, just whether it was a valid parameter name." Correct behavior here too. Although if you are using named parameter and use the *wrong* name, SetParameterInfo will work but ICommand::Execute will fail.

So, there are a few provider differences that I've found so far. If you're writing a new program or library, you should start with SNAC provider. To use the new features in SQL Server 2005 like MARS and Query Notifications, you MUST use SNAC (or SqlClient and ADO.NET of course). But be sure to test existing programs before making the switch.

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

Just finished watching Ichiro break the single season hit record on TV. I'm a Seattle Mariner fan from way back when, and a big Ichiro fan. Most likely some of the students from my class in Tokyo this summer were watching it too. The announcer said it was on lots of channels there. This summer I realized they got a lot of live/taped Mariner games on TV in Tokyo along with many Japanese baseball league games by watching TV in my hotel. Congratulations to Ichiro! But it reminded me of my world's worst marketing idea.

It was 2002, I think, middle of the post-year-2000 high tech bust and we were trying to find ways to increase class attendence. At the time, we had many "high-profile" instructors (MSDN called them "rock star speakers" this year).

I'd just seen Ichiro bobble-head doll night, where folks lined up around the block to get into the ballgame because they were giving away this collectable. The Ichiro bobble-head doll given away for free that night supposedly sold for hundreds of dollars on EBay the very next day. They should even be worth more now.

My idea was to give away bobble-heads of all the DM instructors with a very limited number of the "high-profile" ones. One doll/student at each class. After watching folks line up for collectables, I figured that this actually might help sell classes. Sort of like the attendees you see wandering around PDC with armloads of SWAG. I once worked with someone that joined IEEE at a conference just to get the free coffee cup. Since there would be less copies of the "high-profile" ones, they'd have to come back again and again. I was so enamored with the idea that I even found a place on the internet where they could make them (personal collectables) up.

My cohorts didn't think so highly of the idea when I posted it to our internal mailing list. A few of them couldn't stop laughing over the outragousness (or ridiculelessness) of it, most just ignored me. But my favorite comment was from Craig Andera, an amazingly bright guy and very witty; for some reason not that high-profile. He wrote to me "I can just see my students unwrapping their collectable and saying "damn, I got another Andera, I got 5 of these. Anyone want to trade?" Cracked me up.

Beginning and end of my career as a marketer. And why programmers (at least most of us) shouldn't be marketers.

Last week I promised some of my students that I had an article in the works on the intricacies of SQL Server 2005/.NET 2.0 Query Notifications, both from a server implementation and client consumer point of view. Yesterday I found out that the article “shipped” to (was officially posted on) the website. You can find it here. This article is part 4 of a 6-part series on ADO.NET 2.0/SQL Server 2005 I'm writing for MSDN Data Access and Storage Developer Center. The other articles might be useful to ya' too...

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.

My apologies to anyone who showed up at SQLPass this morning for the pre-pre-conference talks, which was supposed to include a day of SQL Server 2005 for developers given by me. If you were there, you probably noticed that I wasn't. A storm called Hurricane Jeanne saw to that. The Orlando airport was closed from 5pm Saturday to noon today. Thought I'd be arriving too early (Saturday night) but I wanted to make sure I'd make it. That wasn't soon enough. The talks may be scheduled for some other venue, but not at SQLPass. The rest of the conference is proceeding though, starting with Tuesday's preconference.

I've never had to cancel a conference or class before. The closest was interestingly, a VSLive in Orlando. I had a Sunday flight for a Monday talk. Portland to Denver to Orlando. Only someone left a door open at PDX, leading to the evacuation of the airport. Planes were all at least 2 hours late, but I cruised into Denver with confidence, knowing I had a “direct-with-one-stopover” flight. However, the Denver-to-Orlando portion was cancelled and I received a ticket for the next day. Sigh, I called and cancelled.

When I went back to arrange for tickets back to Portland and told the person at the counter my tale of woe, she said “there's another alternative but you probably don't want it”. The previous person had told me there was no alternative. Oh. The alternative was to fly from Denver to LAX and then to Orlando on a red-eye. Arriving 6:15am. I jumped at that one, called and un-cancelled, and arrived at 7am for a 10am talk. And I don't sleep on planes at all.

After that incident, I always arrive an extra day early, to “be sure“. Saturday, running through possibilities and remembering last time, I even checked the LAX-to-Orlando red-eye for Monday morning. It was cancelled too.

So I'm home in rainy Portland OR, in the rainy Pacific Northwest. Last SQLPass I attended was in rainy Seattle, where folks complained about the weather. It's going to be over 80 degrees today and sunny here all week. Oh well...

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.

After a summer of being a homebody, fall and winter will be *lots* of traveling. I'll be doing the day of SQL Server 2005 for Developers at SQLPass, as a pre-pre-conference talk (a day before the pre-conference) later this month. And Ascend 2, in at least three continents, so far.

Interestingly Ascend 2 starts almost exactly a year after Ascend 1. Both started about the same time as our fall trip to Oregon Shakespeare festival in Ashland. Last time around Dan was writing labs and wrote an ObjectSpaces lab (there, I've said the 'O' word) around object queries over a relational-ized version of the XML rendition of the collection of Shakespeare's plays. Query was pretty fast, considering how many plays he wrote. This year it will be XQuery and recursive CTEs, although these were around last year too.

I'm actually taking a little light reading on the trip, Michael Brundage's XQuery book (reading it again for detail) and also Joe Celko's new "Trees and Hierarchies in SQL for Smarties" book. I'm meeting up with Joe at WinDev, and I'll need to have read the book; hope there won't be a test. Listening to both sirens of the relational trees vs. XML siren song. I'm not sectarian about this.

OSF is non-sectarian as well, counting Bill Joy and Bill Gates among it's donors. And us, Mary and me.

Off to the land of planes, trains, and automobiles, perhaps I'll see you on the road...later.

Thinking ahead to the SQLPass conference and also back a few weeks to the Ascend Airlift. Almost forgot that at the airlift I met Angel Saenz-Badillos for the first time in person. He introduced himself at an Ask the Experts session; I was doing the asking. He's been writing about ADO.NET 2.0 and gave me a lead on a transaction investigation project he was doing. It's on my "long list" but hasn't made it to the short list yet. I was reminded because he's on my Blogroll to the right. Check out his blog.

Interesting how you "converse" with folks on newsgroups for years without actually meeting the person you're conversing with. I'd met up with Angel years ago on microsoft.public.dotnet.framework.adonet; we were both answering questions on all things ADO.NET, but seemed to be the only ones answering Oracle questions. He was supporting the Microsoft OracleClient provider, I was trying them all (ODP.NET, OracleClient, and DataDirect.NET Oracle) and answering folks' questions. Now we're both working on SQL Server 2005 and crossing paths again. This time around .NET is used in the server as well as on the client. It was good to be able to say hi for real.