SQL Data Compare 7 - Comparison Key missing

bluebird84bluebird84 Posts: 10 New member
Hi,

I have the following table in 2 databases. When I try to setup a data compare between them, I'm not able to set the ID column as the Comparison Key. The ID column is not even in the list (See screenshot).

Can someone please advise on how I can resolve this issue, what am I missing? Other tables with Primary Keys compare fine.

thanks


Development Database
CREATE TABLE [dbo].[udPartyTypeDefaults](
	[ID] [smallint] IDENTITY(1,1) NOT NULL,
	[PartyType] [dbo].[uCodeLookup] NULL,
	[DefaultIPType] [dbo].[uCodeLookup] NULL,
	[DefaultAssocType] [dbo].[uCodeLookup] NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 CONSTRAINT [PK_udPartyTypeDefaults] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[udPartyTypeDefaults] ADD  CONSTRAINT [DF_udPartyTypeDefaults_rowguid]  DEFAULT (newsequentialid()) FOR [rowguid]
GO

Test Database
CREATE TABLE [dbo].[udPartyTypeDefaults](
	[ID] [smallint] IDENTITY(1,1) NOT NULL,
	[PartyType] [dbo].[uCodeLookup] NULL,
	[DefaultIPType] [dbo].[uCodeLookup] NULL,
	[DefaultAssocType] [dbo].[uCodeLookup] NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 CONSTRAINT [PK_udPartyTypeDefaults] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[udPartyTypeDefaults] ADD  CONSTRAINT [DF_udPartyTypeDefaults_rowguid]  DEFAULT (newsequentialid()) FOR [rowguid]
GO

jcAUYc5.png

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    In order to select the ID column as the comparison key, you must first select "custom", then ID appears in the list of candidates. You are probably better off with the default, though, since the index is already built on the ID column and indexes are going to have better performance.

    Hope this helps.
  • bluebird84bluebird84 Posts: 10 New member
    In order to select the ID column as the comparison key, you must first select "custom", then ID appears in the list of candidates. You are probably better off with the default, though, since the index is already built on the ID column and indexes are going to have better performance.

    Hope this helps.

    Thanks for your reply. I've tried that and even after clicking Custom the ID column does not show up

    n0ltxdB.png
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Sorry, just realized this is v7 and I am using v10. Is it possible for you to upgrade?
  • bluebird84bluebird84 Posts: 10 New member
    Sorry, just realized this is v7 and I am using v10. Is it possible for you to upgrade?

    I downloaded the trial version of v10 and I have exactly the same issue as in V7 when using the same project file. I then created a new project and didn't have the issue. I compared the Options and the Include Identity Column option wasn't checked. Once that was checked in v7 and v10 it mapped correctly.

    thanks
  • AlexMBanksAlexMBanks Posts: 15 Bronze 2
    Sorry, just realized this is v7 and I am using v10. Is it possible for you to upgrade?

    I downloaded the trial version of v10 and I have exactly the same issue as in V7 when using the same project file. I then created a new project and didn't have the issue. I compared the Options and the Include Identity Column option wasn't checked. Once that was checked in v7 and v10 it mapped correctly.

    thanks
    Thanks @bluebird84, this was driving me crazy. I was dreading having to manually go through and set several hundred comparison keys - your suggestion to use the 'Include Identity Column' option fixed the issue for me.

    What I still don't understand is why it was treating my two databases differently in the first place - they were literally deployed from the same source.
Sign In or Register to comment.