Red Gate forums :: View topic - Order of columns in mappings doesn't match source table/view
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

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

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
Brian Snider



Joined: 22 Apr 2009
Posts: 4

PostPosted: Wed Apr 22, 2009 1:10 am    Post subject: Order of columns in mappings doesn't match source table/view Reply with quote

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:
Code:

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.:

Code:

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Mon May 04, 2009 1:10 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Snider



Joined: 22 Apr 2009
Posts: 4

PostPosted: Mon May 04, 2009 5:36 pm    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Tue May 05, 2009 10:34 am    Post subject: Reply with quote

The SQL Query that Data Compare uses to retrieve columns for all views uses this order clause:
Quote:
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;
Back to top
View user's profile Send private message
Brian Snider



Joined: 22 Apr 2009
Posts: 4

PostPosted: Tue May 05, 2009 5:14 pm    Post subject: Re: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6645

PostPosted: Tue May 05, 2009 6:40 pm    Post subject: Reply with quote

Hi Brian

I'm glad that you've found the Sort method. I'll remember that one for the future.
Back to top
View user's profile Send private message
Brian Snider



Joined: 22 Apr 2009
Posts: 4

PostPosted: Fri Oct 09, 2009 8:01 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group