How can we help you today? How can we help you today?

SQL Data Compare 7 - Comparison Key missing

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
bluebird84
0

Comments

5 comments

Sort by
  • 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.
    Brian Donahue
    0
  • bluebird84
    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
    bluebird84
    0
  • Brian Donahue
    Sorry, just realized this is v7 and I am using v10. Is it possible for you to upgrade?
    Brian Donahue
    0
  • bluebird84
    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
    bluebird84
    0
  • AlexMBanks
    bluebird84 said:
    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.
    AlexMBanks
    0

Add comment

Please sign in to leave a comment.