Installing a SQL Server CLR Stored Procedure as a Hex String

Introduction

This tip is how to turn a .NET CLR stored procedure assembly into a hex string in a SQL script for installation. This allows installing the CLR stored procedure without having to read the .dll file from the file system.

Using the Code

This is using the .NET framework. I wrote it in Powershell, but it can easily be translated to any language.

The script below takes:

• $assemblySqlName - the name you want the assembly called in SQL Server
• $dotNetClrAssemblyPath - the full path to the .NET assembly (.dll file)
• $outputSqlFilePath - the output path for the SQL file to write

The SQL file that is produced has the T-SQL command to import the hex string as a .NET CLR assembly. For a full walk-through of installing a CLR stored procedure, see my tip Installing a CLR Stored Procedure on SQL Server.

# Reads in a .NET assembly file, and outputs as a hex string in a .sql file.
function Get-ClrAsSqlHex {
    param(
        $assemblySqlName, # What do you want the assembly called in SQL Server?
        $dotNetClrAssemblyPath, # Where can the .dll be read as a file from?
        $outputSqlFilePath) # Where do you want the SQL file written to?
    
    # Make sure the .dll exists
    if(![system.io.File]::Exists($dotNetClrAssemblyPath)){
        write-output ("File not found: {0}" -f $dotNetClrAssemblyPath)
        exit 1
    }

    # Get the file as a hex string
    $hex = new-object System.Text.StringBuilder
    get-content $dotNetClrAssemblyPath -Encoding Byte | %{ $hex.Append("{0:X2}" -f $_) > $null }
    $hex = $hex.ToString()

    # Write the hex string out in the SQL command to install it.
    $sw = new-object System.IO.StreamWriter $outputFilePath
    $sw.WriteLine("create assembly {0} from" -f $assemblySqlName)
    $sw.WriteLine("0x{0}" -f $hex)
    $sw.WriteLine("with permission_set = external_access;")
    
    $sw.Close()
}

To run the script:

Get-ClrAsSqlHex -assemblySqlName 'MyClrAssembly' 
-dotNetClrAssemblyPath 'C:\temp\dotNetClrAssembly.dll' -outputSqlFilePath 'C:\temp\dotNetClrSqlHex.sql'

Points of Interest

One other feature of this that I like is it allows the CLR Procedure that was installed to be source controlled as a SQL text file.

History

• 2015-07-20 - Initial submission