Extracting a SQLCLR assembly
One of our in-house applications has a SQLCLR assembly which is currently experiencing problems. The developer is having problems recreating the issue using the version that is stored in our source control system, so he suspects that some code may have been released that was not uploaded to source control. Is there a way to extract a SQLCLR assembly into a .dll file so that he can reverse engineer it for analysis?
Solution 1:
There is indeed a way - the assemblies are stored in the sys.assembly_files
table in your database. Select everything from sys.assembly_files
and find the assembly_id
, then run the following code (changing the assembly_id
and SaveToFile
path first):
DECLARE @IMG_PATH VARBINARY(MAX)
DECLARE @ObjectToken INT
SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65536
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'D:\SqlServerProject1.dll', 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
In order for this to work though, you will need to enable the OLE Automation feature in Surface Area Configuration for Features on your SQL instance.
I pulled this code from here:
http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/1e00e656-f322-45ec-b4e0-83db748fa97a