Problem importing sync data

coderanger · Post by **coderanger** » Mon 27 Sep 2010 07:32

I compared my two databases and created a SQL file, then tried to import this sql into the target database and receive an error.

It is trying to perform an UPDATE statement and updating the unique identity field which you cannot do in SQL Server; and thus fails.

Code: Select all

UPDATE dbo.PersonAlias SET nID = 85681, dtCreated = '2010-09-23 12:11:55.710' WHERE lPersonID = 2557 AND strNameAlias = N'Jamie Cromwell'

nID is a unique identity field as shown below:

Code: Select all

CREATE TABLE [dbo].[PersonAlias](
	[lPersonID] [int] NOT NULL,
	[strNameAlias] [varchar](50) NOT NULL,
	[nID] [int] IDENTITY(1,1) NOT NULL,
	[bIsAKA] [bit] NOT NULL,
	[dtCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_PersonAlias] PRIMARY KEY CLUSTERED 
(
	[lPersonID] ASC,
	[strNameAlias] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

.jp · Post by **.jp** » Mon 27 Sep 2010 10:00

You can turn the Ignore Identity Column on for comparison. In such case, all the columns, having the Identity property will be excluded from comparison and synchronization. Or you can turn the Reseed Identity Columns on in the Synchronization wizard.
Thanks.

coderanger · Post by **coderanger** » Mon 27 Sep 2010 10:24

No good as it excludes every table from comparison then. Instead of ignoring identity on comparison (which seems wrong as that is its primary key field), it should just not be setting it on an UPDATE statement which is illegal and impossible in SQL Server

.jp · Post by **.jp** » Mon 27 Sep 2010 13:04

Unfortunately, dbForge DC for SQL Server doesn't have an information about what to do with identity columns for different users in different situations. That's why we offer the following options:

1. On the Options page of the comparison wizard you may select the Ignore Identity Column check box. It excludes all the identity columns in all tables from comparison and synchronization.

2. On the Mapping page of the comparison wizard you may exclude any fields from any tables from comparison. They will not be synchronized during synchronization.

3. In the comparison document you may exclude any table from synchronization. You may also exclude any records from synchronization or exclude commands by type, for example, execute only insert commands.

4. On the Options page of the synchronization wizard you can select the Reseed Identity Columns check box. It allows to execute insert/update/delete operations with identity columns correctly.

If you need to exclude a column from update operations only, you need to generate synchronization script and make changes to it manually.

Alternatively you can perform comparison and synchronization in two stages: at first, create the script containing only insert and delete operations, then exclude the column on the Mapping page and create the script, containing only update operations.

coderanger · Post by **coderanger** » Mon 27 Sep 2010 13:26

Thanks, I will try the remapping option to map the identity fields instead of the unique clustered key and see if that helps.

coderanger · Post by **coderanger** » Tue 28 Sep 2010 09:19

Yup that did the trick, thanks a lot

.jp · Post by **.jp** » Tue 28 Sep 2010 12:12

coderanger wrote:Yup that did the trick, thanks a lot

Kindly let us know if you have any other issues or questions regarding the product, we'll like to help.

coderanger · Post by **coderanger** » Tue 28 Sep 2010 12:18

No problem now thanks.

But I was trying to run the command line version through my automated build tool (http://www.coderanger.com/products/buildit) but it wouldnt work.

After a few hours investigation I found that it was because your tool does not appear to use STD Handles for output so I rebuilt my tool to not require STD Handles (with Win32 CreateProcess) and it seems to work fine now.

So all is great, thanks and I will post again any issues or suggestions I may have in future.