Order of columns in mappings doesn't match source table/view

Brian Snider

I'm comparing a view in two different databases which contains six columns. The view specifies the columns in a certain order which, queried using a SELECT * statement, returns Field1, Field2, Field3, Field4, Field5, Field6 in the expected order.

The view is defined similarly to the following:

SELECT TOP 100 PERCENT
Field1, Field2, Field3 Field4, Field5, Field6
FROM SomeTable

Since the PKs don't always match between databases, I'm comparing using all available fields as a pseudo-composite key. To add all the fields to the MatchingMappings object, I simply iterate through each FieldMapping in my TableMapping object before joining, e.g.:

foreach(FieldMapping m in tableMapping.FieldMappings) {
    System.Console.WriteLine("adding mapping on {0}", m.Obj1.Name);
}

I noticed that the order of the fields is seemingly random, i.e., it doesn't match the expected order. The above foreach iteration displays, e.g., Field4, Field1, Field6, Field3, Field2, Field5. How can I add the fields in the same order specified in the view definition without hard-coding the field names? The comparison is based on a user-selected set of views which are read in at application startup from an XML document, each with different field names, so I want this to be as dynamic as possible.

This is an issue because the resulting differences are returned in the same field order as the matching mappings, which doesn't make any sense when displayed as the order of the columns is not what my users are used to seeing in our main program interface.

Thanks,
Brian

Brian Donahue

Hi Brian,

I'm sorry that you haven't had an answer in so long -- the new topic notifications to the moderators for this forum were not set up.

I'll have to pull out the source code for Data Compare to say for sure, but I would assume that SQL Data Compare Engine gets the list of columns for a view from the sys.syscolumns information schema view, and they must be ordered somehow: either as they are ordered in syscolumns or alphabetically.

Are the fields of your views returned in alphabetical order? If so, that may be the answer. I'll try to clarify this tomorrow.

Brian Snider

Brian Donahue wrote:

Are the fields of your views returned in alphabetical order? If so, that may be the answer. I'll try to clarify this tomorrow.

Hi Brian,

Thanks for the reply. I ran a few queries and found the following:

* The fields are not returned in alphabetical order * The fields are not returned in sys.syscolumns.colid or sys.syscolumns.colorder order * The fields are not returned in the same order as they appear in sys.syscolumns (which is ordered by colid in this case) * The fields

are returned in the same (incorrect) order on every run, so they appear to be ordered by something

Any assistance you can provide would be greatly appreciated!

Thanks,
Brian

Brian Donahue

The SQL Query that Data Compare uses to retrieve columns for all views uses this order clause:

ORDER BY sysusers.name, sysobjects.name, syscolumns.colid

I would assume that this means you would get the columns in syscolumns.colid order, but you're saying this isn't the case, so I don't know what to tell you.

Maybe it would just be a good idea to try and work around the issue by getting the column ordinals in the result store rather than relying on the order they appear in mappings?

(TableDifferences)diffs["[dbo].[objectname]"].ResultsStore.Fields["ColumnName"].OrdinalInResults1;

Brian Snider

Brian Donahue wrote:

Maybe it would just be a good idea to try and work around the issue by getting the column ordinals in the result store rather than relying on the order they appear in mappings?

Thanks for the info. I just tried your suggestion of using the ordinal in the result store, but it yields the same results, i.e.:

Field4, ordinal 0
Field1, ordinal 1
Field6, ordinal 2
Field3, ordinal 3
Field2, ordinal 4
Field5, ordinal 5

I tried using both the OrdinalInResults1 and OrdinalInResults2 properties, both with the same result. Interestingly, I found out that the order of the fields in the result set is identical to the order the fields were added to the matching mappings. On a whim, I tried calling the FieldMappings.Sort() method before adding each field mapping to the matching mappings, and it sorted them by column id, as expected!

It appears that simply calling FieldMappings.Sort() before iterating over the available field mappings solves my issue. Thanks Brian for the suggestions and the confirmation on how the items are ordered internally - you got me pointed in the right direction.

Thanks,
Brian

Brian Donahue

Hi Brian

I'm glad that you've found the Sort method. I'll remember that one for the future.

Brian Snider

Bumping this thread as I've run into a related issue.

Calling FieldMappings.Sort() appears to "sort" the columns so that they match the original order in the view's select list (intended and expected)...

but...calling FieldMappings.Sort() also appears to sort the returned row data as well in the corresponding results by the first column (not intended and unexpected), which effectively jumbles the expected order of the rows in my case.

Brian (or anyone else at Red Gate) - can you provide any insight in this area? Is there any way to get the column order to match the underlying view being compared, without modifying the order of the resulting rows?