PK IDENTITY column data with FK dependencies will be lost???

npinchot

So I finally got around to trying your software out, and so far I'm not impressed.

Severity: Medium
Object: WorkOrders
Title: The IDENTITY column on table [dbo].[WorkOrders] has changed. The table must be rebuilt. The data in the table apart from dropped columns will be preserved.

---

Severity: Medium
Object: WorkOrders
Title: Column [recordId] on table [dbo].[WorkOrders] could not be matched to a column in the source table. The data in this column will be lost.

Old

CREATE TABLE [dbo].[WorkOrders]
(
[WorkOrderId] [bigint] NOT NULL IDENTITY(1, 1),
[CreateDate] [smalldatetime] NOT NULL CONSTRAINT [DF_WorkOrders_CreateDate] DEFAULT (getdate()),
[ModifyDate] [smalldatetime] NULL,
[BuildingId] [int] NULL,
[TechnicianId] [bigint] NULL,
[StatusId] [bigint] NULL,
[Approved] [bit] NOT NULL CONSTRAINT [DF_WorkOrders_Approved] DEFAULT ((0)),
[TypeId] [int] NOT NULL CONSTRAINT [DF_WorkOrders_TypeId] DEFAULT ((1)),
[Active] [bit] NOT NULL CONSTRAINT [DF_WorkOrders_Active] DEFAULT ('TRUE'),
[CreatedByUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ModifiedByUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContactName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PhoneNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RoomNumber] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Situation] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

New

CREATE TABLE [dbo].[WorkOrders]
(
[recordId] [bigint] NOT NULL IDENTITY(1, 1),
[createDate] [smalldatetime] NOT NULL CONSTRAINT [DF_terminations_createDate] DEFAULT (getdate()),
[modifyDate] [smalldatetime] NULL,
[buildingId] [int] NULL,
[technicianId] [bigint] NULL,
[statusId] [bigint] NULL,
[approved] [bit] NOT NULL CONSTRAINT [DF_workOrders_approved] DEFAULT (0),
[typeId] [int] NOT NULL CONSTRAINT [DF_workOrders_typeId] DEFAULT (1),
[createdByUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[modifiedByUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[contactName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phoneNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[roomNumber] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[situation] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

The "recordId" field is renamed to the name "WorkOrderId" in the current veresion of the database. Why can this not match it up? The recordId column is the Primary Key of the table and type BigInt. The WorkOrderId column is the Primary Key of the table and type BigInt. I don't understand why this can't be matched up - or at least the data saved? I need this data saved because their are child tables with Foreign Key dependencies and if the recordId column gets dropped that will obviously kill those Foreign Keys and the link between the data in those child tables. I could get past the fact that it doesn't match up if it would give me an option to tell it what column to match to - but it doesn't.

What am I supposed to do here to make this go smoothly. Did I miss an option or setting somewhere?

David Atkinson

Hi,
The problem here is that SQL Compare is seeing two completely different columns. It cannot assume or second-guess that these should be considered the same. Until we've implemented a column-name mapping feature (it's already on our list of enhancement requests), the only thing I can suggest is that you first rename the column on the source database and repeat the comparison. SQL Refactor (http://www.red-gate.com/products/SQL_Refactor/index.htm) has a Smart Rename feature that does just that.

If you have any alternative suggestions of how SQL Compare could or should be behaving, I'd be very interested to hear them.

Kind regards,

David Atkinson
Product Manager
Red Gate Software

npinchot

I think it will be great when you implement that column-name mapping feature.

So I went ahead and renamed the columns so that they all match across both databases. I refreshed the comparison and everything looked like it was going to go smooth. The only warning I got was that 1 table needed to be rebuilt but nothing about dropping columns. I attempted to complete the synchronization and got this error:

[544] Cannot insert explicit value for identity column in table 'tmp_rg_xx_WorkOrders' when IDENTITY_INSERT is set to OFF.

At this point I'm just ready to give up on this.

Michelle T

I'm having difficulty reproducing the error that you report - I've tried synchronizing the tables you've provided the script for, and synchronizing them after changing the column names (which doesn't even require a table rebuild), and synchronizing them with the identity changed slightly to force a rebuild, and all of these have worked fine in version 7.1.

If you could send me (michelle.taylor@red-gate.com) a snapshot of the databases that you're having difficulty synchronizing, along with the exact version number (from the About box) and the project options that you're using, I'll have a better chance of reproducing the problem (which we'd very much like to fix if we can find it).