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 how they work together. The panel is being held as a webcast, and they'll be some slides too. This is all taking place on September 22, and they'll be two separate iterations. If you've even wondered what the point of using the exactly coordinated CTPs together was (and whined about it), come and attend this panel and find out why it was worth it. For a hint, bring up your SQL Server 2005 box and look in C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell. Nuff said.

Here's the official invite:

Unleashing SQL’s True Potential With Visual Studio

Come join us for this interactive web seminar featuring discussions with SQL expert and recognized author, Bob Beauchemin of SQLskills, Microsoft SQL Server and Visual Studio product experts and Microsoft Gold partner, Dexterra. The panel will address how, when and why developers should use Visual Studio in conjunction with SQL server and its Management Studio product. Specifically, we’ll examine Visual Studio’s user-friendly ability to write, deploy, and debug .NET and T-SQL procedures, how Microsoft stacks up in this space against competitors like IBM and Oracle, and look at some real world examples illustrating how and why customers are leveraging these powerful tools together.

We now return you to our regularly scheduled technical content....

I received email today that it was OK to talk about this. Our book “A First Look At SQL Server 2005 For Developers” was selected as “Best SQL Server Book” by the readers of SQL Server Magazine. You can read a short blurb about it here. And if you're a subscriber, you can read the whole article about the awards from their main page.

Congrats to Dan, Niels, and the folks at A/W who made this possible.

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

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

[SqlMethod(OnNullCall=true)]

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

[SqlMethod(InvokeIfReceiverIsNull=true)]

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

This does work as advertised:

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

both counts return 1.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

I've spent the last few days talking with the data access folks at Microsoft. They're working on the next set of features and I've given input based on feedback I got from clients and students in the past two years. There may be some things I forgot about, however.

If you have an ADO.NET feature request, especially in the context of: “what problems are left unsolved in the data access space after .NET 2.0”, please feel free to write it here. Or if you're a former student or client, you can probably infer my new email address and write me mail.

If you're not particularly vocal about this issue now, prepare yourself for a featureset dictated by folks more vocal than yourself. It may be a hassle (and time-consuming) to provide feedback, it's even less useful to sit and grumble afterward.

I answered a question on the beta newsgroups last week about OSD and RSD (remember them?) by saying that those features had been pushed out into the future. The person then asked if the material in chapter 14 of our first look book had any practical value. I must admit that you can't cut and paste the examples and run them in the upcoming release. I don't know anything concrete about feature futures yet, so here's the long answer... from last week.

The features in chapter 13 and 14 were postponed, so they may appear in
future implementations. I used to tell folks those chapters are "very
futuristic". ;-) They may not appear line-for-line, class-for-class, the way
I coded them. But let's see where they are today without guessing at future.

ObjectSpaces was moved to the WinFS group. AFAIK (blogs and official notice)
it may not appear in WinFS as exactly the same implementation. In the last
WinFS public beta, OPath was/is the WinFS query language. Can't say what the
status of it all is today. There is no OSD or RSD in .NET 2.0.

Object-relational mapping as a concept has been around since there were
relational databases and object-oriented programming. Whether
codification/generalization of it into a product is a good idea is a subject
of *endless* debate, which I *don't* want to start again here. Some of the
products, past and present, have suffered from performance issues. But the
fact remains that if you are using relational data and object classes on the
client to consume/format/present that data, you are likely doing
object-relational transformation (even if it is very shallow) to some
extent.

System.Xml.Serialization is the preferred codification of XML-object mapping
in the .NET framework today. There is also the implementation in
System.Remoting.

The concept and implementation of a "query-intermediate language" mentioned
in chapter 13 was used in .NET 2.0 in XmlCompiledTransform class.
Client-side XQuery in .NET 2.0 was postponed because the spec is not
finished yet. Many implementations of XML consumers use a single library to
permit XPath/XSLT/XQuery in the same exe, probably don't use the
intermediate language concept, but use something coneptually similar. There
is no XSD/RSD mapping in .NET 2.0.

XML-relational mapping is in SQL Server 2005 in the guise of:
1. SELECT ... FOR XML
2. OpenXml and xml.nodes
3. SQLXML4 (which is part of SQL Server 2005)
4. SQLXML3 (which is still supported)
5. XML Web Services

There is an ISO/ANSI spec SQL2003 part 14, that codifies some/most/all of
these mapping concepts. In addition to SQL Server's implementation (in 2000
and 2005) other databases have similar but different ways of approaching
this problem.

So the class names, product/feature names, and implementation may change,
but the concepts and data models remain the same. As does the use of
multiple data models in the same programming project.

Hope this helps.

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

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

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

ALTER LOGIN sam WITH CREDENTIAL = myuser
GO

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

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

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

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

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

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

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