Troubles With Data Compare
TKH
Posts: 4
I sorted through the forums for a problem like mine, but didn't find one, so my apologies if this has already been adressed somewhere and I missed it.
I have two databases that should basically be identical. Both are on SQL Server, and have been imported from the exact same Access DB.
The first, DB_Original, is a straight import from Access, allowing SQL Server DTS to create the tables and their columns based on what's in the Access DB.
The second, DB_Modified, started off in the same fashion, but was then modified in a few ways. Foreign key relationships were added, a few more columns here and there, etc. Nothing much has been done to already existing columns or data.
I'm concerned about one particular table, Image. As a note, importing Image directly to SQL doesn't work; one of the columns is a smalldatetime in Access, and needs to be changed to a regular datetime during the import process, otherwise it won't fly.
None of the that should actually matter at all, but I put it out there just in case.
Anyway, on to the problem itself: when I compare the two DBs, which should be pretty much identical, SQL Data Compare tells me that I've got 198 rows that are the same, and the other ~16,000 are different. The 198 that are the same are the first 198, and everything after that is different. I pulled up a SELECT * FROM IMAGE in SQL Query Analyzer, and scrolled down to entry 198. Everything after it looks perfectly fine; they match. I dumped the results from both DBs into a text file and ran Beyond Compare on them. Everything looks fine there, too.
Which leaves me somewhat baffled as to why SQL Data Compare doesn't think they're the same. Here's some of the data in question.
From Query Analyzer, for the DB_Original, results 197-202:
From Query Analyzer, for the DB_Modified, results 197-202:
Now, the first two of the six I posted above check out fine in SQL Data Compare. However, after that, it tells me:
SQL Data Compare does not think that either table has records the other doesn't; they both have the same number.
Is there a way to have the Primary keys show in the comparison results?
Am I missing something?
Any help is appreciated,
Thanks,
Tyler
I have two databases that should basically be identical. Both are on SQL Server, and have been imported from the exact same Access DB.
The first, DB_Original, is a straight import from Access, allowing SQL Server DTS to create the tables and their columns based on what's in the Access DB.
The second, DB_Modified, started off in the same fashion, but was then modified in a few ways. Foreign key relationships were added, a few more columns here and there, etc. Nothing much has been done to already existing columns or data.
I'm concerned about one particular table, Image. As a note, importing Image directly to SQL doesn't work; one of the columns is a smalldatetime in Access, and needs to be changed to a regular datetime during the import process, otherwise it won't fly.
None of the that should actually matter at all, but I put it out there just in case.
Anyway, on to the problem itself: when I compare the two DBs, which should be pretty much identical, SQL Data Compare tells me that I've got 198 rows that are the same, and the other ~16,000 are different. The 198 that are the same are the first 198, and everything after that is different. I pulled up a SELECT * FROM IMAGE in SQL Query Analyzer, and scrolled down to entry 198. Everything after it looks perfectly fine; they match. I dumped the results from both DBs into a text file and ran Beyond Compare on them. Everything looks fine there, too.
Which leaves me somewhat baffled as to why SQL Data Compare doesn't think they're the same. Here's some of the data in question.
From Query Analyzer, for the DB_Original, results 197-202:
ID FKID SN CDate RID Data Q Scale _________________________________________________________ 225 17 0 1899-12-30 00:00:00.000 0 NULL 2 120 226 17 1 1899-12-30 00:00:00.000 0 NULL 4 240 227 17 2 1899-12-30 00:00:00.000 0 NULL 5 240 228 17 3 1899-12-30 00:00:00.000 0 NULL 4 240 229 17 4 1899-12-30 00:00:00.000 0 NULL 3 240 230 17 5 1899-12-30 00:00:00.000 0 NULL 3 240
From Query Analyzer, for the DB_Modified, results 197-202:
ID FKID SN CDate RID Data Q Scale _________________________________________________________ 225 17 0 1899-12-30 00:00:00.000 0 NULL 2 120 226 17 1 1899-12-30 00:00:00.000 0 NULL 4 240 227 17 2 1899-12-30 00:00:00.000 0 NULL 5 240 228 17 3 1899-12-30 00:00:00.000 0 NULL 4 240 229 17 4 1899-12-30 00:00:00.000 0 NULL 3 240 230 17 5 1899-12-30 00:00:00.000 0 NULL 3 240ID being the primary key, FKID being a foreign key. Everything's the same, yeah?
Now, the first two of the six I posted above check out fine in SQL Data Compare. However, after that, it tells me:
SN_Orig SN_Mod ____________________ 2 5 3 6 4 7 5 8That's all you really need to see to see the problem, but the other data is all off, too. It can't possibly be matching up the ID primary key entries properly, because I'm looking at them in query analyzer, and compared by ID, the results are exactly the same. It would seem like there's some kind of odd shift going on, or something.
SQL Data Compare does not think that either table has records the other doesn't; they both have the same number.
Is there a way to have the Primary keys show in the comparison results?
Am I missing something?
Any help is appreciated,
Thanks,
Tyler
Comments
What is the datatype of the primary key column? If it's nchar/nvarchar, you may want to try using the TRIM TRAILING SPACES option. If it's a FLOAT, that brings up some potential problems with precision and scale.
Both of the primary key columns are of type bigint.
One of my coworkers opened up SQL Server Enterprise Manager, went to the table in question, opened up the properties menu, went to the Indexes / Keys tab, and in the Primary Key index options, changed the order to Descending, rather than Ascending (as it was on by default) for both tables in both DBs.
Now everything works out okay, for whatever reason.
-Robert Sterbal