Dependencies on User Defined Table Types
debry
Posts: 4 New member
in SQL Compare
User Defined Table Types cannot be altered directly. First, all the dependencies on that type need to be dropped, then the table type is altered, then the dependencies are re-created. It's an annoying limitation of Microsoft SQL.
The way to make this work well in SQL Compare is to check the box that says "Deploy all dependencies". This works in most cases, but we occasionally see objects in the list that are not true dependencies. Specifically, we are seeing tables in the dependency list when only User Defined Table Types are selected for deployment. This causes problems with our deployments.
It seems that only procedures or functions should be dependencies of User Defined Table Types. Is this accurate? Why would tables show up in the list? Thanks.
The way to make this work well in SQL Compare is to check the box that says "Deploy all dependencies". This works in most cases, but we occasionally see objects in the list that are not true dependencies. Specifically, we are seeing tables in the dependency list when only User Defined Table Types are selected for deployment. This causes problems with our deployments.
It seems that only procedures or functions should be dependencies of User Defined Table Types. Is this accurate? Why would tables show up in the list? Thanks.
Tagged:
Answers
In theory they would only be included if they were a part of a dependency chain. Maybe the table use a function that uses the UDTT? So table needs to drop, so function can drop so UDTT can drop.
Whether that's true in this case I obviously can't say, but to investigate further we would likely need the source and target object creation scripts that are being deployed, as well as the SQL Server version and the SQL Compare version so we can try to reproduce the issue here.
Kind regards,
Alex
Have you visited our Help Center?
I'll see if I can re-create the scenario, but either way, one solution to this problem would be to allow users to select / deselect specific objects in the dependency list. That way it's not an "all or nothing" feature. Has this been proposed before?
The option to select the dependencies has be suggested on the SQL Compare Uservoice forum here: https://redgate.uservoice.com/forums/141379-sql-compare/suggestions/2923976-option-to-select-individual-dependencies-or-affec
Do let me know if you are able to share the DDl or a reproduction scenario and I will have a go using that - if needed I can reach out via a support ticket to get the files.
Have you visited our Help Center?