Different Tables are Identical?

Beechams

Hi,

I am experiencing some strange behaviour (from SQLCompare or me!). After comparing two SQL 2000 databases. I select a table that is showing as different and see in the comparison windows that they are identical. All the columns show an equals sign as does the PK.

The script that is generated only appears to drop and recreate an identical clustered primary key.

Sorry I can't post a screenshot.

Cheers
Beechams

Beechams

Just done some more checking in SQL and have discovered that the clustered PK in one of tables had been created the STATISTICS_NORECOMPUTE option.

I am assuming this is causing the two tables to appear as different, but is there an option to show this in the comparison window?

Cheers
Beechams

Daniel Handley

Hi Beechams

The engine that shows the differences in the lower text window is not as powerful as the one that creates the scripts. This is probably why this option is missing from the text window.

Regards
Dan

storsell

Hi, Everyone --

I've encountered this issue using the latest software because the primary key does not seem to adhere to the "ignore constraint and index names" option (or IgnoreConstraintNames when using the toolkit). I do not specify the optional constraint name for a primary key in a table, so it is different in each database.

All other constraint naming differences are ignored, and even this difference is correctly ignored in the "Creation SQL" script view. When a script is generated, however, the primary key is still dropped and re-added with the name from the source database (in SQL Compare 3.2.1.14 or even when using the IgnoreConstraintNames option in the BuildFromDifferences method of the Toolkit).

Hopefully I will be able to work around the issue in the toolkit by manually turning off this particular "difference" among my dbs until the issue is fixed.

Thanks,
Shane

storsell

Just a follow-up note:

I only experience this issue when other differences exist between two database tables besides different primary key names. Tables that are identical besides the primary key name result in "do nothing" scripts when I use the "ignore constraint and index name" option.

My primary key is unnecessarily dropped and re added when:
1. the pk names differ AND
2. other differences exist between the two tables (e.g., column widths) AND
3. index/constraint names are to be ignored

I'm sure the same thing happens when #3 is not selected, but in that scenario the behavior would be correct

It looks like I won't be able to disable this Difference in the Toolkit, as I want the other table adjustments to be included in the generated script.

Thanks again,
Shane

storsell

Is anyone going to take a look at this, or do I need to report this bug through other means?

Example 1 - different primary key constraint name, plus other differences

db1.SomeTable
SomeTableID int identity primary key (constraint name PK_1)
OtherField varchar(10) unique (constraint name UQ_1)

db2.Sometable
SomeTableID int identity primary key (constraint name PK_2)
OtherField varchar(20) unique (constraint name UQ_2)

SQLCompare with "Ignore constraint and index names" option generates a script with two tasks:
1. drop/re-add the primary key constraint (incorrect)
2. resize OtherField (correct)


Example 2 - only difference is primary key constraint name

db1.SomeTable
SomeTableID int identity primary key (constraint name PK_1)
OtherField varchar(20) unique (constraint name UQ_1)

db2.Sometable
SomeTableID int identity primary key (constraint name PK_2)
OtherField varchar(20) unique (constraint name UQ_2)

SQLCompare with "Ignore constraint and index names" option generates a script with NO tasks (correct)