Drop assembly-dependent objects prior to dropping assembly
shadowbob
Posts: 71 Bronze 2
I apologize if this has been posted before. The only issue I consistently have with SQL Compare is the order of operations when updating an assembly.
Let's say I have an assembly with a table-valued user defined function in it. I deploy this initially using Visual Studio. This creates both an ASSEMBLY and a FUNCTION object within the database. I then deploy a copy of this database to a production server. Later, I find that I need to update the assembly to fix a bug. I do so, again using VS to deploy the fix to the development copy of the database. Finally, I'd like to update the production database by using SQL Compare.
SQL Compare correctly notices that the assembly needs to be updated. However, SQL Server doesn't allow you to drop an assembly unless you first drop all of the objects that refer to it (in this case, the UDF). SQL Compare doesn't know this, so it just blindly tries to DROP ASSEMBLY, which always fails. At this point I have to intervene and manually drop the UDF. A subsequent run of Compare will then work.
The fix is to have SQL Compare first drop all dependent objects (the UDF in this example), drop the assembly, create the (new) assembly, and recreate the UDFs.
I've used SQL Compare for a few years now and this is the first and only issue I've ever had with it. Thanks for another great product!
Let's say I have an assembly with a table-valued user defined function in it. I deploy this initially using Visual Studio. This creates both an ASSEMBLY and a FUNCTION object within the database. I then deploy a copy of this database to a production server. Later, I find that I need to update the assembly to fix a bug. I do so, again using VS to deploy the fix to the development copy of the database. Finally, I'd like to update the production database by using SQL Compare.
SQL Compare correctly notices that the assembly needs to be updated. However, SQL Server doesn't allow you to drop an assembly unless you first drop all of the objects that refer to it (in this case, the UDF). SQL Compare doesn't know this, so it just blindly tries to DROP ASSEMBLY, which always fails. At this point I have to intervene and manually drop the UDF. A subsequent run of Compare will then work.
The fix is to have SQL Compare first drop all dependent objects (the UDF in this example), drop the assembly, create the (new) assembly, and recreate the UDFs.
I've used SQL Compare for a few years now and this is the first and only issue I've ever had with it. Thanks for another great product!
Jeremy Fuller
VP, Software Development
Organizational Intelligence, Inc.
VP, Software Development
Organizational Intelligence, Inc.
Comments
Can you please confirm if you have enabled the "Do not use ALTER ASSEMBLY to change CLR objects"? By default this option is turned off.
When this option is selected, SQL Compare will drop and re-create any CLR assemblies that are to be synchronized.
Many Thanks
Eddie
Eddie Davis
Product Support Engineer
Red Gate Software Ltd
e-mail: support@red-gate.com
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
VP, Software Development
Organizational Intelligence, Inc.
I suspect that the "Do not use ALTER ASSEMBLY to change CLR objects" option may help resolve your issue.
Have you enabled this option? Does this option resolve your issue?
If the option does not help in the scenario you have described, I will submitt a feature request / bug report, for SQL Compare to operate in the manner that you are seeking.
Many Thanks
Eddie
Eddie Davis
Product Support Engineer
Red Gate Software Ltd
e-mail: support@red-gate.com
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
VP, Software Development
Organizational Intelligence, Inc.