Cannot synchronise changes in User Defined Table Types when they are in use

jamieburns

So, in my SQL Server 2012 database, I've got a User Defined Table Type, which is used by a stored procedure. If I make a change to this type (e.g., change a column from varchar to nvarchar) and try to synchronise this change to another database, I get an error -

Cannot drop type '[type name]' because it is being referenced by object '[stored procedure]'. There may be other objects that reference this type.

The suggestion in the error is that the stored procedure should be dropped, but why isn't SQL Compare doing that for me?

I've been keeping up to date with the latest versions of SQL Compare (this is now on 12.4.12.5042 Professional), and I'm fairly sure that this wasn't the behaviour in previous versions.

Can someone please confirm if this is a change in behaviour, and if so, how to work with it? I'm pretty sure the solution isn't for me to manually drop that type so the change can go through.

Thanks.

J

jamieburns

Right, this was definitely me being daft.



Ticking 'Deploy all dependencies' fixes this issue.

sam.blackburn

To be honest we don't fully understand how people handle dependencies - we only ever expect Compare to do the right thing when that box is checked, and yet the majority of our customers tell us they leave it unchecked and select any dependencies manually. If you have any insights about how you use this feature, we'd love to hear them!

jamieburns

Hey Sam,

Now I come to think of it, I really can't think of why I don't have that checkbox checked. I vaguely remember a time when deploying dependencies caused a problem, but this is literally going back 5+ years ago. This was the first time when I actually needed to deploy a dependency in all that time.

99% of the time, I deploy all changes between two databases, and so the synchronisation just seems to work without having to include dependencies.

I guess I like to feel in control of what changes are going to be made. I use the first comparison page (where I can choose which items to include, and preview the changes) to make sure that these are indeed the changes I want to make. As soon as I click 'Deploy' I've already decided what I want to change, and nothing more. When I get given the option to include more changes (which is how dependencies feels), then I avoid it, because I don't want any unintentional changes - if I had wanted those changes, I would have picked them on the first comparison page. I think this feeling would be even stronger if I only choose to deploy a subset of changes - I wouldn't want something that I've excluded to be included again.

Now, if there had been an option to 'temporarily alter dependencies to allow the chosen changes to be deployed, but those dependencies will end up in their original state' (obviously needs a much better name than that!), then I would see that as a perfectly safe thing to leave checked, because I know only the changes I've selected will be deployed and even though other things will be affected during the deployment, I know the end result is exactly what I'm expecting.

Does that makes sense?

For now, though, I'll make a conscious decision to leave the 'dependencies' checkbox checked!