Dependency issues/errors synchronizing CLR Assemblies
I am getting errors while executing script generated by SQL Compare to synchronize CLR Assemblies on the target database. Here is my situation.
Assembly A with
- a public class named A_CLASS1
- no UDT or UDF
Assembly B with
- UDT named B_UDT1 that depends on A_CLASS1
- UDF named B_UDF1 that depends on B_UDT1
Assembly C with
- UDF named C_UDF1 with no dependency on any
- CLR stored procedure named C_CLRProc1 that depends on B_UDT1
A T-SQL stored procedure named TSQL_Proc1 that depends on B_UDT1 and B_UDF1
A T-SQL stored procedure named TSQL_Proc2 that depends on B_UDT1 and B_UDF1
As you can see that
- Assembly C depends on B
- assembly B depends on A
- T-SQL stored procedures TSQL_Proc1 and TSQL_Proc2 depend on assembly B
After I change all three of these assemblies in my development database I run SQL Compare to synchronize these assemblies to my QA database. I have the following option selected in SQL Compare -
Do not use ALTER ASSEMBLY to change CLR types
I include all the dependencies in the synchronization script by selecting SQL Compare option "Include dependencies". SQL Compare generates a script that looks something like below. I have added line numbers and "..." to truncate long statements just for convenience and better readability.
Line# 01 DROP PROCEDURE C_CLRProc1
Line# 02 DROP FUNCTION C_UDF1
Line# 03 DROP FUNCTION B_UDF1
Line# 04 DROP TYPE B_UDT1
Line# 05 DROP ASSEMBLY A
Line# 06 DROP ASSEMBLY B
Line# 07 DROP ASSEMBLY C
Line# 08 CREATE ASSEMBLY A ...
Line# 09 CREATE ASSEMBLY B ...
Line# 10 CREATE ASSEMBLY C ...
Line# 11 CREATE TYPE B_UDT1 ...
Line# 12 CREATE FUNCTION B_UDF1 ...
Line# 13 CREATE PROCEDURE C_CLRProc1 ...
Line# 14 CREATE FUNCTION C_UDF1 ...
Line# 15 ALTER PROCEDURE TSQL_Proc1 ...
When I run this script on the QA database to synchronize it, the script results in errors because of the following problems with the script.
- Line# 03 and 04 throw error because, B_UDF1 and B_UDT1 can not be dropped unless T-SQL procedures TSQL_Proc1 and TSQL_Proc2 are dropped first because of the dependency. But as you can see T-SQL procedures TSQL_Proc1 and TSQL_Proc2 are never dropped in the script.
- Line# 05 and 06 throw error because, assembly A can not be dropped unless assembly B is dropped first because of the dependency. Likewise, assembly B can not be dropped unless assembly C is dropped first because of the dependency.
So my consulsion is
- SQL Compare is unable to detect T-SQL procedures that depend on UDTs and UDFs that are being altered or dropped.
- SQL Compare places "DROP ASSEMBLY" statements in reverse order of what the correct order should be.
I have been looking at the forums but seems like no one else is having such a problem. Can anyone tell me what I am doing wrong here?
Thanks a lot.