Tables with compound keys may not be mapped automatically

Category: Troubleshooting & error messages
Date: 19 Nov 2008
Product: SQL Data Compare
When SQL Data Compare is used to compare the data in two identical tables with a compound primary key, the tables will not be mapped together automatically if the order that the columns of the compound key was originally created in is different.

For example, the following tables would need to be mapped together automatically because of the primary key column order. This is a known issue, but it cannot be avoided for technical reasons and therefore cannot be fixed. The method for working around the issue is to recreate the second table with a schema identical to the first one.

USE DB1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable](
[Column1] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Column2] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Column3] [varchar](7600) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Column1] ASC,
[Column2] ASC
) ON [PRIMARY]
) ON [PRIMARY]
/ The script for the second table /
USE DB2
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable](
[Column1] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Column2] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Column3] [varchar](7600) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Column2] ASC,
[Column1] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
--And the one in Test:
USE [DB2]
GO
/**** Object: Table [dbo].[Reports_Publish] Script Date:
03/28/2008 08:15:12 ****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
DROP TABLE [dbo].[Reports_Publish]
CREATE TABLE [dbo].[Reports_Publish](
[SourceTableName] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PublishTableName] [varchar](200) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ColumnList] [varchar](7600) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Reports_Publish] PRIMARY KEY CLUSTERED
(
[SourceTableName] ASC,
[PublishTableName] ASC

) ON [PRIMARY]
) ON [PRIMARY]

GO