How to script and load assemblies list to SQL Server?

Question

My SQL CLR procedure depends on log4net, it depends on System.Web.dll.

When I upload this System.Web.dll :

create assembly [System.Web]
from 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll'
with permission_set = unsafe;
go

I see that yet 20 other assemblies added. Ok. How can I script this/these assemblies and upload them from binary in script?

Answer 1

Good News (mostly)

If you have already loaded these assemblies into SQL Server, then you have two options. First, if you are using SSMS, you might be able to script out the assemblies. Just:

1. right-click on the database name in Object Explorer, go to "Tasks ⏵", and select "Generate Scripts...".
2. Choose "Select specific database objects" and choose the desires assemblies to script under "SQL Assemblies".
3. Click the "Next >" button
4. Select "Save as script file" and enter in the "File name:"
5. Click the "Next >" button
6. Click the "Next >" button

The scripted out objects might be in dependency order, in which case the CREATE ASSEMBLY statements don't need to be reordered.

If you get an error, you might need to save the report if you want the actual details. It could be:

SmoException: Cyclic dependencies found.

which is what I ran into when scripting out some .NET Framework libraries that I had loaded. Sooo....

The second option is to simply construct the CREATE ASSEMBLY statements by SELECTing their contents from the system catalog views:

SELECT N'CREATE ASSEMBLY ' + QUOTENAME(asm.[name]) + NCHAR(0x0A)
         + N' FROM ' + CONVERT(NVARCHAR(MAX), [content], 1) + NCHAR(0x0A)
         + N' WITH PERMISSION_SET = '
         + CASE asm.[permission_set]
                WHEN 3 THEN N'UNSAFE'
                ELSE asm.[permission_set_desc] COLLATE DATABASE_DEFAULT
           END
FROM   sys.assemblies asm
INNER JOIN sys.assembly_files asf
        ON asf.[assembly_id] = asm.[assembly_id]
WHERE  asf.[file_id] = 1
AND    asm.is_user_defined = 1
--AND    asm.[name] = N'{{assembly_name}}'
FOR XML AUTO, ELEMENTS;

Warning: Do NOT left-click on the returned XML value that should appear as a link and will open a new tab with what should be a formatted XML document if you do left-click on it. The returned value will most likely be too large for SSMS to handle (it certainly was on my system using SSMS 18.10) and it will just hang forever and you will need to forcibly kill the process (and I was just trying an assembly that was 4 MB). You likely have several assemblies that got loaded as dependencies and those sizes can add up quickly.

You will need to save the results by right-clicking anywhere in the "Results" tab and selecting "Save Results As...". I'm not sure if the "Save as type" matters as they are all text and there is only one value, but I usually select "All files (*.*)".

If the file is too big to open in a text editor or SSMS, you can uncomment the WHERE predicate filtering on [name] and do one at a time, or make it an IN list and do several at a time.

Also keep in mind that:

1. You will need to remove the <asm> and </asm> tags.
2. The values are not necessarily returned in an order that can be loaded, so you will have to test and might need to adjust the order of the CREATE ASSEMBLY statements.

Not-so-good News (potentially)

SQL Server checks the GAC for assemblies of the same name that are being referenced in SQL Server. If it finds any, it will check the version number and they must be the same (i.e. between the assembly in SQL Server and the one in the GAC). You will get an error if the version within SQL Server is different. This means, that if any of the .NET Framework assemblies that you are loading into SQL Server get updated via Windows Update (or even manual update of .NET), then you will probably need to drop these assemblies and reload them with what will then be the current version(s).