Assembly redirection is not supported in SQL CLR. Assembly redirection can be done using publisher, application configuration and machine configuration files. SQL Server 2005 uses application configuration to redirect assembly call after Alter Assembly. So let’s suppose there is an assembly A calling assembly B and the user will run Alter Assembly command for B (modifying the version). In this case, SQL Server will write an application configuration in memory to redirect the calls of A to the new version of B overwriting any other application configuration filer ?. This is the reason for what why SQL Server does not take in consideration the user specified redirection in application configuration file.
The story is different for machine and publisher configuration file. If there is a redirection of the of the assembly B from one version (let’ s say 1.1.1) to other(1.1.5) and there were not Alter Assembly statements inside of the database to redirect the version 1.1.1 to 1.1.3 the attempt to load assembly B in the memory will fail. This can cause Create Assembly, Alter Assembly or executing the code inside of the assembly to fail. If there are Alter Assembly statements to match the redirection, the assembly can be load in the memory so it can be altered or executed. I mention Alter Assembly statements because final assembly redirection can come after multiple assembly steps: Alter Assembly from 1.1.1 to 1.1.2 and Alter it again from 1.1.2 to 1.1.3.
The entry in the application configuration from the memory for an assembly will be deleted when the assembly is dropped from the database.
The redirection resulted from an Alter Assembly will also have the following effect: if a user registers Assembly A version 1.0 and alter it to version 2.0 he will not be able to register again assembly A version 1.0 until he drops Assembly A version 2.0. After the drop he will be able to register both of the versions of the assembly since the application policy was updated.
Also note that SQL Server will distinguish between multiple versions of the same assembly only for strong named (or signed) assemblies
In my tests I used Fuslogvw.exe to see the redirection and loads attempts.
This posting is provided “AS IS” with no warranties, and confers no rights.
Cross Posted from http://blogs.microsoft.com/sqlclr