Compiled T-SQL? Don’t throw away all your SQLCLR code just yet

The speed increase from memory-optimized tables in SQL Server 2014 is nice, but I thought, to get the most bang for your buck for really need to use compiled stored procedures in addition. After looking at some of the C code these produce, it almost looked like I was doing direct vector branching into the […]

Using filestream, streaming I/O and SQL logins with impersonation

A number of months ago, I wrote an article using the SQL Server filestream feature programmatically. I've gotten a number of inquiries since from folks who *need* to login to SQL Server using SQL logins (often the same SQL login for everyone), but want to access the stream using streaming I/O (e.g. SqlFileStream data type). […]

Does everybody get that? (SQLCLR)

Just came back from TechEd, where I had a bit of time to think about training vs speaking. When I'm teaching, I'm not just speaking facts and doing neat demos (ta da!) at folks. I look at their eyes to make sure they "get it" and aren't satisfied until they do. During the presentation, I'd […]

Adventures in autodeploy: SQL Server 2012, SSDT, and SQLCLR

I'm the kind of person that doesn't often/always use Visual Studio "autodeploy" (that is, the "Deploy Solution" option) for SQLCLR projects. It's always been missing things (like ALTER ASSEMBLY) and never quite kept up with the newer things you could do with SQLCLR (e.g. ordered table-valued functions, multiparameter aggregates). But every once-in-a-while, especially while I'm […]

SQLCLR .NET 4-based behavior differences in SQL Server 2012

As I mentioned in a few previous blog entries, SQL Server 2012 runs the .NET 4 version inside of SQL Server. Your SQLCLR code may be affected by some behavior differences between .NET versions 2.0/3.5/3.5 SP1 and .NET 4.0. I like the way SQL Server chose to deal with these differences. Although the documentation on these differences […]

Per-appdomain diagnostic info in SQL Server 2012 SQLCLR

In SQL Server 2012, SQLCLR now loads .NET 4.0 code rather than .NET 2.0 as in previous versions of SQL Server. Note that the "main system" assemblies (e.g. mscorlib.dll, System.Data.dll) in .NET did not appear as a separate directory, between version 2.0 and 4.0; instead, the .NET 2.0 librares were updated. As an example, SQL […]

A quick set of SQLCLR spatial decomposition functions

Saw a request on the SQL Server spatial forum this morning for routines that would decompose a geography type to a set of Lat/Longs. A while ago, I was experiemnting with spatial decomposition, and happened to cruft up a couple of SQLCLR table-valued functions to accomplish this. Although the same functions could be written in […]

SQLCLR-based libraries available from TotallySQL

I’ve always been interested in SQLCLR, and wondered about the potential for producing libraries of useful functions for a variety of specialist purposes. With the inclusion of the spatial and hierarchyid data types in SQL Server 2008 as well as a few SQLCLR-based functions in SQL Server Denali, you can’t help but wonder if the […]

Denali memory allocator changes and SQLCLR

Once upon a time (well, now, currently), there were two memory allocators in SQL Server, the single-page allocator and the multi-page allocator. The single-page allocator was used for almost everything (data buffers, caches, etc); the multi-page allocator was used for somewhat more esoteric things. Like most of SQLCLR (that's CLR code that runs inside SQL […]

Hold it right there, you’re a user-defined aggregate

I've been trying out the windowing extensions (the OVER clause extensions and friends) with different kinds of functions (aggregate functions, first_value, last_value() etc), and thought I'd use a SQLCLR aggregate. After all, they're just "regular" aggregates, right? You can't build a user-defined ranking aggregate in SQLCLR, so they can't be confused with one of those. […]

What appears in DMVs for SQLCLR?

A variation of the following question came up in a mail list that I’m on. Can you see currently executing SQLCLR code statements (i.e. the underlying C# code) in any of the sys.dm_exec… DMVs? Or anywhere else? This turned out to be more interesting than I thought. As far as the engine internals are concerned, […]

SQLCLR debugging and VS 2010 revisited

In my blog entry from about a month and a half ago, I summarized my finding on using Visual Studio 2010 with SQLCLR, and the partial support of SQL Server 2008 new features. One feature (with the disclaimer that's it's not preferred/supported) that I thought I'd lost was "attach to process" style debugging with SQLCLR. […]

Visual Studio 2010 and SQLCLR 2008 new features

There's a special kind of project for SQLCLR development in Visual Studio. It's been around since Visual Studio 2005, when SQLCLR was introduced in SQL Server 2005. When SQL Server 2008 was released, there were a bunch of new features introduced in the SQLCLR arena. Unfortunately, Visual Studio 2008 supported none of them. We were […]

Temporary table lifetime and SQLCLR stored procedures

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

On SQL Server and .NET 4.0

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

SQLCLR interop between SQL Server versions and Visual Studio multi-targeting

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

SQLCLR default procedure parameters scripts correctly by SSMS and SMO 2008

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

Visual Studio 2008 SP1 and SQL Server 2008 SQLCLR features

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

A helper function for ring-orientation in the SQL Server 2008 geography data type

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

Semantic (possibly breaking) change in SQLCLR TVFs

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

How do you shutdown a running SQLCLR appdomain?

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

DDL appdomains appear in SQL Server log in SQL Server 2008

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

Installing .NET 3.5 on a SQL Server 2005 machine…be careful

Recently I had the occasion to load .NET 3.5 on to a machine that had an existing instance of SQL Server 2005. .NET 3.5 does not work by versioning the “main .NET assemblies” (e.g. there is no “version 3.5″ of mscorlib.dll, System.dll) but by replacing the 2.0 versions of them. You can observe this by […]

LINQ and LINQ to XML to appear in SQL Server 2008 SQLCLR approved list

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

Large user-defined types and aggregates in SQL Server 2008

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

SQL Server 2008: Ordered SQLCLR table-valued functions

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

SQLCLR in SQL Server 2008: Multi-input user-defined aggregates

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

Using the SQL Server 2008 UDTs on clients

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

SQL Server 2008 and .NET framework versions

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

SQLCLR and system functionality in SQL Server 2008 – part 3

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

SQLCLR and system functionality in SQL Server 2008 – part 2

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

SQLCLR and system functionality in SQL Server 2008 – part 1

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

.NET Nullable Types as SQLCLR parameters in SQL Server 2008

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

ORDPATH, ORDPATH, everywhere

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

SQL Server system data types implemented in .NET

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

Two things you can’t do in SQLCLR

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

Using SQLCLR functions in indexed views

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

LINQ/EDM in SQLCLR?

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

What’s that stuff good for, anyway?

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

Multiple synthesized rowsets with SqlDataRecord

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

New “SQLCLR-approved” assembly in SP1

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

SQLCLR supports assembly versioning…NOT

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

Poking at SQLCLR

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

More info about System.Configuration

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

New samples; Calling Oracle from SQLCLR

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

Using System.Configuration.dll in .NET sprocs and UDFs

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

Ownership chains and .NET procedure data access code

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

Seeing errors in callers, SQLCLR error 6522, and T-SQL TRY-CATCH

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

SQL Server 200x wish list – part 5, SQLCLR

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

Hello again. and SQL Server 200x wish list part 1

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

What’s that source code doin’ in there?

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

Security in SQL Server 2005 – unsafe assemblies in Sept CTP

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

SQLCLR, transaction nesting, and TransactionScope

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

IsNull != IS NULL, the reprise and the solution

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

SqlTypes.SqlXml and impersonation

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

Why my cat can’t use a SQLCLR proc to read files

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

What’s a truncation exception?

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

Combined provider: transactions and the return of 6522

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

A first demo of the combined data provider

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

What do you mean by “unsupported”?

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

DROP ASSEMBLY change – just a convenience?

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

SQL Server unified provider info on DataWorks’ team blog

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

About the new SQLCLR TVFs

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

Don’t try this at home…yet

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

Tracking the pesky appdomain

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

Merging SqlClient and SqlServer providers

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

SqlContext syntactic changes

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

Using inheritence in SQLCLR UDTs

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

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

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

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

One more thing about 6522 error

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

Error 6522:the reprise

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

How SQLCLR works when its disabled

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

Using FX assemblies outside of “the list”

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

More about SQLCLR approved assemblies

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

SQLCLR – List of approved framework class libraries

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

What’s all that stuff in sys.assembly_files?

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

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

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

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

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

No more Microsoft.VisualStudio.DataTools.SqlAttributes.dll

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

“Rite of passage” programs and SQLCLR

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