SQL Data Compare 7 - Comparison Key missing

bluebird84

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

Brian Donahue

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.

bluebird84

Brian Donahue wrote:

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

Brian Donahue

Sorry, just realized this is v7 and I am using v10. Is it possible for you to upgrade?

bluebird84

Brian Donahue wrote:

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

AlexMBanks

bluebird84 said:

Brian Donahue wrote:

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.