Extracting a SQLCLR assembly

Question

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?

Answer 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