Checking for dangerous attributes at catalog time

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

Good XQuery Books

Because of SQL Server 2005’s support of XQuery, a lot of folks have been asking about good XQuery-specific books. I’ve found two excellent books, each with a different focus. “XQuery From The Experts” is a compendium of essays by the folks in the W3C XQuery working group. It’s an excellent treatise of how XQuery is […]

At the airlift

I’m at the Ascend program launch this week; it’s being launched as part of a larger event. Yes, I finally got to meet Roger Doherty. I’m doing a day-long (actually a very long day-long) presentation of SQL Server 2005 new features for developers tomorrow. Today, I’m listening to Larry Chesnut speak about “DBA and scalability […]

Using “ordinary” class libraries with SQL Server 2005

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

SQLCLR Optimizations – 2

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

Error 6522 and You

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

SQLCLR Optimizations – 1

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

HTTP endpoint session support in SQL Server 2005

I've been trying out the new session support in HTTP endpoints. You manipulate this through SOAP headers, and looking through the list of SOAP headers supported allowed me to understand this feature from an entirely different perspective. It looks like the session support makes this into more of an alternate client stack than a mechanism […]

Better XML to DataSet inference in .NET 2.0

When you load XML into an ADO.NET DataSet using it's ReadXml() method, ADO.NET uses a fixed set of rules to determine how to infer a relational structure for the XML. These rules are doc'd in the .NET framework docs. One of the most frustrating rules was that you couldn't have the same attribute name or […]

UDTs and UDAggs – Serialization and Construction

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

SqlTypes.SqlXml and XmlReader.Create

As Mark Fussell will tell you, one of the coolest things in System.Xml 2.0 is the static creator methods on XmlReader and XmlWriter. It made his top 10 list, albeit at number 10. I've been looking into how the client side handles SQL Server 2005's XML data type and was bemoaning the fact that System.Data.SqlTypes.SqlXml […]

Fun with displaying UDTs in SSMS

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

Where did the netlibs go?

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

How do you refer to …?

I was writing slides and labs last week. I read Dare’s discussion of “The Sex and Cash theory“ about doing what you think is cool vs doing what you do for cash (work) that may not be as “sexy“. Didn’t feel so bad after that. My discussions with others revolved around the “official” words and terminolgy for various […]

SMO and object-relational mapping

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

On SELECT statements and SqlDependency

There have been a number of questions recently (well OK, three) on the beta newsgroups about SqlDependency problems. It's been suggested that SqlDependency doesn't work in Whidbey Beta 1. The reason for this is that SqlDependency (and, of course, its lower-level cousin SqlNotificationRequest) requires a “valid” notify-able SQL query to work. What's a “valid”  notify-able query […]