How do I update an assembly and its dependent assemblies in MS-SQL?

Question

This is the situation:

I have a Trigger.dll and a Trigger.XmlSerializer.dll.

I use CREATE ASSEMBLY to register them in MSSQL.

Now, I have compiled new versions of both.

I want to use ALTER ASSEMBLY to update them, however you can only update one at a time. If you try to update one that has a dependency, it complains. What's the trick to doing this?

I don't want to drop and recreate as I have to then drop all the triggers, etc. and there is database downtime.

Answer 1

I fear dropping the table and re-creating it is the only way.

The main reason for this is that values stored in a type in an assembly, are unusable if you update the assembly to a new version.

Answer 2

according to the microsoft support you can use it by trick.

Notes

• These steps upgrade or downgrade assembly A in the scenario that was described in the "How to upgrade or downgrade an assembly" section.
• This example assumes that the versions of the two assemblies are both 1.0.0.0 and the assemblies are both written in C#. When you follow these steps, you try to upgrade assembly A and assembly B to version 2.0.0.0.

To upgrade or downgrade assembly A, follow these steps.

1. Back up version 1.0.0.0 of assembly B to a folder.
2. Modify and then recompile assembly B to version 2.0.0.0.
3. Use the ALTER ASSEMBLY statement to upgrade assembly B in SQL Server 2005.
4. Modify and then recompile assembly A to version 2.0.0.0. When you do this, reference version 1.0.0.0 of assembly B from the backup that you made in step 1. To do this, use the Csc.exe compiler tool together with the /reference switch. For example, use the following command: csc /target:library /out:AssemblyA.dll AssemblyA.cs AssemblyInfo.cs /reference:"BackupFolder\AssemblyB.dll" Note To verify the version of assembly B in the metadata of assembly A, open assembly A by using the Ildasm.exe utility. Then, verify the metadata information for the referenced assembly under the MANIFEST section.
5. Use the ALTER ASSEMBLY statement to upgrade assembly A in SQL Server 2005.