I recently received a question from Dave Ballantyne (blog|twitter) about returning succinct error messages from CLR code.

Let's have a look at a basic example of some CLR code where you might want to throw an exception:

private const string _passwordSalt = "--^p@$$w0rD-sAlT-";[Microsoft.SqlServer.Server.SqlFunction]public static SqlString fn_getPasswordHashCode(string password){    using (SHA256Managed hashProvider = new SHA256Managed())    {        byte[] hash = hashProvider.ComputeHash(             Encoding.UTF8.GetBytes(_passwordSalt + password));        StringBuilder output = new StringBuilder();        foreach (byte b in hash)        {            output.Append(b.ToString("X2"));        }        return output.ToString();    }}

So, this returns us a hex string that contains the hash of a user-supplied password. However, this code will return the hash of the salt if an empty string is passed, and will return a hash even if the input is NULL. We may well want to avoid those situations, so we could modify the code in order to throw an exception that notifies the user of this fact:

[Microsoft.SqlServer.Server.SqlFunction]public static SqlString fn_getPasswordHashCode(string password){    if (string.IsNullOrEmpty(password))    {        throw new ArgumentException(            "Supplied password cannot be null or empty.", "password");    }    using (SHA256Managed hashProvider = new SHA256Managed())    {        byte[] hash = hashProvider.ComputeHash(            Encoding.UTF8.GetBytes(_passwordSalt + password));        StringBuilder output = new StringBuilder();        foreach (byte b in hash)        {            output.Append(b.ToString("X2"));        }        return output.ToString();    }}

This returns us a particularly verbose and ugly error message:

What this is doing is returning a static string which tells us the name of the assembly module being executed, as well as giving us the Message and StackTrace members.

My first point of call was to see if deriving from ArgumentException would allow us to control that output. So I created a class 'MyArgumentException':

private class MyArgumentException : ArgumentException{    public MyArgumentException(string message, string paramName)        : base(message, paramName)    {    }    public override string Message    {        get        {            return null;        }    }    public override string StackTrace    {        get        {            return null;        }    }}

Once the code was modified to throw MyArgumentException instead of ArgumentException, the error message was as follows:

So, still pretty ugly - obviously this wasn't the way to go. At this point I was due elsewhere so I emailed Dave back to say I didn't think it was possible, and went on my merry way.

The next day, however, I did some more digging, and found that some people had been using SqlContext.Pipe.ExecuteAndSend(SqlCommand) in order to be able to send better error messages.

So I tried that, in a stored procedure, and it did what I wanted - almost. It returned the error to the client, but also returned the 6522 error afterwards. So I posted a connect item - Allow SQL CLR Exception messages to be passed more succinctly to the client - and Niels Burglund posted that you can wrap the ExecuteAndSend call in a try {} catch {} block to eat the .NET exception - and then the job's done. I let Dave know, and thought 'I'll write a blog post about that'.

It was only while I was thinking about how to write this very post, that I realised that method of returning errors is great for Stored Procedures - but for User Defined Functions the Pipe is not available - meaning you can't ExecuteAndSend, meaning no nice error message goodness.

So, if you want to be able to return nice error messages from the CLR, go vote for that connect item. Then hope.

One thing that I see a lot is people trying to export data from a stored procedure into a temporary table, and have the temporary table created based on the schema of the result set. There is a common solution using OPENROWSET which isn't ideal, but here it is:

sp_configure 'Show Advanced Options', 1GORECONFIGUREGOsp_configure 'Ad Hoc Distributed Queries', 1GORECONFIGUREGOSELECT * INTO #TempTable   FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_connection=yes;',                              'EXEC [TestDatabase].[dbo].[stproc_test]')SELECT * FROM #TempTable

Ok, so that works. Or does it? Does Server=(local) actually get us to the server we want? What about if we have multiple instances? Ok, so we could use @@SERVERNAME to build the provider string and... Oh. OPENROWSET won't accept anything but a string in there. Not an expression that results in a string, nor a variable of a string type.

Well, at least we can build up the SQL string to remove the hard-coded reference to the... Oh. OPENROWSET places similar restrictions on the SQL statement to be executed.

All in all, I would say this is a practice to be avoided. You will end up with hard-coded references to both your server and instance name and your database name in code - and, worst of all, they are in a string. Why is that a problem? Because it means that there is no tool that will validate this for you before the SQL gets run for the first time. You can see it now, the operations team move the database onto a new system, and keep the old system live as a GTH plan. The queries continue to run, merrily accessing the old data, making it look (initially at least) like the system is working. Then, a couple of weeks later, somebody who is writing 'an urgent report for the directors' suddenly realises that the data isn't updating, and hasn't been updating since the system move. Cue panic investigation from the operations team, who find the above, then have to try and find all instances of that style of code in the whole database.

Not exactly how to win friends and influence people.

There are a couple of extra views which give us some more information about SQL Modules. They relate to specific types of SQL Modules, namely procedures and triggers.

Let's look at [sys].[procedures] first, which gives us some more information about procedures, over and above the information in [sys].[objects]:

So, not really much extra information about procedures. Let's look at [sys].[triggers] which gives us information about triggers, and does not inherit columns from [sys].[objects]:

There seems to be quite a lot of information about triggers, but, in effect we're not told that much. We simply find out if the trigger is marked NOT FOR REPLICATION, whether it is enabled and whether it is an INSTEAD OF or AFTER trigger.

One thing that we still don't know about triggers is what events they fire on. For example - does a particular DML trigger fire on an INSERT, an UPDATE, a DELETE or a combination of the three? For the answer, we have to look to another table, [sys].[trigger_events]:

We now have all the information we need about triggers. A note, though, about the is_first and is_last columns - a lot of people mistakenly believe that you can actually specify the order in which multiple triggers fire. You can't. You can just specify that a trigger must execute first for a particular event, or that a trigger must execute last for a particular event, and not the explicit order of any other triggers in between. However, if you run across this problem, then you probably have bigger issues, as having more than 3 triggers on the same event type for the same parent object would not be a brilliant idea, performance-wise.

Next time we'll look at some of the metadata that surrounds the CLR objects, as well as the assemblies that contain them.

So what are SQL modules? Simply put, SQL modules are objects in the database that are programmed in SQL. This isn't entirely accurate, as there are a few SQL programmable object types that don't fall under the 'sql module' banner. These are:

• Computed columns
• Check constraints
• Default constraints

So, effectively, that leaves us with stored procedures, replication filter procedures, the three types of functions (scalar, in-line table-valued and multi-statement table-valued) as well as both DML and DDL triggers. Also, the legacy 'standalone rule' and 'standalone default' object definitions count as SQL modules. However, the less said about them the better, frankly! The meta-data for these objects is stored in the system view [sys].[sql_modules] - so let's look at what we're offered:

So we get nearly enough to create the script for all SQL modules from this one view. The major exception being the correction of object names for sql modules that have had their name changed using sp_rename. Interestingly, if a procedure is altered using ALTER PROCEDURE, then the definition in [sys].[sql_modules] will still contain the CREATE keyword.

About system stored procedures

System stored procedures are not magic, they are just SQL modules. Their defintion can be found in [sys].[all_sql_modules], in exactly the same format as for [sys].[sql_modules]. Ok, so they are slightly magic - they often reference internal objects that you simply don't have access to as a normal SQL user. But, looking at the source of system stored procedures can be a great way to learn more about the internals of SQL server, as well as giving you the basis for creating your own customised versions.

Next time we'll look at some of the extra meta-data that is available around SQL modules, specifically triggers, stored procedures and replication filter procedures.