I am very frustrated trying to compare data here. I am trying to compare data from an SQL server table to a flat file. Since not one table contains the data that this flat file contains I did the following.
Created an import template that pulls in an SQL query matching the flat file format, and imports this into an Access Database.
Created another import template that pulls in the flat file and imports this into the same access Database.
Keep in mind both of these tables are identical in structure. Same column names, same number of columns, same primary key and foreign key.
However, when I try to do the compare, after I select the originating table and target object, the select columns dialog that comes up is completely blank. No columns are displayed. If you cancel out of this dialog, there will be a red x showing on this table I am comparing saying No keys to compare.
What is going on? Why can I not compare these two ‘identical schema’ tables???
BTW, as a side note, if I import these as excel files, it seems to work. However, only if both of the compares are from Excel. If I try Access to Excel (or vice versa) compares, again, I get ‘no keys to compare’ message and am unable to continue with the compare wizard.
I can see why you are frustrated, Data Compare is broken when using Access and SQL Server. I entered CR91671 to fix. Thanks for bringing this to our attention. We will fix for TDA 3.1.
Debbie
I see. That would make sense why it doesn’t work.
So I am able to get it to work, as I said, through excel to excel comparison. However, I seem to have TERRIBLE performance issue doing this. Even with just 1500 rows, it will take 10 min in the wizard to load the ‘preview’ of the spreadsheet. Is there something I am doing wrong that the spreadsheet will kill Toad’s performance this much???
We use a thrid party component to open and read those files. Performance is affected by numbers of rows, columns and worksheets as well as objects in the file. 1500 rows should not be an issue. Are there a lot of columns, other worksheets or objects like graphs or pivot tables in the file?
Debbie
I wouldn’t say a lot of columns compared to what Excel can handle, but there are around 30 columns. No other worksheets or objects/pivot tables or anything however. They are basically a flat file that was just imported and converted to Excel.
It’s literally unusable comparing Excel because between each step of loading the data, Toad will freeze up for about ten minutes. So in total, it takes over 20 min just to run a compare!
Gee, compare excel to excel is not workable at all. I had no idea. Seems embarassinly slow. I entered CR91762 for this but that will not help you in the short term.
Do you happen to be using TDA 3.0 Pro? If so I would save content to Local Storage. This works at the expected speed.
Debbie
I checked the release notes for 3.0.1 and 3.1 and not seeing these two defects (CR91762, CR91671) in there. Any idea what release these are scheduled to be fixed in?
Thanks!
Let me check with the Data Compare team and find out. They are scheduled for the TDA 3.1 release but I don’t know how soon they will be in the Beta. Do you use Beta? Or do you have to wait for GA release?
Debbie
Thank you. I have no problem using a Beta if it fixes those two issues. 
