What are the challenges you face when working across database platforms? Take the survey
Options

Bug with /include:StaticData when constraints change

sab39sab39 Posts: 10
edited October 23, 2014 12:08PM in SQL Compare 11
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.

Comments

  • Options
    There's no getting away from needing a lot of space in this case. The best I can offer is that you change the system environment variable RGTEMP to point to any directory (even on another drive) where you can be confident of having lots and lots of space.
  • Options
    Is that reply intended to another thread? This bug isn't about needing space, at all. It's about the wrong constraints getting operated on in this scenario - the constraints get dropped/added from how the database USED to be before the schema changes, but those constraints either don't exist any more or are different. So the resulting database ends up wrong.

    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.
  • Options
    Yes! Sorry about that- I must be going cross-eyed.

    I'd like try to reproduce the issue here- are you able to share a schema + a change that replicates the problem?
Sign In or Register to comment.