Field Order

mzcopeamzcopea Posts: 31
edited October 1, 2005 11:28AM in SQL Compare Previous Versions
We are using Sqlcompare command-line version. It does not appear to modify the order of the fields within a table when the order is changed - is that the normal behavior?

Or is there an option that will bring over field order changes?

Comments

  • Hi

    If you look in the options there is one that will force the column order.

    This will drop columns and recreate them in the correct order so they match. The data is kept by using temporary names for the old tables and moving the data.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • that's great, we'll give that a try.

    one more thing - can we use sqlcompare.exe command-line to just apply a script without doing a compare first?

    we'd like to create one script each week and use that same script for multiple environments. sometimes we tweak the script before we apply it. in order to do that, we need to be able to run sqlcompare.exe only to apply our final tweaked script.

    thanks.
  • Hi

    Unfortunately you can not use SQL compare command line to run a script. You will have to use Query Analyser or an OSQL prompt.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • Is this normal behavior:

    I did a sqlcompare between Snapshot1 and DB2 with only /exclude: user and /exclude:role and no other options. I applied that script to my DB2 successfully.

    I then did a sqlcompare between Snapshot1 and DB2 and chose /include: table and added the /options forcecolumnorder and applied that script to DB2 successfully.

    I then did a third script between Snapshot1 and DB2 which did a sqlcompare with /exclude: user and /exclude:role and I added the /forcecolumnorder option. I expected the snapshot and DB2 to be identical, but instead I got a huge script of differences and I get errors when I apply it due to some bad stored procedures which we need to fix.

    BUT - If the forcecolumnorder option only forces table column order to be correct, why does sqlcompare detect additional differences in the third script? And I don't understand why the stored procedure problems weren't detected in the first script?

    Thanks.
    Abbe
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Abbe,

    My guess is that the force column order option exposes problems in your stored procedure due to dependencies -- if the stored procedure depends on one of the columns of a table that needs to be rebuilt, then the stored procedure would need to be dropped first and later recreated.

    With force column order off, this procedure is probably being left alone because there is no reason to drop or alter it, so the procedure problem goes unnoticed.
This discussion has been closed.