Dependency issues/errors synchronizing CLR Assemblies
jmatap
Posts: 3
Hello
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.
- jmatap
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.
- jmatap
This discussion has been closed.
Comments
Have you tried to synchronize without the 'Do not use ALTER ASSEMBLY to change CLR types' option? This seems to make a difference on my similar setup. Without this option, I can synchronize the assembly, but with it, I also get Please let me know if this works.
Thanks for the response.
I have tried without the 'Do not use ALTER ASSEMBLY to change CLR types' option but that doesn't always work because as per MSDN -
============================
ALTER ASSEMBLY statement cannot be used to change the following:
- The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server cannot rebind .NET Framework database objects in SQL Server with the new version of the assembly.
- The signatures of methods in the assembly that are called from other assemblies.
- The list of assemblies that depend on the assembly, as referenced in the DependentList property of the assembly.
- The indexability of a method, unless there are no indexes or persisted computed columns depending on that method, either directly or indirectly.
- The FillRow method name attribute for CLR table-valued functions.
- The Accumulate and Terminate method signature for user-defined aggregates.
============================
I am trying to find a solution that will always work while deploying CLR Assemblies to QA database.
Thanks.
- jmatap
I think there could be a problem if you need to drop and recreate the assemblies when SQL Compare finds a difference. If there is a dependency between assemblies, for instance you create a UserDefinedType and store it in one assembly, then create a second assembly that 'references' the first assembly, SQL Compare cannot detect these dependencies because they don't appear in any of the object creation (SQL DDL) scripts because they exist in the .NET code.
If SQL Compare needs to drop and create multiple assemblies that reference each other, there is a chance of failure.
If there is a way around this, we'll try to come up with a solution for the next version.