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?
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
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: