Category Archives: clr

Extract CLR Assemblies from SQL Server

I’ve run into a few situations that required examining the existing CLR assemblies on a server. Whether I needed to do a comparison between two versions to make sure they are the same or confirm something in the assembly itself, this script has come in handy. Point it at a database, give it an output path and it will save all the assemblies in that database to dlls in the given folder. You can then use a .NET disassembler to confirm suspicions or a binary comparison to make sure the dll matches what it should.

<#
    .SYNOPSIS
        Extracts CLR Assemblies from a SQL 2005+ database.
 
    .DESCRIPTION
        Extracts CLR Assemblies from a SQL 2005+ database.
 
    .PARAMETER  ServerInstance
        The Server\Instance to connect to
    .PARAMETER  Database
        The Database to extract assemblies from
    .PARAMETER  OutPath
        The path to output the assemblies
 
    .EXAMPLE
        PS C:\> .\Get-SqlAssemblies.ps1 -ServerInstance 'MyServer\MyInstance' -Database 'MyDatabase'
        This example shows how to call Get-SqlAssemblies with named parameters.
 
    .INPUTS
        System.String
 
    .NOTES
        For more information about advanced functions, call Get-Help with any
        of the topics in the links listed below.
 
#>
param(
    [string]$ServerInstance = 'LOCALHOST',
 
    [Parameter(Mandatory=$true)]
    [string]$Database,
 
    [string]$OutPath = '.'
)
 
#Correct for variations of incoming ServerInstance names
if(-not $ServerInstance.Contains('\')) {$ServerInstance += '\DEFAULT'}
if($ServerInstance.Contains(',') -and -not $ServerInstance.Contains('`,')) {$ServerInstance = $ServerInstance.Replace(',', '`,')}
 
dir SQLSERVER:\SQL\$ServerInstance\Databases\$Database\Assemblies | %{
    $_.SqlAssemblyFiles | %{
        $str = $_.name
        $path = Join-Path $OutPath ($str.Substring($str.LastIndexOf('\')+1))
        Set-Content -Path $path -Value $_.GetFileBytes() -Encoding byte;
    }
}

Using .NET’s TryParse() in SQL CLR

In my current position, I deal with a lot of data quality issues. This column should be a date, but we get an integer, string, NULLs, etc. As a rule of our processing, if we cannot convert the value to the appropriate data type, then we use a default value, or choice of values depending on the situation.

SQL Server already has the CAST and CONVERT functions built in. There is a shortcoming of these functions though. If the value cannot be converted, you get an error and your processing stops. For converting values, you have to pass through the table twice. Once to manually convert any known values that cannot convert into something that can or set them to NULL. Then you can run CAST or CONVERT on the values. We wanted to be able to do this in one step and this led us to .NET’s TryParse() method.

Each basic data type in .NET has a TryParse() method that attempts to parse the value and returns a boolean if successful. The parsed value is passed back through an output variable. In my case, I don’t really care about the boolean return value. I want a value if it was successful, otherwise give me a NULL. This is the syntax for creating the SQL CLR version.

And the syntax for using it within SQL.

Why would I want NULL on failure you might ask. Well, if I get a NULL back, then I can easily check against NULL for success and use functions such as COALESCE to substitute a default value if the parsing failed. A good case for us to use this is when we have blank strings that need to convert into a default date. CONVERT cannot cast the value ” to a datetime and thus throws an error.

Now I can do things like the following to allow processing to complete if CONVERT hits a bad value.

Now the drawbacks of using this method. If you need to make sure you have all valid values beforehand, this will cause those to fail silently. An alternative would be to use this to check for invalid values. You can do something like this to find problematic values:

This allows you to find all unconvertible values without SQL throwing an error as it would if you had tested with CONVERT(). We mainly use this function in the second situation. We want to know all values that would cause problems.

I have also made versions for Int, BigInt and Money following the same basic pattern. As with any CLR functions, make sure you know the security and performance implications when using it. Hopefully if you do a lot of data staging then converting this could make your life easier.