Options
SQL Compare: Ordering of columns
Brian Donahue
Posts: 6,590 Bronze 1
Hi Peter,
Thanks for writing. Preserving the column order is important in some
circumstances where your SQL Queries are inserting or updating data in a way
that depends on the columns being in a certian order.
That's why we have a 'Force column order to be identical' option in the
software. If you use this, SQL Compare will rebuild the table if needed to
preserve the order of the columns so they're in the same order as the source
database.
Regards,
Brian Donahue
Red Gate Technical Support
"Peter Mounce" <pete@x-rm.com> wrote in message
news:4qI7SrcXEHA.2468@server53...
> Hi
>
> I use SQL Compare 3.17, Data Compare 3.37.
>
> Problem a)
> If I insert a column into a table (in the middle of the existing columns)
in
> database A, and then sync the change to database B, and then design that
> database B's table using Enterprise Manager, I find that the column has
been
> added to the end of the table, not at the position where it was inserted.
> If the table is then edited in database A (ANY edit), the column shows up
as
> being different (correctly, because it's in a different position).
However,
> if no edit is performed, the tables are do not show up as different, *but
> they should*. I contend that table column ordering should be a factored
> into the compare algorithm at a higher level than it currently is. Also
> that it should be maintained in the first place
>
> Problem b)
> In the above case, say the column being inserted in the first database
gets
> some data put into it, and the change is synced using Sql Compare to the
> second database. I now want to sync the data in the database, so I run
Sql
> Data Compare on the databases. However, it appears that the table now has
> two different columns called the same thing; it's not possible to transfer
> the data in this way, because DC (correctly) thinks the columns are
> different.
>
> I suspect that problem b) will be solved for free if problem a) is
solved -
> ie, column ordering is preserved during synchronisation.
>
> Regards
> Peter Mounce
>
>
Thanks for writing. Preserving the column order is important in some
circumstances where your SQL Queries are inserting or updating data in a way
that depends on the columns being in a certian order.
That's why we have a 'Force column order to be identical' option in the
software. If you use this, SQL Compare will rebuild the table if needed to
preserve the order of the columns so they're in the same order as the source
database.
Regards,
Brian Donahue
Red Gate Technical Support
"Peter Mounce" <pete@x-rm.com> wrote in message
news:4qI7SrcXEHA.2468@server53...
> Hi
>
> I use SQL Compare 3.17, Data Compare 3.37.
>
> Problem a)
> If I insert a column into a table (in the middle of the existing columns)
in
> database A, and then sync the change to database B, and then design that
> database B's table using Enterprise Manager, I find that the column has
been
> added to the end of the table, not at the position where it was inserted.
> If the table is then edited in database A (ANY edit), the column shows up
as
> being different (correctly, because it's in a different position).
However,
> if no edit is performed, the tables are do not show up as different, *but
> they should*. I contend that table column ordering should be a factored
> into the compare algorithm at a higher level than it currently is. Also
> that it should be maintained in the first place
>
> Problem b)
> In the above case, say the column being inserted in the first database
gets
> some data put into it, and the change is synced using Sql Compare to the
> second database. I now want to sync the data in the database, so I run
Sql
> Data Compare on the databases. However, it appears that the table now has
> two different columns called the same thing; it's not possible to transfer
> the data in this way, because DC (correctly) thinks the columns are
> different.
>
> I suspect that problem b) will be solved for free if problem a) is
solved -
> ie, column ordering is preserved during synchronisation.
>
> Regards
> Peter Mounce
>
>
This discussion has been closed.
Comments
I'm also having trouble with this. I'm new so there is probably something I'm missing. As you can see from my example below I think I setting the "Force Column Order' option.
What am I missing?
BunchOfWork.BuildFromDifferences(differences, Options.ForceColumnOrder, false);
Thanks
AJ
That certainly looks like the correct way to enforce the column order in SQL Toolkit, but you should also use the Options.ForceColumnOrder in the Database.CompareWith method that you ran earlier in the code. This way the comparison is done properly as well as the scripts being built properly.