Options
Bug with /include:StaticData when constraints change
sab39
Posts: 10
I'm using the SQL Automation Pack which appears to include the SQL Compare command line tool 11.0.0.184.
My command line is doing a comparison between an SQL source control repository and a database to generate the scripts to bring the database up to date. It looks roughly like:
sqlcompare /sourcecontrol1 /sfx:sfx.xml /mfx:mfx.xml /revision1:head /vu1:(user) /vp1:(password) /server2:(server) /database2:(database) /u2:(dbuser) /p2:(dbpw) /scriptfile:script.sql /force /include:StaticData
The problem occurs when one of the static data tables has changed its constraints between the source control version and the database version. For example, a foreign key has changed its name or had ON DELETE CASCADE added or removed or something.
The resulting SQL script appears to do the following sequence of steps:
- Update the schema - which includes dropping the old constraints and add the new constraints
- Try to drop the OLD constraints
- Insert the static data
- Try to re-add the OLD constraints
This can fail in two different ways - if the name of the constraint has changed then the DROP statement fails because it no longer exists (the schema update dropped it already). If the name hasn't changed, the failure is more subtle - the constraint gets added and the script succeeds, but it gets added with its old characteristics, not the new ones.
My command line is doing a comparison between an SQL source control repository and a database to generate the scripts to bring the database up to date. It looks roughly like:
sqlcompare /sourcecontrol1 /sfx:sfx.xml /mfx:mfx.xml /revision1:head /vu1:(user) /vp1:(password) /server2:(server) /database2:(database) /u2:(dbuser) /p2:(dbpw) /scriptfile:script.sql /force /include:StaticData
The problem occurs when one of the static data tables has changed its constraints between the source control version and the database version. For example, a foreign key has changed its name or had ON DELETE CASCADE added or removed or something.
The resulting SQL script appears to do the following sequence of steps:
- Update the schema - which includes dropping the old constraints and add the new constraints
- Try to drop the OLD constraints
- Insert the static data
- Try to re-add the OLD constraints
This can fail in two different ways - if the name of the constraint has changed then the DROP statement fails because it no longer exists (the schema update dropped it already). If the name hasn't changed, the failure is more subtle - the constraint gets added and the script succeeds, but it gets added with its old characteristics, not the new ones.
Comments
In the absence of any replies I worked around the problem by digging out the correct constraints in source control and replacing the relevant sections within the generated script by hand to drop/add the correct constraints, but it'd be nice to know that a future version of sqlcompare would render that unnecessary.
I'd like try to reproduce the issue here- are you able to share a schema + a change that replicates the problem?