More ADO.NET 2.0 and XML data type

When you retreive a rowset that contains an XML data type column or use an XML data type column as parameter input, you have two choices. You can use the vanilla .NET type, System.String, or a special types from the System.Data.SqlTypes namespace called SqlXml. ADO.NET 2.0 handles conversion when you use strings (as long as […]

Home again

I returned home from Europe late Saturday night. With catching up on my sleep, email, questions, Thanksgiving (which Mary and I celebrated on Sunday this year), and everything else, I just realized its Tuesday morning and no blog entries. Time to blog again. I had two great classes, the caliber of the students was outstanding […]

Some useful articles on MSDN online

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 […]

SQLCLR assembly owners and dependencies

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 […]

SQLCLR appdomain usage

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 […]


This is actually posted a few days after I wrote it. I haven’t done the “internet on the plane” service although I think it exists. 5:00AM Paris time, in the skies over ???? Right now, I’m in an alternate reality space that I call “AirplaneLand”. I’ve racked up over 100,000 “airplane miles” this year, which […]

An excellent SQLNS book

SQLNS is an acronym for SQL Server Notification Services. Originally introduced as a “free addition” to SQL Server 2000, it is an integrated part of SQL Server 2005. SQLNS is a framework for a specific kind of application, a publish-subscribe notification application. We did a chapter on SQLNS 2005 in our “First look at SQL […]

How do I get (N) rows from a SQL Server XQuery?

Every time I demonstrate using XQuery against a table that contains an SQL Server XML data type to a new class, a get about one out of four people with puzzled looks. And they're the ones who know XPath and XSLT. The part that seems weird to them is that a XQuery against what they […]

What’s my (Windows) identity?

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 […]

Don’t you database people have any standards?

[Comment heard from an XML afficianado] A: Uh…Yes, lots of them… User-schema separation always leads to the recollection that “user-schema separation is the way things are defined in ANSI SQL 1999”. Which brings up the subject of standards. Touting the ANSI SQL 1999 standard is passe now, because the ANSI SQL 2003 is out. And […]

I have CREATE TABLE privilege but can’t create tables

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

More on ownership chains

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 […]

Users, Schemas, Objects, and Owners

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

.NET-based library for Service Broker

I was browsing through some of the SQL Server 2005 code samples today (the ones that come with the product) and came across one that was a really nice idea. It's a library to encapsulate SQL Server Service Broker T-SQL calls in an object model, called ServiceBrokerInterface. Some of the other Service Broker samples are […]

Formatting XML/XQuery in SQL, and caveats

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

XQuery Schema Validation Clarification

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

Early XQuery schema validation

Dan Sullivan pointed this out to me a couple of weeks ago. I'd forgotten about it, but it came back to bite me in a demo last week. It's a feature. If you have an XML column, variable, or UDF return value that's tied to an XML SCHEMA COLLECTION, any XQuery against that XML will […]

Slides, articles, and shameless self-promotion

I seem to have lost the gift, if I ever had it, for what some of my old collegues would call (with a smile on their face) “shameless self-promotion”. Last week at Win-Dev attendees at my talks pointed out to me that I didn’t have a slide that advertised my email address, or web site […]