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).