Force column order - Identity_Insert when no PK defined

Lxocram

When i put option Force Column Order on I frequently get errors like

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'tmp_rg_xx_sec_dossierbeheerder_departement' when IDENTITY_INSERT is set to OFF.

The problem occurs when the table has no PK but it does have an identity column(wich is in fact a PK column)

The synchronization script is missing two statements

PRINT N'Rebuilding [dbo].[sec_dossierbeheerder_departement]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_sec_dossierbeheerder_departement]
(
[ID_Link_Dossierbeheerder_Departement] [int] NOT NULL IDENTITY(1, 1),
[ID_departement] [tinyint] NULL,
[ID_Personeelslid_Dossierbeheerder] [int] NULL,
[geldig_van] [datetime] NOT NULL DEFAULT ('01/01/1900'),
[geldig_tot] [datetime] NOT NULL DEFAULT ('31/12/2999'),
[geldig] AS (case when [Geldig_Van]<dateadd(day,(0),datediff(day,(0),getdate())) AND [geldig_Tot]>=dateadd(day,(0),datediff(day,(0),getdate())) then (1) else (0) end),
[NTUsername] AS ([dbo].[get_Username_Personeelslid_COMPUTED_COLUMN]([ID_Personeelslid_Dossierbeheerder]))
)
GO
SET IDENTITY INSERT [dbo].[tmp_rg_xx_sec_dossierbeheerder_departement] ON
go
INSERT INTO [dbo].[tmp_rg_xx_sec_dossierbeheerder_departement]([ID_Link_Dossierbeheerder_Departement], [ID_departement], [ID_Personeelslid_Dossierbeheerder], [geldig_van], [geldig_tot]) SELECT [ID_Link_Dossierbeheerder_Departement], [ID_departement], [ID_Personeelslid_Dossierbeheerder], [geldig_van], [geldig_tot] FROM [dbo].[sec_dossierbeheerder_departement]
GO
SET IDENTITY INSERT [dbo].[tmp_rg_xx_sec_dossierbeheerder_departement] OFF
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[dbo].[sec_dossierbeheerder_departement]')
IF @idVal IS NOT NULL
DBCC CHECKIDENT(N'[dbo].[tmp_rg_xx_sec_dossierbeheerder_departement]', RESEED, @idVal)
GO
DROP TABLE [dbo].[sec_dossierbeheerder_departement]

Chris Auckland

Thanks for your post.

You're absolutely right about what it *should* be doing, but I'm not sure why it isn't in your case. I've tested it here with the same table structure, and it seems to add the identity insert statements correctly.

There must be something in the table that's causing the identity property to be missed.

Can you run the following query in SSMS and make sure that 128 is returned for status?

SELECT name, status FROM syscolumns WHERE name = 'ID_Link_Dossierbeheerder_Departement'

Can you let me know what is being changed when you sync sec_dossierbeheerder_departement?

Lxocram

Chris,
128 is returned

I've tried it again with an other table

creation script (SSMS)

CREATE TABLE [dbo].[kzlst_Opleiding_Voorzitter](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ID_Opleiding] [int] NOT NULL,
	[ID_Opleidingsvoorzitter] [int] NOT NULL,
	[Geldig_Van] [datetime] NOT NULL,
	[Geldig_Tot] [datetime] NOT NULL,
	[GELDIG]  AS (case when [Geldig_Van]<dateadd(day,(0),datediff(day,(0),getdate())) AND [geldig_Tot]>=dateadd(day,(0),datediff(day,(0),getdate())) then (1) else (0) end),
	[ID_Departement]  AS ([dbo].[getID_Departement_From_ID_Opleiding_COMPUTED_COLUMN]([ID_Opleiding])),
 CONSTRAINT [PK__kzlst_Opleiding___17F5F1ED] 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].[kzlst_Opleiding_Voorzitter] ADD  CONSTRAINT [DF_kzlst_Opleiding_Voorzitter_Geldig_Van]  DEFAULT ('01/10/2009') FOR [Geldig_Van]
GO
ALTER TABLE [dbo].[kzlst_Opleiding_Voorzitter] ADD  CONSTRAINT [DF_kzlst_Opleiding_Voorzitter_Geldig_Tot]  DEFAULT ('31/12/2999') FOR [Geldig_Tot]
GO

SYNCRO SCRIPT (with ignore check constraints OFF and ignore identy increment OFF)

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [GPS]
GO
PRINT N'Disabling DDL triggers'
GO
DISABLE TRIGGER ALL ON DATABASE
GO
PRINT N'Dropping constraints from [dbo].[kzlst_Opleiding_Voorzitter]'
GO
ALTER TABLE [dbo].[kzlst_Opleiding_Voorzitter] DROP CONSTRAINT [PK__kzlst_Opleiding___17F5F1ED]
GO
PRINT N'Dropping constraints from [dbo].[kzlst_Opleiding_Voorzitter]'
GO
ALTER TABLE [dbo].[kzlst_Opleiding_Voorzitter] DROP CONSTRAINT [DF__kzlst_Opl__Geldi__60D0A58C]
GO
PRINT N'Dropping constraints from [dbo].[kzlst_Opleiding_Voorzitter]'
GO
ALTER TABLE [dbo].[kzlst_Opleiding_Voorzitter] DROP CONSTRAINT [DF__kzlst_Opl__Geldi__61C4C9C5]
GO
PRINT N'Rebuilding [dbo].[kzlst_Opleiding_Voorzitter]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[ID_Opleiding] [int] NOT NULL,
[ID_Opleidingsvoorzitter] [int] NOT NULL,
[Geldig_Van] [datetime] NOT NULL DEFAULT ('01/10/2009'),
[Geldig_Tot] [datetime] NOT NULL DEFAULT ('31/12/2999'),
[GELDIG] AS (case when [Geldig_Van]<dateadd(day,(0),datediff(day,(0),getdate())) AND [geldig_Tot]>=dateadd(day,(0),datediff(day,(0),getdate())) then (1) else (0) end),
[ID_Departement] AS ([dbo].[getID_Departement_From_ID_Opleiding_COMPUTED_COLUMN]([ID_Opleiding]))
)
GO
INSERT INTO [dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter]([ID], [ID_Opleiding], [ID_Opleidingsvoorzitter], [Geldig_Van], [Geldig_Tot]) SELECT [ID], [ID_Opleiding], [ID_Opleidingsvoorzitter], [Geldig_Van], [Geldig_Tot] FROM [dbo].[kzlst_Opleiding_Voorzitter]
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[dbo].[kzlst_Opleiding_Voorzitter]')
IF @idVal IS NOT NULL
    DBCC CHECKIDENT(N'[dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter]', RESEED, @idVal)
GO
DROP TABLE [dbo].[kzlst_Opleiding_Voorzitter]
GO
EXEC sp_rename N'[dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter]', N'kzlst_Opleiding_Voorzitter'
GO
PRINT N'Re-enabling DDL triggers'
GO
ENABLE TRIGGER trgNoRightJoins ON DATABASE
GO

:!: As you can see:
* the constraints are not recreated under the same name (even if they were the same)
* the primary key attribute is not recreated at all (even if it was there)

Then I went back to the synchronisation option and found that ignore Identity property on columns was on

SO if column on receiving end is allready an identity column and ignore identity property is ON the synchronisation fails

Furthermore i changed 'ignore CONSTRAINT AND INDEX NAMES' to OFF

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [GPS]
GO
PRINT N'Disabling DDL triggers'
GO
DISABLE TRIGGER ALL ON DATABASE
GO
PRINT N'Dropping constraints from [dbo].[kzlst_Opleiding_Voorzitter]'
GO
ALTER TABLE [dbo].[kzlst_Opleiding_Voorzitter] DROP CONSTRAINT [PK__kzlst_Opleiding___17F5F1ED]
GO
PRINT N'Dropping constraints from [dbo].[kzlst_Opleiding_Voorzitter]'
GO
ALTER TABLE [dbo].[kzlst_Opleiding_Voorzitter] DROP CONSTRAINT [DF__kzlst_Opl__Geldi__60D0A58C]
GO
PRINT N'Dropping constraints from [dbo].[kzlst_Opleiding_Voorzitter]'
GO
ALTER TABLE [dbo].[kzlst_Opleiding_Voorzitter] DROP CONSTRAINT [DF__kzlst_Opl__Geldi__61C4C9C5]
GO
PRINT N'Rebuilding [dbo].[kzlst_Opleiding_Voorzitter]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[ID_Opleiding] [int] NOT NULL,
[ID_Opleidingsvoorzitter] [int] NOT NULL,
[Geldig_Van] [datetime] NOT NULL CONSTRAINT [DF_kzlst_Opleiding_Voorzitter_Geldig_Van] DEFAULT ('01/10/2009'),
[Geldig_Tot] [datetime] NOT NULL CONSTRAINT [DF_kzlst_Opleiding_Voorzitter_Geldig_Tot] DEFAULT ('31/12/2999'),
[GELDIG] AS (case when [Geldig_Van]<dateadd(day,(0),datediff(day,(0),getdate())) AND [geldig_Tot]>=dateadd(day,(0),datediff(day,(0),getdate())) then (1) else (0) end),
[ID_Departement] AS ([dbo].[getID_Departement_From_ID_Opleiding_COMPUTED_COLUMN]([ID_Opleiding]))
)
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter] ON
GO
INSERT INTO [dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter]([ID], [ID_Opleiding], [ID_Opleidingsvoorzitter], [Geldig_Van], [Geldig_Tot]) SELECT [ID], [ID_Opleiding], [ID_Opleidingsvoorzitter], [Geldig_Van], [Geldig_Tot] FROM [dbo].[kzlst_Opleiding_Voorzitter]
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter] OFF
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[dbo].[kzlst_Opleiding_Voorzitter]')
IF @idVal IS NOT NULL
    DBCC CHECKIDENT(N'[dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter]', RESEED, @idVal)
GO
DROP TABLE [dbo].[kzlst_Opleiding_Voorzitter]
GO
EXEC sp_rename N'[dbo].[tmp_rg_xx_kzlst_Opleiding_Voorzitter]', N'kzlst_Opleiding_Voorzitter'
GO
PRINT N'Re-enabling DDL triggers'
GO
ENABLE TRIGGER trgNoRightJoins ON DATABASE
GO

It is still missing the PK constraint

So i put the option 'ignore indexes' OFF

:!: I thought the ignore options where there to not detect a difference between tables, I did not know the full impact on the synchronisation script. If the table is synchronized for another reason (column order) and theres a table rebuild, it should not throw away constraints that were allready there (but ignored for tablediff)

Lxocram

Chris,

Do you get what i mean?
Is use the ignore options to only detect some differences (my software is also uesd by another company so and some options are different like SQLUsers indexes etc... )

But it seems that when a table rebuild occurs, the table is not fully rebuilt when some ignore options are on

Chris Auckland

Thanks for your reply, and sorry for the delay.

I see what you mean, so I was just putting a test case together for the development team.

I'll update you with a bug reference number.

Sorry to not keep you updated.

Chris Auckland

I've create the bug report now, so thanks for the poke.

1) The identity Insert problem.

I've confirmed this. If you use the 'ignore identity property' option and SQL Compare needs to rebuild the table. It forgets to set the identity insert when it inserts the data into the temp table. This is now logged under the bug tracking code SC-5647.

2) Constraint Names

It looks like the new constraint names are consistent with the names on the source schema, so I think this is the correct behaviour. The names are only ignored for the comparison, so a sync will always use the names from the source database.

3) Recreation of PK.

I couldn't reproduce this. The PK is actually recreated after the temp table is renamed to the correct table name, so it might just be happening further down the script to where you expect it, but it should be there.

Does this explain the behaviour, or have I missed anything?