No matter how hard you try at some point it might become necessary for you to add a method to an existing CLR User-Defined Type to support a new business rule, or if created a single assembly with all of your objects including your CLR UDT, you may need to modify an existing function or stored procedure to meet new requirements. If you find yourself in this situation, and you attempt a Deploy from Visual Studio, you will receive the following error:
------ Build started: Project: SQLCLR_Example, Configuration: Debug Any CPU ------
SQLCLR_Example -> C:\Documents and Settings\jkehayias\My Documents\Visual Studio 2005\Projects\SQLCLR_Example\SQLCLR_Example\bin\Debug\SQLCLR_Example.dll
Drop assembly: SQLCLR_Example.dll ...
------ Deploy started: Project: SQLCLR_Example, Configuration: Debug Any CPU ------
Deploying file: SQLCLR_Example.dll, Path: C:\Documents and Settings\jkehayias\My Documents\Visual Studio 2005\Projects\SQLCLR_Example\SQLCLR_Example\obj\Debug\SQLCLR_Example.dll ...
Error: Cannot drop type 'GeoLocation' because it is currently in use.
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
========== Deploy: 0 succeeded, 1 failed, 0 skipped =========
Or from TSQL if you explicitly issue the DROP TYPE command you will get:
Msg 3732, Level 16, State 1, Line 1
Cannot drop type 'GeoLocation' because it is currently in use.
This exception occurs as a result of dependency checking in the SQL Server Engine which prevents you from dropping a base object for which there are dependencies. This doesn't however; mean that updating the Assembly is necessarily impossible. In some/most cases, you will probably be able to manually deploy the Assembly to SQL Server using the ALTER ASSEMBLY command provided your changes don’t violate certain rules. To use ALTER ASSEMBLY, your changes can not:
1. Change the signature of any of the objects in the Assembly. If you need to change the input parameters or the output type of one of your objects you wont be able to ALTER the ASSEMBLY. To get around this, you will have to drop the function or procedure first, then ALTER the ASSEMBLY. If another Assembly references the changing Assembly, you will have to first cascade up the dependencies to drop all of the depending objects that reference the objects being dropped.
2. Change the indexability of that method. This is more specific to User Defined Functions which are deterministic. The function must remain Deterministic after the changes are made if it is used for a computed column in a table.
3. Change the FillRow method name for CLR table-valued functions.
4. Change the Accumulate and Terminate method signature for User-Defined Aggregates
In addition to this, if the assembly contains a User-Defined Type, you can only make the following changes:
1. You may modify public methods of the UDT as long as signatures and attributes are not change. For the GeoLocation UDT, this means that it must still have a Longitude and a Latitude.
2. You may add new public methods to the UDT.
3. You may modify any private method in anyway.
For the purposes of this example, we will modify the GeoLocation UDT from Part 2 of this series and add a public method called DistanceBetweenPoints which provides the distance between two GeoLocations in miles or kilometers. As a part of this, we will be adding a new User-Defined Function to the Assembly as well as two helper C# methods in the UserDefinedFunctions class. Once the DLL for the Assembly has been compiled, the first step to updating it in SQL Server is to drop any files associated with the assembly in the sys.assembly_files DMV. To do this, the following dynamic SQL statement can be issued:
DECLARE @AssemblyName sysname
DECLARE @DLLPath varchar(300)
SET @AssemblyName = 'SQLCLR_Example'
DECLARE @SqlCmd varchar(max)
SET @SqlCmd = ''
SELECT @SqlCmd = @SqlCmd + 'ALTER ASSEMBLY '+QUOTENAME(@AssemblyName)+' DROP FILE '''+b.name+'''; '
FROM sys.assemblies a INNER JOIN sys.assembly_files b
ON a.assembly_id = b.assembly_id
WHERE a.name = @AssemblyName
AND b.name != @AssemblyName
Once this has been done, you can issue the ALTER ASSEMBLY command to update the ASSEMBLY definition in SQL Server:
ALTER ASSEMBLY [SQLCLR_Example]
FROM 'C:\SQLCLR_Example\SQLCLR_Example\bin\Debug\SQLCLR_Example.dll';
To test that the ALTER actually changed the definition of the Assembly, you can issue the following TSQL Test.
DECLARE @Loc1 GeoLocation
DECLARE @Loc2 GeoLocation
SET @Loc1 = '-87.451142,27.981460'
SET @Loc2 = '-117.620300,47.968081'
SELECT @Loc1.DistanceFrom(@Loc2, 1)
The result should be 3413 miles roughly. Once you have validated that the Assembly has been updated, the only task left to do is add the new User-Defined Function definition to make use of the new UDF as well:
CREATE FUNCTION [dbo].[DistanceBetweenPoints](@lat1 [float], @lon1 [float], @lat2 [float], @lon2 [float], @returntype [int])
RETURNS [float] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLCLR_Example].[UserDefinedFunctions].[DistanceBetweenPoints]
If you followed this article correctly, then you should have altered the assembly and updated the object definitions manually for the Assembly.
For further reference please see the following in the Books Online: