Field Order

mzcopea

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?

Daniel Handley

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

mzcopea

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.

Daniel Handley

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

mzcopea

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 Donahue

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.