Drop assembly-dependent objects prior to dropping assembly

shadowbob

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!

Eddie D

Thank you for your post into the forum and sorry for the delay in reply to you.

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

shadowbob

The option you mentioned is indeed turned off. Would turning it on solve my problem? I'm curious why you would ever be able to drop an assembly without first dropping it's dependent objects. Thanks.

Eddie D

Thank you for your reply.

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

shadowbob

Since updating an assembly is rare, I haven't had the opportunity to try this out yet. When I do, I'll let you know if the option solved the problem. If it does, I'm confused why the default behavior is the way it is. I don't see how the default behavior would ever work. I must be missing something. Thanks!