Table rebuild to add column scripted after sproc update to use the new column

GregE

Using SQL Compare 14.0.7.13517 (latest) to compare SQL Compare snapshots and SQL Server 2012 databases. I'm experiencing a problem where a column is added to an existing table, and stored procedures are updated to use the new column. The script SQL Compare generates is rebuilding the table using a temporary RG_Recovery table, which is nice, but this step is inserted after the sproc update in the script. This makes the overall script fail because the column doesn't exist when the sprocs try to use it.

SQL Compare normally handles this kind of dependency just fine when it uses ALTER TABLE (there's an example of it working that way in the exact same comparison I found this problem in). Is this just a hole in logic that needs to be fixed to calculate the same depedency order when rebuilding a table for a new column? Is there an option I can change on my side to prevent this? I tried 'add object existence checks' which changed the error by not updating the sprocs for a column that doesn't yet exist, which isn't a full solution and of course made the script much harder to read.

Below is an example of the generated script - taAuListStream.Enabled is added and then spC_Ext_UpdateAuListStream is altered afterwards, but taPlaylistEvent is rebuilt to add the MediaType column only after spM_Ext_GetCachedPlaylistMediaAvailability has been altered to include it.

And here are my options:

(SQL script moved to support ticket)

Tianjiao_Li

Thanks for reporting this issue.

We'd need a replication to be able to diagnose this further. I'll reach out by email.

Thanks.