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.
function Get-ClrAsSqlHex {
param(
$assemblySqlName,
$dotNetClrAssemblyPath,
$outputSqlFilePath)
if(![system.io.File]::Exists($dotNetClrAssemblyPath)){
write-output ("File not found: {0}" -f $dotNetClrAssemblyPath)
exit 1
}
$hex = new-object System.Text.StringBuilder
get-content $dotNetClrAssemblyPath -Encoding Byte | %{ $hex.Append("{0:X2}" -f $_) > $null }
$hex = $hex.ToString()
$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