People are sometimes concerned with the lifetime of temporary tables they create in CLR code. As an example, if I use a parameterized statement in .NET code, the SqlClient provider wraps the statement in exec sp_executesql. If the statement batch includes something like "CREATE TABLE #temp1" as part of the batch, the table #temp1 is no longer visible when the batch returns. This is because the lifetime of a temporary table created inside of a stored procedure is the stored procedure itself, ie. the temporary table no longer exists once the stored procedure completes. In this case, the stored procedure that's scoping the temp table is "sp_executesql". But... if proc A creates a temporary table and then (inside proc A) calls proc B that reads it, that's fine. Because nested procs can see "temp" tables created within outer scopes.

But what about procs written in SQLCLR? If you're using the context connection in your SQLCLR proc, visibility in the inner proc works fine, regardless of whether:
procs A and B are written in T-SQL
procs A and B are written in SQLCLR
proc A is SQLCLR and B is T-SQL
proc A is T-SQL and B is SQLCLR

Hope that clarifies things...

I finally got around to install .NET 4.0 CTP today on a SQL Server box to test a long-held theory. Didn't quite work out the way I'd thought.

When .NET support was introduced in SQL Server 2005, there was a lot of interest in how SQL Server and .NET Framework would approach the versioning story. The story was that SQL Server would always load "the latest version of the .NET runtime installed on the machine". SQL Server 2005 shipped with .NET 2.0.50727.42 and since then, there's been a number of Service Packs and even new releases of .NET, including 2.0 SP1, 3.0, 3.5, and 3.5 SP1. SQL Server 2008 shipped with a pre-requisite of .NET 3.5 SP1. And, up until this point, SQL Server has always loaded the latest version of the .NET runtime. That's because there is no choice.

The basic bits of .NET reside in mscorlib.dll and System.dll. Through all of the releases after SQL Server 2005, those bits are still in the "version 2.0" directory. The 3.0 and 3.5 version don't install a side-by-side version of mscorlib (or System.dll, or System.Data.dll or most of the "core" .NET framework). These are versioned by updated the 2.0 versions in-place. .NET 3.0 and 3.5 directories exist, but they only contain additional libraries, like WCF and LINQ for example. The current version of the core .NET framework at the time of this writing is 2.0.50727.3053.

Enter .NET 4.0, which includes side-by-side new versions of mscorlib.dll or the rest of the entire core. When the .NET 4.0 CTP is installed on a machine that includes SQL Server 2008, the version of .NET loaded by 2008 is 2.0.50727.3053 (sys.dm_clr_properties reports 2.0.50727), not 4.0. Specifying 4.0 in a configuration file will force SQL Server to load the 4.0 version, but then attempting to create and initialize a variable of type "geography" fails with the message "Method's type signature is not interop compatible".

It will be interesting to see how this plays out as .NET 4.0 gets closer to becoming a reality.

A friend of mine was asking about the affect of multi-targeting in Visual Studio 2008 on SQLCLR. For an explanation of how multi-targeting works, reference David Kean's blog entry on Visual Studio 2008 multi-targeting and FXCop.

I happened to have an instance of SQL Server 2005 RTM (which uses .NET framework version 2.0.50727.42) on hand to try. I took two assemblies as a test. Just for fun, one assembly contained a user-defined function that works on SQL Server 2005, but not on SQL Server 2008 (reference my blog posting on 'Semantic (possibly breaking) change in SQLCLR TVFs') but contains NO new 3.5-specific functionality. It simply reads the event log and returns a table in a SQLCLR TVF. One assembly used System.DateTimeOffset (a type in 2.0.50727.3053 but not in 2.0.50727.42) internally (ie, not as an input or output parameter, but inside of a method I intended to try as a SQLCLR UDF).

I compiled both assemblies on a system with Visual Studio 2008 (.NET 2.0.50727.3053) targeting the .NET Framework 3.5. As an aside, when I tried targeting .NET 2.0, running code analysis gave a warning about DateTimeOffset. Move both 3.5-targeted assemblies to my SQL Server 2005 RTM system.

The assembly with no 3.5-specific functionality cataloged and ran fine in SQL Server 2005 RTM. Attempting to catalog the assembly that included DateTimeOffset failed CREATE ASSEMBLY with the error message:

Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'UseDateTimeOffset' failed because assembly 'UseDateTimeOffset' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message [ : UserDefinedFunctions::GetTimeSpan][mdToken=0x6000001] Type load failed.

where UseDateTimeOffset.dll is my assembly and UserDefinedFunctions.GetTimeSpan is the public static method that would have used it.

Hope that clarifies things. You can use 3.5-targeted assemblies on SQL Server 2005 as long as they don't use functionality (the type System.DateTimeOffset is in mscorlib.dll) that don't exist in the SQL Server 2005 machines's version of .NET.

BTW, you CAN use any of the new .NET functionality in SQL Server 2005 as long as you install .NET 3.5 on the SQL Server machine. Obviously that doesn't mean you can use DateTimeOffset as a stored procedure/UDF parameter, but you can use it inside a stored procedure called by SQLCLR. Just like you can use Array in your implementation, but not expose it to T-SQL.  I mentioned one cavaet to installing .NET 3.5 on a SQL Server 2005 machine in a previous blog posting earlier this year. Also, SQL Server 2005 SP2 won't recognize System.Core.dll as a "safe" assembly (as it is in SQL Server 2008) but that's to be expected.

Although being able to use a GUI tool like SQL Server Management Studio is a nice feature by itself, it's unusual that fixing something in a GUI makes a feature (that hasn't changed) more useable. The "feature" is defaults on SQLCLR procedure parameters.

You can't have defaults on parameters in .NET, but you can specify them in the CREATE PROCEDURE DDL, for example:

CREATE PROCEDURE addwithdefaults (@x int, @z int out, @y int = 5)
AS EXTERNAL NAME sampleasm.StoredProcedures.AddWithDefaults

These work just like defaults in T-SQL procedures; if you don't specify the parameter, the SQLCLR code receives the default value when it is called. Dandy...it's always worked this way.

But, in SQL Server 2005 SSMS (and SMO) the SQLCLR procedure was always displayed as "No Default". Even though there's perfectly good information in the SQL Server metadata about the default. Some folks were convinced that, even though default parameters worked as they should, it was dangerous to use them. Say a DBA was to move the procedure from test to production by scripting the CREATE PROCEDURE statement from SSMS (or SMO). They'd lose the default and when the procedure was defined with the generated script, code that worked in test (depended on the default value) would break in production.

I just noticed in SQL Server 2008 SSMS that defaults ARE displayed and procedures ARE scripted correctly. Great. Let a thousand SQLCLR procedures with default parameters bloom...I guess.

When SQL Server 2008 shipped, Visual Studio 2008 SP1 and .NET 3.5 SP1 shipped a few days later. Visual Studio SP1 contains some neat enhancements that allow you to use SQL Server 2008 databases in Server Explorer and the related designers. There's support for SQL Server change tracking in the Sync Services designer. The EDM and LINQ to SQL designers know about DateTimeOffset (datetimeoffset) and TimeSpan (time) in SQL Server 2008. Server Explorer also knows about geometry, geography, and hierarchyid. Very nice.

I often use the SQLCLR projects (that's Database Project under your programming language) that provide templates, auto-deployment of assemblies and CLR-based database objects, and an "Add Reference" dialog that allows adding references only to system libraries that are classified as "safe to use" (i.e. have been tested with SQL Server) and assemblies that already exist in your auto-deploy target database. Makes the base functionality more accessible to programmers. When I wrote samples of SQL Server 2008 functionality I didn't use those projects, because they didn't yet support some of the new functionality. So I thought I'd go back in and check.

Another reason I checked is because I sometimes get feedback from programmers on newsgroups/forums that, if its not exposed in Visual Studio (or in SSMS Object Explorer to give another example) the base functionality must not actually exist. You CAN program SQLCLR objects that use ALL of these features...just use an ordinary Class Library project and write your DDL by hand.

First thing I noticed is that there is no special SQLCLR project for C++. In VS2005 it was called "SQL Server Project" but in VS2008 it's gone. That leaves us with projects for C# and VB.NET. But not a lot of people coded SQLCLR in managed C++.

Next, I tried out my SQL Server 2008-specific features. Here's a rundown.
  The new supported libraries System.Core and System.Xml.Linq don't appear in Add References dialog.
  Autodeploy doesn't recognize the system .NET-based data types SqlHierarchyId, SqlGeometry, SqlGeography.
  Autodeploy knows about the mapping of DateTimeOffset in SQL Server 2008, but doesn't know about TimeSpan to SQL Server's time data type.
  Can't autodeploy a multi-input user-defined aggregate.
  Can't autodeploy a large user-defined aggregate (in fact it doesn't seem to like any UDAgg with Format.UserDefined).
  Autodeploy always maps the .NET type DateTime to SQL Server's datetime. But in 2008 DateTime can map to SQL Server's datetime, datetime2, or date. Perhaps an enhancement to SqlFacet.
  I can't specify "order by" for an ordered TVF during autodeploy.
  Using a nullable type as a parameter fails in autodeploy.

Bear in mind that these SQL Server 2008 features do work as advertised if you use Class Library projects and manual deployment (ie, roll your own DDL).

I did file a bug on Connect for these. Maybe it should have been an enhancement request instead, but I really thought SQL Server 2008 support would include these and they just weren't in the beta yet.

Folks have always had trouble with the fact that ring orientation is required with spatial instances if you're using SQL Server 2008's geography data type, but not with the geometry data type. For an explanation of the need for this, reference Isaac Kunen's blog entry here and Ed Katibah's blog entry (link in next paragraph).

In Ed's blog entry, he provides a neat way to fix spatial instances that have the wrong ring orientation for geography by using the geometry type and calling a method that forces the required ring orientation for geography. You should read his entry from yesterday for background.

I thought this was such a cool mechanism that I coded up a SQLCLR UDF that encapsulates this behavior. It accepts almost any WKT and produces a "proper" geography type, regardless of the ring orientation in the WKT. The code is included with this post. The function is called GeographyFromAnyWKT. Well, almost any. There are certain edge conditions (such as a ring that actually would exceed a single hemisphere) that will produce and error, but at least you shouldn't have to worry about ring orientation. Thanks Ed!

As an aside, although this is a Visual Studio SQLCLR autodeploy database project, the function can't be autodeployed because it returns a SqlGeograhy type and this is not covered by the autodeployer code. So I've included a deploy script and a couple of test cases in the project. Although I define the UDF as RETURNS NULL ON NULL INPUT, I've also included (redundant) null checking in the function itself, just in case you want to change the function a bit. Enjoy!

I came across the following interesting behavior while testing a SQLCLR table-valued function that did work in SQL Server 2005 SP2 but doesn't work at all in SQL Server 2008. It appears to be by design, because the error message (in 2008) clearly indicates what's wrong. But the code worked in SQL Server 2005 and if you depend on this code behavior, it's a breaking change. And I haven't seen this in a readme file or BOL "What's New" section.

First, some background. .NET procedures are allowed to do any kind of "data access" including getting the Windows Identity, accessing the local database instanace, etc. .NET user-defined functions, however, are not permitted data access unless they are marked with a SqlFunction attribute specifying DataAccess=DataAccessKind.Read. Accessing certain session information requires SystemDataAccess=SystemDataAccessKind.Read as well.

.NET table-valued functions consist of a UDF function method and also a FillRowMethod. The UDF function method must return an instance of a .NET type that implements IEnumerable or IEnumerator. This can be a class that you provide or one of the build-in .NET types such as System.Array. SQL Server will call its Enumerator and call back to FillRowMethod once for every time the enumerator returns true.

In SQL Server 2005, you can do "data access" in the UDF method, the FillRowMethod, or any of the other methods in the class (like the enumerator's MoveNext method). Only the UDF method must be marked DataAccess=DataAccessKind.Read and only the UDF method CAN be marked with the SqlFunction attribute and produce the desired effect.

In SQL Server 2008, attempting to do data access in the FillRowMethod now throws an exception. Perhaps the behavior change was required to implement a new SQLCLR feature, ordered TVFs, but I'm only guessing that ordered TVFs are the reason. Perhaps it was never intended to work. The error message in 2008 is pretty clear:

"System.InvalidOperationException: Data access is not allowed in this context.  Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method."

There's no workaround for this if you choose a table-valued function, except to do all your data access in the TVF method only. But SQLCLR provides another way to way to stream a rowset of data you synthesize yourself, using SqlMetaData, SqlDataRecord, and SqlPipe methods in a SQLCLR stored procedure. You can get almost the same result (streamed rowset) in such a stored procedure and "data access" is always allowed in SQLCLR stored procedure code.

When SQL Server creates a CLR appdomain to run code in (a runtime appdomain as noted in the previous blog entry), the appdomain normally stays in place for the lifetime of SQL Server. This is done to save appdomain create/teardown and assembly load time. Note that DDL appdomains, as opposed to runtime appdomains, are torn down immediately after they are used. A friend of mine recently wanted to shutdown an appdomain on purpose to troubleshoot a problem that he thought might have been SQLCLR-related. So how do you shutdown a runtime appdomain on purpose?

You could write a .NET proc to call AppDomain.Unload. But I shied away from this for a few reasons. You'd need to catalog the appdomain as unsafe for the proc to work, which means marking database as trustworthy or doing the 'signed assembly with key in master' dance. And I'd really prefer a way to have SQL Server gracefully shutdown the appdomain itself.

SQL Server will shutdown an appdomain for different reasons. It can shut them down under extremely low memory conditions or when there is a serious enough unhandled exceptional condition (e.g. unhandled exceptional condition that could leave .NET locks in place). We really don't want to cause either of these on purpose just to shut down an appdomain. Another reason the SQL Server will shut down an appdomain is when a loaded assembly is altered. You can use the ALTER ASSEMBLY DDL statement to replace code in place, subject to limitations. When you alter an assembly in place SQL Server recycles the appdomain to be able to use your new code. Currently executing code will continue to use the appdomain until the call completes; new requests are routed to the new appdomain (with the updated code). When all current requests against the old appdomain complete, the appdomain shuts down. Hmmm, probably not a good idea to muck with recompiling the producting code either. So...

Compile a simple do-nothing assembly with a simple do-nothing function (say, add two numbers together). We'll call the assembly 'fred' and the function 'addtwo'. The assembly must be owned by the same owner as the appdomain you want to recycle (remember runtime appdomains are on a per database and assembly owner basis). So if the appdomain we want to recycle is the 'pubs.dbo[runtime]' appdomain...

use pubs
go

create assembly fred authorization dbo ...
create function dbo.addtwo ...
use the function dbo.addtwo (this causes the assembly to be loaded)
recompile the assembly fred
alter assembly fred ... (this cause the eventual appdomain unload)

Note that you don't have to change the 'fred' assembly, only recompile it. SQL Server decides that an assembly is changed if it has a different MVID (.NET assembly module version identifier). New MVIDs are assigned each time an assembly is recompiled (note that an MVID is not the same as a four-part assembly version number). In Visual Studio, you'd recompile the assembly by using the Recompile menu entry, not the Build menu entry. If you try and run alter assembly without a rebuild you'll get the error message 'ALTER ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "fred"'. You could conceivably use Visual Studio autodeploy for these steps as well, but Visual Studio autodeploy drops the functions and assembly and recreates them rather than using ALTER ASSEMBLY.

The next request against any of the .NET database objects will cause a new appdomain to be created.

.NET (and therefore SQLCLR) divides up running its code (even within the same process like the sqlserver.exe process) into appdomains. The appdomain is like a lightweight process used to enforce isolation between running .NET code within the same Windows process. SQLCLR (.NET code running in SQL Server) uses appdomains to isolate execution of .NET code on a per database and per assembly owner basis.

SQLCLR uses appdomains for two reasons: for running .NET user code like functions and procedures, and for running DDL to create and alter assemblies. You can see appdomains being created and destroyed in the SQL Server log, as well as query the current appdomains by using the sys.dm_clr_appdomains dynamic management view.

In SQL Server 2005, the SQL Server log only showed user code-running appdomains, the messages look like this:

AppDomain 4 (testdb.dbo[runtime].3) created.
AppDomain 4 (testdb.dbo[runtime].3) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.
AppDomain 4 (testdb.dbo[runtime].3) unloaded.

Is these messages there's a appdomain being created for running code in the testdb database for assemblies owned by dbo. It's a runtime appdomain. The last two messages show the appdomain (some time later) being unloaded.

In SQL Server 2005 it was difficult to "see" DDL appdomains, because their presence was not logged in the SQL Server log. You could deduce they existed by noting that the AppDomain numbers (e.g. AppDomain 4 above) seemed to contain skips in the number range. Or if you were extremely lucky, by querying sys.dm_clr_appdomains at *exactly* the right time.  In SQL Server 2008, it is. You don't see them being created, but I just executed some DDL to create a SQLCLR assembly and SQL Server log reports:

AppDomain 5 (testdb.dbo[ddl].4) unloaded.

This should make it a little easier to diagnose appdomain-related problems or see when assemblies have been created or altered.

Sometime before SQL Server 2008 RTM, the libraries for LINQ and LINQ to XML (that is System.Core.dll and System.Xml.Linq.dll) but NOT LINQ to SQL will be added to the SQLCLR "approved" assembly list. The approved assembly list is the list of .NET Framework libraries that have been tested in a SQL Server-hosted environment, annotated their code with HostProtectionAttributes where needed, and can be used in user assemblies that can be cataloged with "PERMISSION_SET = SAFE". That means you can use 'em in SQLCLR user-defined functions, procs, and other SQLCLR database objects.

Being curious, I asked "why LINQ and LINQ to XML but not LINQ to SQL"? The answers I got were "LINQ to SQL not tested in this environment" and "not sure there should be yet another way to access the database with .NET". Fair enough. Although, its never seems to be a problem to provide yet another way to mix SQL Server and XML. The number of different ways now exceeds the number of fingers on one hand.

OTOH, SMO doesn't appear to be added to the approved assembly list in SQL Server 2008. Nor has WCF. Oh well.

Enjoy...

One last SQLCLR feature I'd forgotton about but was quite highly publicized. This is extension of SQLCLR UDT and UDAgg maximum size from 8000 bytes to 2gb. You just the MaxByteSize of -1 in the appropriate attribute, like this:

[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=-1,IsNullIfEmpty=true)]

The serialization format is going to have to be UserDefined, the limt for Format.Native is still 8000 bytes.

Just catalog the UDT or UDAgg and use it as you would any other TYPE/AGGREGATE. I crufted up a proof-of-concept one pretty easily, and the new spatial data types (GEOGRAPHY and GEOMETRY) are also large (system) UDTs, so you know this works as of CTP5.

Now you can have your "infinite" UDT that holds an array or "infinite" string concatenation UDAgg. Enjoy.

Another cool SQLCLR feature in SQL Server 2008 is the ability to define a table-valued function with an ORDER clause. If you have intimate knowledge that the function always returns rows in sorted order, you declare your SQLCLR UDF with an ORDER clause in the CREATE FUNCTION DDL statement. Let's experiment with this using the cheap and easy Fibonacci sequence function from Dan Sullivan and my SQL Server 2005 Developer's Guide.

create assembly orderedtvf from 'C:\temp\OrderedTVF.dll'
go

-- no order clause
create function FibonacciUnOrdered (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
as external name orderedtvf.FData.Fibonacci
go

The Fibonacci sequences generated are always in ascending order because that's how the function is implemented. In fact, they are in order by both the "next" and "prev" column. Because there is no limit to the number of method signatures we can have over the same SQLCLR UDF, we use the same SQLCLR method, just changing the DDL statement and function name.

create function FibonacciByNext (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (next asc) -- this is new
as external name orderedtvf.FData.Fibonacci
go

create function FibonacciByPrev (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev asc) -- same concept, different ordering column
as external name orderedtvf.FData.Fibonacci
go

Now let's do some testing. The query plan iterators and plan cost are shown as comments.

-- cost: 0.0279081
-- TVF -> Sort -> Select
select * from dbo.FibonacciUnordered(3,4,5)
order by next

With an ordered TVF, there is no SORT iterator, but there are extra query plan steps to operate on the ordered set. AND... the query cost is over 10x lower.

-- cost: 0.0023802
-- TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.FibonacciByNext(3,4,5)
order by next

-- cost: 0.0023802
-- TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.FibonacciByPrev(3,4,5)
order by prev

Note that it IS best to have a different TVF name for each sort order, and "no order", if you plan to use different ORDER BY clauses. This one has a Sort AND its cost is greater than the function that's declared UnOrdered

-- Different order
-- cost: 0.0292881 (more than Unordered = 0.0279081)
-- TVF -> Segment -> Sequence Project -> Assert -> Sort -> Select
select * from dbo.FibonacciByNext(3,4,5)
order by prev

In addition, the query plan guarentees that you don't lie in your order clause. Here's proof.

create function FibonacciWrong (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev desc) -- THEY ARE IN ASCENDING ORDER, NOT DESCENDING
as external name orderedtvf.FData.Fibonacci
go

-- Error:
-- The order of the data in the stream does not conform to the ORDER hint
-- specified for the CLR TVF 'dbo.FibonacciWrong'.
-- The order of the  data must match the order specified in the ORDER hint for a CLR TVF.
-- Update the ORDER hint to reflect the order in which the input data is ordered,
-- or update the CLR TVF to match the order specified by the ORDER hint.
select * from dbo.FibonacciWrong(3,4,5)
order by prev

Remember, you're not only saving a SORT iterator in the query plan, you're saving memory too. The SORT iterator requires a memory grant. And because there are no stats for SQL Server to use in these "opaque to SQL" functions, the memory grant for the SORT iterator in FibonacciUnOrdered is 1024K. And, we hope that the rather generous memory grant is enough, else memory is being allocated during query execution.

So, declaring ordered TVFs is worth it.

One thing that I didn't find in the BOL What's New page is some of the new SQLCLR functionality in SQL Server 2008. The first one that intrigued me is support of multi-input user-defined aggregates. Suppose I wanted to implement Oracle's COVAR_POP aggregate, an analytic function that returns the population covariance between two expressions. The signature is COVAR_POP(expr1, expr2) and I want the signature to stay the same in SQL Server.

All that I need to do this is to use the "template" for a .NET UDAgg struct/class, replacing the Accumulate method that take one parameter with a 2-parameter method, like this:

public void Accumulate([SqlFacet(Precision = 20, Scale = 10)] Nullable<decimal> Value1, 
                               [SqlFacet(Precision = 20, Scale = 10)] Nullable<decimal> Value2)
{
  // code here
}

My CREATE AGGREGATE DDL statement would change a bit:

CREATE ASSEMBLY multiparmagg FROM 'C:\temp\multiparmagg.dll'
go

CREATE AGGREGATE dbo.covar_pop(@expr1 decimal(20,10), @expr2 decimal(20,10))
RETURNS decimal(20,10)
EXTERNAL NAME multiparmagg.CovarPop;
go

To invoke:

create table dbo.test_covar (
  i1 decimal(20,10),
  i2 decimal(20,10)
);
go
-- fill with data, then...
select dbo.covar_pop(i1, i2) from dbo.test_covar;

Happy aggregating.

A couple of people have been asking and, in preparing for my upcoming talk on spatial data on SQL Server 2008 at ITForum in Barcelona in a few weeks I got to ask about using the new system UDTs types on the client. In an upcoming CTP release there will be an MSI installer file specifically to install these types on clients. The appropriate files are installed and assembly (Microsoft.SqlServer.Types.dll) registered in the GAC.

Since these are .NET data types, these (HierarchyID, Geography, Geometry) are easily usable in SQLCLR functions and procedures too. No possible data type mismatches or nullability (the new types implement INullable and have a static property to return a NULL instance) concerns.

So, people always ask... now that .NET Framework 3.0 installed on my system and there's going to be a new version of .NET that includes LINQ, what version of the framework will SQL Server load now, in SQL Server 2008? Does 2.0 still load? Or does it load 3.0 or 3.5?

Theoretically, and I've written this in books and previous blog entries, SQL Server loads the most recent version of the .NET framework. .NET service packs therefore affect SQLCLR. Especially if they contain updates to say, System.Data.dll. Or System.Xml.dll. Or [your favorite "approved" library goes here].

At present, one .exe can load one and only load version of the .NET framework. ASP.NET, for example, supports multiple versions with multiple worker-processes, that is, multiple .exe-s. Perhaps in .NET 4.0, we'll be able to run multiple .NETs in a single process. Perhaps not. The "main" .NET assembly is mscorlib.dll; this contains quite a few of the "main" .NET namespaces/classes. Namespaces don't necessarily correlate to DLLs, in Visual Studio 2008 version of .NET "System.Data" will be spread across a few DLLs. To me, there is "a new version of .NET" when there is a new version of mscorlib.dll. That's not the way its represented normally.

.NET 3.0 did not replace mscorlib.dll. Or System.Data, System.Xml, ASP.NET, or others. In my C:\WINDOWS\Microsoft.NET\Framework\v3.0 directory there is no DLLs, only three subdirectories: Windows Communication Foundation, Windows Workflow Foundation, and WPF. So, if you have a machine that "comes with .NET 3.0" it also must come with (at least) .NET 2.0.

My machine (with SQL Server 2008 and Visual Studio 2008 Beta 2) has a .NET 3.5 directory in it. And a subdirectory named "Microsoft .NET Framework 3.5 (Pre-Release Version)". Neither one has an mscorlib.dll in it. It's mostly Visual Studio-related DLLs and utilities.

The main additonal assemblies for 3.0 and 3.5 (like LINQ, for example) actually live in C:\Program Files\Reference Assemblies\Microsoft\Framework. V3.0 and V3.5. That's were the main action is, except that this is all mostly hidden by the presence of and registration in the global assembly cache (GAC).

So, an instance of SQL Server 2008 will load mscorlib.dll, version 2.0.50727.42, as always. However, in SQL Server 2008 (and 2005 for that matter, if it ships as a .NET upgrade through Windows Update), you'll see a new version of System.Data.dll, version 2.0.50727.1378. Or, perhaps, we'll be able to hold the versions constant, at the possible expense of interop between SQL Server 2005 features that use .NET. Like SSIS, SSMS, SSRS, and so on.

Got it? ;-)

So, Microsoft.SqlServer.Types lives in the resource database and runs in its own appdomain when its used by system functions, like SELECT * FROM sys.assemblies. Let's try an experiment with the following setup.

I have two user assemblies in a database named "test". One doesn't access any .NET types, its called datetest. The new DATE/TIME-related data type series are not .NET-based, but you can use them in SQLCLR procs, as you can use NVARCHAR data type. There are some restrictions on DATE/TIME series, more about that later. My second user assembly is named hiertest. It uses the HierarchyID data type in SQLCLR code. That's (of course) OK too. Both my user assemblies are owned by DBO. Both catalog as SAFE_ACCESS.  There are no user assemblies in pubs database. Turn on SQL Profiler.

In pubs: SELECT * FROM sys.assemblies;

In profiler:
Assembly Load event:
Assembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002
In SQL Server Log:
 Common language runtime (CLR) functionality initialized using...
 AppDomain 2 (32767.sys[runtime].1) created
   
Still in pubs: declare @h hierarchyid; select HierarchyID::GetRoot(); -- invoke static method of hierarchyid data type

In profiler:
 Assembly Load Succeeded for Microsoft.SqlTypes.Types
In SQL Server Log:
 Unsafe assembly 'microsoft.sqlserver.types..." loaded into AppDomain 2 (32767.sys[runtime].1)

USE test
GO

In test: SELECT * FROM sys.assemblies;
In SQL Server log:
Assembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002
  
In test: EXEC dbo.somedateproc -- my SQLCLR proc that uses date.
In SQL Server log:
 AppDomain 3 (test.dbo[runtime].2) created
In profiler:
 Assembly Load Succeeded for datetimetest
  
In test: SELECT * FROM dbo.AncestorAndSelf('/'); -- my SQLCLR UDF that uses HierarchyID
In SQL Server log:
 Unsafe assembly 'microsoft.sqlserver.types..." loaded into AppDomain 3 (test.dbo[runtime].2)
In profiler:
 Assembly Load Succeeded for Microsoft.SqlTypes.Types
 Assembly Load Succeeded for hiertest

So what happened??

There is only one resource database appdomain, it gets created when system functions (sys.assemblies) use system assemblies. The actual assembly that contains the system .NET data types loads only when it's required, into the resource database's appdomain. By the way, the declaration of a (NULL) hierarchyID variable isn't enough to load the assembly, you must actually use the variable. If you use only TSQL and the new data types, only this one appdomain is needed, regardless of how many different databases use them.

Additonal appdomains are created on a per-database, per-assembly owner basis as in SQL Server 2005. Each "user" appdomain will also load the system assembly Microsoft.SqlTypes.Types, if and only if it needs it. That is, if you use HierarchyID (or Geometry/Geography) in a SQLCLR procedure.

One last bit. Why are the appdomains referred to as: "AppDomain 2 (32767.sys[runtime].1)" and what happened to AppDomain 1? When .NET is loading into any executing process, there is a single appdomain created, the default appdomain. AppDomain 1. SQL Server doesn't load Microsoft.SqlServer.Types into this default appdomain, but starts another for the resource database. That's AppDomain 2 (into the process). The ".1" in "32767.sys[runtime].1" is first user appdomain.

Database administrators like to know about everything going on in "their" database, and with good reason...if the database fails (or even runs slowly) its (s)he who gets the first phone call for help. SQLCLR is still relatively new. Hope this was helpful in explaining what's going on. But remember, the exact appdomain implementation could be refined further in later releases. Cheers.

I'm running with SQLCLR on, because I'd like to see the interaction between system SQLCLR code and appdomains. See the previous blog posting for an explanation of why this doesn't affect my system functions. First, I bring up SQL Server 2008 "fresh", open SQL log in SSMS, and start a profiler trace to catch Assembly Loading events. SQLCLR is nowhere to be seen.

USE TEMPDB
GO

SELECT * FROM sys.assemblies;
GO

The assembly list contains an entry for Microsoft.SqlServer.Types, that's the assembly that contains the system UDTs. It actually lives in the resource database, but shows up in system metadata lists for every database. In the SQL Server log, just listing the assemblies in a database produces:

Common language runtime (CLR) functionality initialized using...
AppDomain 2 (32767.sys[runtime].1) created

Database 32767 is the resource database, although its not for the most part directly visible in SQL Server 2005 metadata or in SSMS.

.NET appdomains are created on a per-database basis, currently one appdomain per assembly owner. So this functionality runs under an appdomain created for the owner "sys". The assembly is actually owned by principal_id 4, which is INFORMATION_SCHEMA according to sys.database_principals. But sys.schemas indicates that the sys schema is owned by principal_id 4 as well, INFORMATION_SCHEMA schema is owned by principal 3. They're likely referring to principal_id in the resource database, not the current database. Sys.assemblies also has this assembly marked as is_user_defined = 0 (false). And, the assembly has a safety level of UNSAFE. Hmmm...

Knowing how SQLCLR exception escalation works, I was concerned by the system assembly being UNSAFE. It is  running in its own appdomain. However, when I looked at the SQL Server log for the SQLCLR message, another seemingly unrelated message attracted my attention:

Using xpstar.dll version 2007.100.1049 to execute extended stored procedure xp_instance_regread...

SQL Server internals have always included extended stored procedures to perform system functions. User-written extended stored procedures can cause problems if poorly written, but this one (xp_instance_regread) was written by the SQL Server team, its part of SQL Server itself. Hmmm...so is Microsoft.SqlServer.Types part of SQL Server. And .NET code has more built in safeguards than unmanaged code. And BOL has indicated since SQL Server 2005 betas:

"This feature (i.e. extended stored procedures) will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead."

So I guess its OK, they're taking their own advise. And this assembly has been tested with SQL Server for hardening, it doesn't produce the error message that cataloging, say, System.Runtime.Remoting, does.

One final item. SQL Profiler does not load Microsoft.SqlServer.Types.dll just because I execute "select * from sys.assemblies", but profiler reports:

Assembly Load Failed for requested assembly system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86 with Error code: 0x80070002

Perhaps there's a stray assembly dependency somewhere in CTP4. System.EnterpriseServices is not on the list of tested assemblies either, so it shouldn't load automatically.

I was listening to a replay of the webcast recording on the HierarchyID by Michael Wang (thanks, Michael) and as he mentioned the considerations for the CLR-based type with respect to DDL, I thought it would be interesting to go back and see how this type showed up in the various facilities that we have for monitoring what SQLCLR is doing. These facilities are:

1. Ability to see assemblies registed in each database
2. Watch code-running appdomains (but not transient DDL-only appdomains) being created/torn down in SQL log
3. SQL Profiler event for Assembly.Load
4. Monitor memory and CLR-related processes using DMVs and perfmon

Before reporting the results obtained with SQL Server 2008 CTP4, we need a few clarifications. First, the way SQLCLR manages appdomains is an implementation detail and subject to change in future releases, service packs, or can even change before SQL Server 2008 ships. I'm just observing. Second, let's talk about what exactly the "sp_configure 'clr enabled', 0" does. You can also set this option using SQL Server Service Area Configuration utility. This option indicates whether or not its possible to run *user-written SQLCLR code*, that is, SQLCLR stored procedures, UDFs, trigger, UDTs, and UDAggs written by programmers.

The switch *does not*:

1. Keep SQLCLR from loading in SQL Server's process. This always loads the first time that you need it.
2. Prevent DBAs from using SQLCLR-related DDL, such as CREATE/ALTER/DROP ASSEMBLY, and define SQLCLR objects.
3. Prevent system functions that use SQLCLR from running. In SQL Server 2005, there were no SQLCLR system functions that I was aware of.

In SQL Server 2008, system functions that use SQLCLR that immediately come to mind include:
   a. The HierarchyID and upcoming spatial data types, Geometry and Geography
   b. Change Data Capture and the Dynamic Management Framework

So, its not that using system functions written in SQLCLR is "a trick" that bypasses an established control mechanism. The reality is that the mechanism was never defined to prevent SQLCLR loading, DDL, or system functions.

One things that will prevent SQLCLR from running is to enable lightweight pooling, or "fiber mode scheduling". This is also a configuration option, and its incompatible with SQLCLR. The CLR is not "fiber aware", although it may be implemented sometime in the future. A few other SQL Server system features are incompatible with fiber mode as well.

Just found out about this one today. This SQLCLR function works right now in CTP4 of SQL Server 2008.

public static Nullable<int> AddTwo(Nullable<int> x, Nullable<int> y)
{ return x+y; }

create function dbo.addtwo (@x int, @y int)
returns int
as
external name asmname.[Mynamespace.Class1].AddTwo;

select dbo.addtwo(2, null);
-> null

Great! Turns out, I suggested this back in 2005...after SQL Server 2005 shipped. And they listened. Actually, going back over the list from 2005... SSMS does display NULL for a NULL UDT already, as of SP1. And large UDTs and UDAggs are also on the agenda for SQL Server 2008.

That may also explain why there's no System.Data.SqlTypes.TimeSpan/DateTimeOffset in Visual Studio Orcas Beta2. They're not needed.

ORDPATH is a hierarchical labeling scheme used in the internal implementation of the XML data type in SQL Server 2005. It's meant to provide optimized representation of hierarchies, simplify insertion of nodes at arbitrary locations in a tree, and also provide document order. It's described in the academic whitepaper "ORDPATHs: Insert-Friendly XML Node Labels". In addition to being used internally when the XML data type is stored, its also part of the key of the PRIMARY XML INDEX, used to speed up XQuery. It's an implementation/specialization of the path enumeration model of representing hierarchies in relational databases, mentioned in Joe Celko's book "Trees and Hierarchies in SQL".

In SQL Server 2008, there are additional uses of ORDPATH. There is a new system data type HierarchyID, that will likely use ORDPATH in its implementation. This allows simply hierarchies to be represented as relational column and provides methods that optimize common hierarchical operations (like parent, child, sibling, ancestors, descendants) without being concerned about the intricacies of elements and attributes.

In addition to representing and indexing XML and hierarchies, Michael Rys mentioned at his TechEd chalktalk on spatial data that the spatial data types may be indexed using a multi-level grid system and that these indexes would also use ORDPATH. Since neither HierarchyID or spatial types are in the current CTP of SQL Server 2008, we'll have to wait a bit to see if this is truly "ordpath everywhere".

SQL Server 2008 will contain, if my count is correct, 7 new data types. Note: none of these are in the current CTP.

DATE - ANSI-compliant date data type
TIME - ANSI-compliant time data type with variable precision
DATETIMEOFFSET - timezone aware/preserved datetime
DATETIME2 - like DATETIME, but with variable precision and large date range

GEOMETRY - "flat earth" spatial data type
GEOGRAPHY - "round earth" spatial data type
HIERARCHYID - represents hierarchies using path enumeration model

The first four (date/time series) are NOT implemented/exposed as .NET system UDTs, but the last three are exposed as .NET system UDTs. This means that, for the first time, .NET will be used as part of SQL Server. In SQL Server 2005, there were originally DATE and TIME data types implemented in .NET, but implementing temporal data is an intricate process. After much wailing and gnashing of teeth by some members of the user community, these were removed.  One of the complaints was the .NET implementation. Hmmm...

One of the nice side-effects of implementing spatial and hierarchyid as .NET types is that these will be shipped as a separate assembly, and that the types will be available for client-side and middle-tier use as well as in the database. So if you want to do some massive number crunching of spatial sequences on a computation server and the network traffic from database to computation server is acceptable, you can do so.

In general, SQLCLR makes the "logic in database or middle-tier" argument easier to deal with. Although there's no "run on database or run on server" switch in VS, with minimal code changes you can move your logic, or even duplicate the logic between tiers if need be. You can't do this with T-SQL; although its faster and better for data access on the database, it doesn't run outside the database. Unless you want to use SQL Server Express Edition as an application server. But that's a discussion for another time.

Since SQL Server 2005 was introduced with .NET programming support, folks have been trying to push the boundaries of what can be used in SQLCLR or at least trying to determine where those boundaries are. Here's two things that, as far as I know, can't be done in SQLCLR.

1. Use dynamically generated code. The canonical example of this is dynamic serialization assemblies generated when you use "Add Web Reference" in the generated web service proxy code. The way around this is to use the sgen utility. But lately, it's been brought up that dynamic programming languages such as Iron Python always generate dynamic code. SQLCLR forbids using this, even in UNSAFE assemblies. No dynamically generated languages.

2. Use the SMO libraries. A combination of SMO not supporting partially trusted callers and using a special type of connection result in SMO being unusable even in UNSAFE assemblies. The obvious workaround is to use SQL DDL, but SMO encompasses more than DDL, for example, configuring service settings via WMI. If you really want to use SMO, it would be possible to call out to a web service or better yet, a Service Broker-based service that uses external activiation, does the SMO calls and returns the script and/or results.

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.

Quite a while ago, someone (I forget who) was soliciting opinions on whether or not LINQ and ADO.NET vNext EDM should be able to run "in process", in other words should be useable in SQLCLR procedures. Here's one perspective, and a question.

LINQ and Entity SQL are QUERY languages. Although they might be able to encapsulate resultset output from stored procedures, much of their value is in the query language itself. NOT having them be useable in SQLCLR would encourage folks that want to use these languages to move AWAY from stored procedures and put their data access query code in client or middle-tier programs, rather than in the database. I don't think DBAs and database developers really want to encourage that.

On the other hand, some of the value of stored procedures is being able to restrict table permissions and find errors at compile time. SQLCLR procedures don't have either of those attributes built-in, although you can restrict table permissions if you use EXECUTE AS OWNER. And coding with LINQ/Entity SQL can allow compile-time type metadata checking. Other benefits of stored procedures, such as allowing the code to be shared among database apps, and consolidation of SQL code in database objects, do apply as well to SQLCLR.

And its a given that, with either of these languages (and with SQLCLR in procedures in general) you'll be giving away procedure execution speed for (*arguably* better, meaning you'all can argue about it) code maintainability. Unless you only use LINQ/Entity SQL to process T-SQL stored procedure resultsets.

What do YOU think?

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.

I usually show folks the SQLCLR streaming table-valued functions and using SqlDataRecord with SqlPipe.SendResultsStart/SendResultsRow/SendResultsEnd on the same day. This sometimes brings up the question "which is better"? Since you can synthesize rowsets with both mechanisms and they both stream rows back to the client one buffer-worth at a time, its an interesting choice. However, with SqlPipe/SqlDataRecord, you can synthesize multiple rowsets in the same stored procedure. Just cruft up two different SqlDataRecord With multiple SendStart/SendRow/SendEnd blocks. Can't do that with any kind of table-valued function. You must "finish" one rowset (SendResultsEnd) before "starting" the next (SendResultsStart), though, you can't have interleaved output any more than you would with an ordinary cursorless rowset. Example follows:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void MultiRowset()
{
SqlMetaData[] m = new SqlMetaData[1] {new SqlMetaData("colname", SqlDbType.NVarChar, 5) };
SqlDataRecord rec = new SqlDataRecord(m);
rec.SetSqlString(0, "Hello");

SqlMetaData[] m2 = new SqlMetaData[2] { new SqlMetaData("another", SqlDbType.NVarChar, 5), new SqlMetaData("number", SqlDbType.Int) };
SqlDataRecord rec2 = new SqlDataRecord(m2);
rec2.SetSqlString(0, "Goodbye");
rec2.SetSqlInt32(1, 42);

SqlPipe p = SqlContext.Pipe;
p.SendResultsStart(rec);        // first rowset
for (int i=0;i<10;i++)
 p.SendResultsRow(rec);
p.SendResultsEnd();

p.SendResultsStart(rec2);       // next rowset
for (int i = 0; i < 10; i++)
    p.SendResultsRow(rec2);
p.SendResultsEnd();
}

People often ask about the set of base class library assemblies that can be safely used in SQLCLR. It usually takes the form "can I use assembly System.XYZ.dll in SQLCLR procedural code" or "why do I get "assembly System.XYZ.dll is not found" when I try and catalog my own assembly that calls this one? The ones is hear mentioned most frequently is System.DirectoryServices.dll (Active Directory support) or System.Management.dll (WMI support) or System.Remoting.dll et al. The only way you use these is to run CREATE ASSEMBLY on them yourself, which involves using PERMISSION_SET = UNSAFE. And cataloging all the dependencies. Not for the faint of heart.

I explain that there is a hardcoded list of assemblies that SQL Server will load from the file system and these assemblies have undergone an extensive code review to ensure they don't destablize SQL Server when they are running in process. Since some assemblies contain both crucial and "unsafe" (to SQL Server) classes (mscorlib.dll is an example that comes to mind) these libraries must also be decorated with instances of HostProtectionAttribute. This attribute gives the host (SQL Server 2005, in this case) the final say over whether to run a method (e.g. Thread.Start in mscorlib.dll). SQL Server is very picky about when it will run an HPA decorated method, its only if the calling assembly is CREATEd with PERMISSION_SET = UNSAFE. There's more about this in chapter 2 of A Developer's Guide to SQL Server 2005.

I refer to this list of assemblies as "the approved list". You can see most of the "approved list" assemblies by creating a Visual Studio 2005 Database/SQL Server project and choosing "Add Reference". This is the entire list of approved assemblies with one exception. Right before RTM "System.Configuration" was added to the list. I'm not entirely sure why, because although you can successfully use a .NET config file with SQL Server 2005 currently, it neither supported or encouraged (see my blog articles about this).

Why the long story? Because, in SQL Server 2005 SP1 another assembly made the list: System.Deployment.dll. I'm rather puzzled by this one too, because it has to do almost entirely with click-once deployment. Not sure why someone would want a sproc to do this, unless it was used to tie SQL Server to .NET project deployment in conjunction with Visual Studio Team System. But, its in there. Hmmm...

The libraries that I'd hoped would make the list eventually, perhaps in a SQL Server service pack when Windows Vista and WinFX are released, are the Windows Communication Foundation (System.ServiceModel.dll) and Windows Workflow Foundation (System.Workflow.Runtime.dll et al). I've heard a lot of folks ask the same question, but so far there's been silence on this. Since System.WebServices (but not WSE) is aleady "in there", there might be some uses going forward. As a look toward "intent", I ran ILDASM against the just-released May CTP version of these. Not a HostProtectionAttribute in sight. Oh well.

SQL Server 2005 doesn't support assembly versioning in SQLCLR. Recently in a talk I restated this as "SQL Server 2005 will not allow two versions of the same assembly to be cataloged into the same database using CREATE ASSEMBLY". Someone promptly pointed out that they could indeed run CREATE ASSEMBLY on two instances of the same code, if one was strong-named and the other was not strong-named. That is the case, but that is not support of assembly versioning. The .NET Framework Developer's Guide states "Versioning is only done on assemblies with strong names".

As an example, if I run CREATE ASSEMBLY on an assembly without a strong name, the query "SELECT name, clr_name FROM sys.assemblies" produces the following output:

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil

Note the version in clr_name is "0.0.0.0" regardless of the version number that is stored in the [assembly:AssemblyVersion] attribute in the file AssemblyInfo.cs. Attempting to catalog another version of the unsigned assembly, code or AssemblyVersion attribute change or not, produces the error:

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

If I have strong named assemblies, I can only have one strong named version that differs by version. Running create assembly (on a strong-named "version 1") gives the following output using "SELECT name, clr_name FROM sys.assemblies"

notsigned_test versioningtest, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil
v1                  versioningtest, version=1.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil   

So I DO have "two copies of the same" assembly in the database at a time. However attempting to run CREATE ASSEMBLY on a strongly typed version 2.0.0.0 yields the following error.

Msg 10326, Level 16, State 2, Line 1
Two versions of assembly 'versioningtest, version=2.0.0.0, culture=neutral, publickeytoken=3e0cd30f6da0843f, processorarchitecture=msil' cannot coexist in database 'Northwind'. Keep one version and drop the other.

So assembly versioning is not supported. Note that you can have assemblies with different 4-part names that differ only by culture. Assemblies that include a culture other than "neutral"; these are resource-only assemblies such as localized strings for different cultures. How does SQL Server 2005 decide which culture to use? SQL Server itself really doesn't decide, but that's another story for another day.

A common question on the newsgroups is "what will happen if a SQLCLR procedure allocates a huge chunk of memory or enters a tight, endless loop" by mistake? DBAs are concerned about any language with a looping construct (they've likely coded the tight, endless loop in T-SQL to see) or a malloc or equivalent. The BOL suggests any loop should call Sleep(0) but sleeping is not always the same as yielding.

SQL Server 2005 does respond to memory pressure in a concerted manner releasing buffers and ending procedures if needed. So you might see an error similar to this under memory pressure or if you allocate "too much memory":

Msg 6532, Level 16, State 49, Procedure MyProc, Line 0
.NET Framework execution was aborted by escalation policy because of out of memory.

However, SQL Server doesn't consider "being busy" an error condition. Suppose there was a batch process that actually did run for a long time? In the case of the endless loop, if the server has other work to do, SQL Server 2005 will force a CLR thread to yield and "punish" it (timeslice-wise). This is visible using a dynamic management view, sys.dm_clr_tasks, in the field "force_yield_count". When in an endless loop, this field increases, but SQL Server does not kill the task.

A couple of weeks ago, I published an entry about using System.Configuration.dll in SQLCLR procedures, along with a sqlserver.exe.config file. Since then I've received information that, although reading configuration information in SQLCLR procedures does work, that *technically* its not officially supported, and the fact that it works might change in future releases. Just thought I'd let you know. Don't do this.

As with the use of undocumented stored procedures or undocumented DBCC commands in code, I think its better to be safe than sorry in these cases. Speaking of undocumented stored procedures, folks that depend upon the undoc'd (at least it was last time I looked) xp_regread procedure might be happy to know that it trivally simple to read the registry (modulo security considerations) from a SQLCLR program. That's one undoc'd procedure that you don't need anymore.

I think I got my December update too early. There's an update of the SQL Server 2005 samples on the downloads website. I have (thought I had) the December update, this one actually says "Date published 1/4/2006" and its 20mb instead of the old once (just under 16mb).

The site says: Since its original release, new samples have been added for the following technologies: CLR, SMO, Integration Services, Replication, and Reporting Services. See the SQLServerDatabasesAndSamplesOverview.htm file for descriptions of the new and original samples.

Well, the samples folks (thanks Bonnie and everyone) have really outdone themselves this time. Thanks!

One of the samples that particularly intruiged me was a TVF that returned a resultset from Oracle using System.Data.OracleClient. I was surprised because, although System.Data.OracleClient is on the "approved assemblies" list, the DDL cataloged the assembly as UNSAFE. Hmmm...thought I'd only need EXTERNAL_ACCESS.

So I fired up my combined VPC of SQL Server 2005/Oracle 10g and decided to see for myself. When cataloged as EXTERNAL_ACCESS I got:

Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65541. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'OracleTVF, Version=1.0.0.0, Culture=neutral, PublicKeyToken=65071e111a9fd000' or one of its dependencies. Failed to grant minimum permission requests. (Exception from HRESULT: 0x80131417) ---> System.Security.Policy.PolicyException: Required permissions cannot be acquired.

When cataloged as UNSAFE, it worked fine. Well, after I changed it to access the SCOTT.DEPT table. I don't have the "titles" table the sample referred to.

The reason for this (UNSAFE) is most likely that System.Data.OracleClient (which is approved) needs to load Oracle's client libraries. These are written in unmanged code, which makes them unsafe to load, even if System.Data.OracleClient isn't unsafe. BTW, I didn't try ODP.NET. This would have required cataloging my DLL as UNSAFE because Oracle.OracleClient.dll provider isn't on the approved list. But I'm running UNSAFE already. Oh.

System.Configuration.dll was the last assembly added to the "SQL Server 2005 approved assembly" list, that is, the list of BCL assemblies approved for SQL Server consumption. These are loaded from the GAC, rather than having to be explicitly added to the database by the user. System.Configuration was added SO late that it doesn't show up in a Visual Studio Database assembly project in the "Add References" dialog with all of its collegues. You have to use a normal Visual Studio Class Library project (or other means) to reference it.

When students in my beta classes would try and fiddle with the config file information used to determine which version of an assembly should be loaded by putting these settings in a file aptly named sqlservr.exe.config, these would be ignored. I'd thought that the entire config file was discarded, and rebuilt so that SQL Server would have its own version configuration information. Just before SQL Server 2005 shipped, the ability to run CREATE ASSEMBLY on multiple versions of the same user assembly was dropped (you can still have multiple language-specific resource sattelite assemblies cataloged).

So what do these two things have to do with one another?

Someone asked me recently if you could access configuration strings by making a sqlservr.exe.config unrelated to assembly loading I didn't think so, but if not, what is System.Configuration doing on the approved list? Let's try it in a .NET user-defined function...

public static string GetConfigString()
{
  // This statement requires referencing System.Configuration.dll
  ConnectionStringSettings s = ConfigurationManager.ConnectionStrings["pubs"];
  if (s != null)
    return s;
  else
    return "no config";
}

This works fine. The .NET UDF can even be cataloged and run as SAFE. So now that we've gone this far, a better question might be "why do you want to put configuration strings in files"? You're running IN A DATABASE after all, how 'bout storing them in a TABLE? Perhaps so that the same code would work inside SQL Server or in middle tier server? OK, I'll buy that.

Dynamic SQL executed in a stored procedure executes by default using the security context of the CALLER of the procedure rather than the OWNER. That's the way SQL Server has always worked, and although SQL Server 2005 lets you EXECUTE AS OWNER (among other choices), EXECUTE AS CALLER is still the default.

So how does this relate to SQL statements execute in a .NET stored procedure, function, or trigger? Do these execute as caller or owner? Turns out it depends on what statement you are executing. Executing an "ordinary" SQL statement like this:

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

GRANT someuser EXECUTE on dbo.GetAuthorsNET

EXECUTE AS USER='someuser'
EXEC dbo.GetAuthorsNET
GO
REVERT

executes the SELECT statement in the .NET code as CALLER and throws a permission denied error if the caller doesn't have direct SELECT access to the authors table. The same contruct in a T-SQL procedure:

CREATE PROCEDURE dbo.GetAuthorsSQL
AS
SELECT * FROM dbo.authors

GRANT someuser EXECUTE on dbo.GetAuthorsSQL

would execute the SELECT as the OWNER of the stored procedure, not the caller and the SELECT succeeds.

OK. How about the following .NET code? Does it execute dbo.byroyalty as the OWNER of the stored procedure?

public static void ExecByRoyalty {
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("dbo.byroyalty", conn)
{
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@percentage", 50);
  conn.Open();
  SqlContext.Pipe.ExecuteAndSend(cmd);
}
}

GRANT someuser EXECUTE on dbo.ExecByRoyalty -- grant on calling proc (owner by dbo)
DENY someuser EXECUTE on dbo.ByRoyalty -- deny on called proc

This executes the ByRoyalty proc AS the OWNER of the ExecByRoyalty proc, ownership chain intact. Even if someuser executes ExecByRoyalty. Oh.

Time for a tiebreaker. How about this one?

public static void ExecByRoyaltyAsString {
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("exec dbo.byroyalty @perc", conn)
{
  cmd.CommandType = CommandType.Text; // not a sproc, a textual execute statement, does it matter?
  cmd.Parameters.AddWithValue("@perc", 50);
  conn.Open();
  SqlContext.Pipe.ExecuteAndSend(cmd);
}
}

This executes the ByRoyalty proc AS the OWNER of the ExecByRoyaltyAsString proc, ownership chain intact. The sproc works even when 'someuser' executes it. Interesting.

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.

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.

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.

When I'd talk at Ascend about how Visual Studio auto-deploy of SQLCLR assemblies not only catalogs the assemblies and the PDB files to SQL Server 2005 but also the source files, folks would always ask "why the source files"? Today I found out; I'm surprised it escaped me for so long.

In Visual Studio Server Explorer, if you've deployed the source files and PDB, you can right-click on any T-SQL or SQLCLR stored procedure, UDF, etc and and debug it, source code and all, whether or not you have the actual project files in-hand. It will just get them from inside SQL Server. Pretty neat...so that's what its there for.

There is a pretty hefty (but *completely* understandable and necessary) price to pay for admission to this feature, however. You must be sysadmin in SQL Server (CONTROL SERVER) to do this. Understandable because you're going to:
a. stop all managed threads on the server
b. be able to snoop around

So...that's why. And how.

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.

Ahh...enough shameless self-promotion (for a while), let's talk transactions.

I worked in the past with folks who like to compose transactions. SQL Server supports nesting of transactions and named savepoints but not autonomous transactions. So

CREATE PROCEDURE X
AS
BEGIN TRAN
-- work here
COMMIT

calling it standalone means the work is in a transaction. Calling it from procedure Y:

CREATE PROCEDURE Y
AS
BEGIN TRAN
-- other work here
EXECUTE X
COMMIT

doesn't start an autonomous transaction, the BEGIN TRAN in X merely ups @@TRANCOUNT by 1. Interesting things happen when you roll back X while its being called by Y.

I'd like to emulate this behavior in SQLCLR, i.e. have a procedure that acts like X, and can be used standalone or composed. I can do something akin to T-SQL (and get the interesting rollback behavior with a slightly different error number) using the BeginTransaction method on the context SqlConnection. I'd heard awhile ago that System.Transactions used inside of SQLCLR would "always do the right thing". AND because of the way promotable transactions work, it would compose a context SqlConnection in the SAME local transaction. A la T-SQL or SqlConnection.BeginTransaction().

It doesn't do this. If I have a SQLCLR proc that looks like this (condensed version):

public static void X {
using (TransactionScope ts = new TransactionScope())
using (SqlConnection conn = new SqlConnection("Context connection=true"))
{
  conn.Open();
  ts.Complete();
}
}

If SQLCLR X is used standalone, all well and good, local transaction. If SQLCLR X is called from procedure Y (above) then SqlConnection.Open() starts a *distributed* transaction. Apparently it HAS to be this way, at least for now, because of how TransactionScope works.

If you WANT a distributed transaction composed with your outer transaction (your SqlConnection is calling to another instance for example), USE TransactionScope, if you DON'T want one, use SqlConnection.BeginTransaction. It won't act any different from T-SQL (except you do get a different error number) if you roll back inside an inner transaction. But you get a nesting *local* transaction with BeginTransaction.

BTW just is case you wondered if SQLCLR X proc could do this:

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))

and go for that elusive "autonomous transaction", don't do it. You'll get a message saying "no autonomous transaction. Because SQL Server doesn't support autonomous on a single connection. SQLCLR or not. There is the two connection case, but that's a story for another day.

This was as compressed a blog entry as I could make it, but was still quite long. Any questions?

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.

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

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.

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

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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