By: Pavel Pawlowski | Updated: 2013-12-16 | Comments (9) | Related: > Common Runtime Language
Problem
You have imported an assembly into a SQL Server database to use the CLR functions and stored procedures it contains. However later, you lost the original .dll file and you would like to create the .dll file again from what's in the database. Scripting the assembly using the create script option doesn't help, because it scripts out the assembly as binary sequence instead of creating the .dll file. In this tip, we look at how you can recreate the .dll file.
Solution
There are several possibilities how to achieve the goal and export the assembly back to a .dll file. This tip will show one of the possible ways by using a simple SQL CLR stored procedure that we pass the assembly name and path where we would like to store the .dll file created again.
Getting Information About Assemblies
To be able to export the assemblies, we need to get information about them and of course their binary representation. Information about assemblies and their files are stored in two system table. In the sys.assemblies table information is stored about the assemblies itself, like name, id, permission set, timestamp of creation or latest modification. As a single assembly, can be represented by multiple physical files, a second system table sys.assembly_files exists. This table contains information about individual assembly files and also contains their content (binary representation) in a varbinary(max) field.
To get file names and their binary content for particular assembly we can use the below T-SQL code:
SELECT
af.name,
af.content
FROM sys.assemblies a
INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id
WHERE
a.name = 'Microsoft.SqlServer.Types'
CLR Stored Procedure to save assembly
As mentioned above we will use a CLR Stored procedure to do the job.
First we create a new SqlConnection to SQL Server with connection string "context connection=true". This creates the new connection in the context of the current session from which the CLR stored procedure is being called.
Once the connection is created we can create a new SqlCommand which will execute the above T-SQL query to fetch all file names and binary content for the provided assembly name. In a loop we will iterate though all the rows returned by the SqlDataReader and save the binary data for each file using FileStream.
public class AssemblyExporter
{
[SqlProcedure]
public static void SaveAssembly(string assemblyName, string destinationPath)
{
string sql = @"SELECT af.name, af.content FROM sys.assemblies a INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id WHERE a.name = @assemblyname";
using (SqlConnection conn = new SqlConnection("context connection=true")) //Create current context connection
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlParameter param = new SqlParameter("@assemblyname", SqlDbType.VarChar);
param.Value = assemblyName;
cmd.Parameters.Add(param);
cmd.Connection.Open(); //Open the context connetion
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read()) //Iterate through assembly files
{
string assemblyFileName = reader.GetString(0); //get assembly file name from the name (first) column
SqlBytes bytes = reader.GetSqlBytes(1); //get assembly binary data from the content (second) column
string outputFile = Path.Combine(destinationPath, assemblyFileName);
SqlContext.Pipe.Send(string.Format("Exporting assembly file [{0}] to [{1}]", assemblyFileName, outputFile)); //Send information about exported file back to the calling session
using (FileStream byteStream = new FileStream(outputFile, FileMode.CreateNew))
{
byteStream.Write(bytes.Value, 0, (int)bytes.Length);
byteStream.Close();
}
}
}
}
conn.Close();
}
}
}
Creating Assembly and Registering Stored Procedure
Once we build the AssemblyExporter assembly, we have to create the assembly in a SQL Server database and create the CLR Stored procedure. As the stored procedure access resources outside of SQL Server (it is saving the assembly files to disk), the assembly needs to be created with EXTERNAL_ACCESS permission set and the database has to be set to TRUSTWORTHY.
CREATE DATABASE [TestDB]
GO
ALTER DATABASE [TestDB] SET TRUSTWORTHY ON WITH ROLLBACK IMMEDIATE
GO
USE [TestDB]
GO
CREATE ASSEMBLY [AssemblyExporter]
AUTHORIZATION [dbo]
FROM 'C:\SQLCLR\AssemblyExporter.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE SCHEMA [AssemblyExporter] AUTHORIZATION [dbo]
GO
CREATE PROCEDURE [AssemblyExporter].[usp_SaveAssembly] (
@assemblyName [nvarchar](128), --Assembly Name
@destinationPath [nvarchar](256) --Destination path for export
)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [AssemblyExporter].[AssemblyExporter].[SaveAssembly]
GO
Testing Export
Once the assembly and CLR stored procedure created, we can test it as follows passing in the name of the assembly we want to recreate and the path where we want the .dll file created.
EXECUTE [AssemblyExporter].[usp_SaveAssembly]
@assemblyName = 'Microsoft.SqlServer.Types'
,@destinationPath = 'C:\SQLCLR'
GO
Please note, the current implementation accesses the disk drive under the credentials of the account the SQL Server service is running. Therefore to be able to export the assembly, the SQL Server service account needs appropriate access to the destination directory.
Next Steps
- Take a look on MSDN on sys.assemblies (Transact-SQL) and sys.assembly_files (Transact-SQL).
- Don't forget to check CLR Stored Procedures on MSDN.
- You can download a complete sample Visual Studio 2013 project here. For easier deployment create a C:\CLRSQL directory prior building the project. Project has a post build action which automatically copies compiled AssemblyExporter.dll file to this directory for easier deployment. You can use the script in AssemblyExporter.sql for easy creation of the assembly and CLR stored procedure.
- You can further elaborate on the sample and update it so the assembly is saved under the credentials of current user instead of the SQL Server service account. For this purpose take a look on the SqlContext.WindowsIdentity Property on MSDN.
About the author
Pavel Pawlowski is Lead BI Specialist in Tieto with focus on Microsoft SQL Server and Microsoft BI platform.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-12-16
