How to update a CLR Assembly without dropping assembly from SQL Server

Question

How can i update a CLR function (or procedure) assembly dll without having to drop and re-create the assembly in SQL Server (2008 R2)?

As it stands now if i update an assembly (e.g. to add a new function), SQL Server will not honor the updated dll until i drop the assembly:

DROP ASSEMBLY CLRFunctions

Msg 6590, Level 16, State 1, Line 1
DROP ASSEMBLY failed because 'CLRFunctions' is referenced by object 'NormalizeString'.

But before i can drop the assembly, i must first drop all functions that reference it:

DROP FUNCTION NormalizeString
DROP FUNCTION RemoveDiacritics
DROP FUNCTION RemoveCombiningDiacritics
DROP FUNCTION CombineLigatures
....
DROP FUNCTION PseudolocalizeArabic

And then i can drop the assembly:

DROP ASSEMBLY CLRFunctions

Now i have to "create" the assembly:

CREATE ASSEMBLY CLRFunctions FROM 'c:\foos\CLRFunctions.dll';

And now i have to hunt the declaration of all the UDF's that were registered before i deleted them.

i would rather update an assembly, and have SQL Server begin using it.

---

Update: i randomly tried DBCC FREEPROCCACHE to force a "recompile", but SQL Server still uses the old code.

Update: i deleted the assembly dll CLRFunctions.dll, and SQL Server is still able to run the code (without code that should be impossible).

Solution 1

I think you're looking for alter assembly. From BOL:

If the FROM clause is specified, ALTER ASSEMBLY updates the assembly with respect to the latest copies of the modules provided. Because there might be CLR functions, stored procedures, triggers, data types, and user-defined aggregate functions in the instance of SQL Server that are already defined against the assembly, the ALTER ASSEMBLY statement rebinds them to the latest implementation of the assembly. To accomplish this rebinding, the methods that map to CLR functions, stored procedures, and triggers must still exist in the modified assembly with the same signatures. The classes that implement CLR user-defined types and user-defined aggregate functions must still satisfy the requirements for being a user-defined type or aggregate.

One of the examples on the same page seems like it'd do the trick:

ALTER ASSEMBLY ComplexNumber 
FROM 'C:\Program Files\Microsoft SQL Server\90\Tools\Samples\1033\Engine\Programmability\CLR\UserDefinedDataType\CS\ComplexNumber\obj\Debug\ComplexNumber.dll' 

Solution 2

To add to Ben Thul's answer, this can be accomplished remotely fairly easily via SQL Server Management Studio's GUI.

1. Under the Object Explorer for your database -> Programmability, right click on Assemblies and select 'New Assembly...'.

2. Browse to your updated DLL.

3. Instead of clicking 'OK' (which will fail, as an assembly of the same name already exists) click 'Script' at the top of the New Assembly window.
    
   You will be dropped into a SQL Query that includes a 'CREATE ASSEMBLY' line followed by a huge blob that is the DLL you just selected.

4. Change 'CREATE' to 'ALTER' and then execute!

The Script also created an 'AUTHORIZATION' line for me that I had to remove before executing; your milage may vary.

I hope this helps someone else without filesystem access to their servers.

Hopefully Microsoft will make this a first-class operation in SSMS someday, but this is a fairly easy workaround until they do.

Solution 3

i found a hint at the answer on Stackoverflow:

ALTER ASSEMBLY CLRFunctions FROM 'c:\foos\CLRFunctions.dll';

Comments

[Zarepheth]

Can this be done when the updated assembly is located on the SSMS client machine rather than the SQL Server host machine? I do not have sufficient privileges on the server to directly access its file system, but I do have sufficient rights to add and remove CLR assemblies.

[Ben Thul]

No. Well, mostly no. You can specify a UNC path (i.e. \\server\path\to\file) and as long as the service account that the SQL engine is running under has read permissions on the file, it should work. The other option is to specify the binary value for the assembly. If you already have it deployed on another server, scripting the alter from there will get you the blob value.

[Zarepheth]

Yeah, that's what I thought. :( Perhaps a newer version of SSMS will allow updating assemblies from a remote machine. In the meantime, I guess I drop and create assemblies via the SSMS GUI -- and perform DROP and CREATE operations for all the dependent functions.

[Ben Thul]

I wouldn't hold my breath on that one. As far as having to drop and recreate, why can't you exercise either of the methods outlined above?

[Zarepheth]

I can use T-SQL for anything that does not require a file-system reference and is generally available to people with DBO rights on specific databases. Adding and Altering assemblies requires a file-system reference. A UNC path might work, but I'd have to mess with my machine's local firewall (assuming that corp security hasn't locked it down) and hope that SQL Server runs with a security context that can access my machine. The SSMS GUI can upload files from my machine to the remote server - so I can use it for adding assemblies.

[Ben Thul]

"Adding and Altering assemblies requires a file-system reference." - this is not true. Both CREATE ASSEMBLY and ALTER ASSEMBLY will take a blob that represents the assembly. Prove this to yourself by going to any database created on 2008+ and go to Programmability -> Assemblies and script the creation of the Microsoft.SqlServer.Types assembly. That gigantic varbinary is the assembly. As this applies to your situation, deploy your assembly to your local instance, script it out, and make it an ALTER ASSEMBLY script.

[Zarepheth]

I'll to keep that in mind! I didn't realize I could provide a BLOB, though that might be how SSMS uploads my assemblies.

[miroxlav]

You meant ALTER ASSEMBLY... (UPDATE is in DML, ALTER is in DDL.)