Red Gate forums :: View topic - Force column order - Identity_Insert when no PK defined
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  | Register 
Go to product support center
SQL Compare 10
SQL Compare 10 forum

Force column order - Identity_Insert when no PK defined

 Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Lxocram



Joined: 07 Jun 2007
Posts: 13
Location: Belgium, Ghent
PostPosted: Thu Mar 01, 2012 3:50 pm    Post subject: Force column order - Identity_Insert when no PK defined Reply with quote
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]
Back to top
View user's profile Send private message MSN Messenger
Chris Auckland



Joined: 24 Oct 2006
Posts: 710
Location: Red Gate Software Ltd.
PostPosted: Mon Mar 05, 2012 9:39 pm    Post subject: Reply with quote
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?
Code:
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?
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
Lxocram



Joined: 07 Jun 2007
Posts: 13
Location: Belgium, Ghent
PostPosted: Tue Mar 06, 2012 10:33 am    Post subject: Reply with quote
Chris,
128 is returned

I've tried it again with an other table

creation script (SSMS)
Code:
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)
Code:
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


Exclamation 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

Code:
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

Exclamation 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)
Back to top
View user's profile Send private message MSN Messenger
Lxocram



Joined: 07 Jun 2007
Posts: 13
Location: Belgium, Ghent
PostPosted: Thu Mar 08, 2012 4:53 pm    Post subject: Table rebuild + Ignore options = NONO Reply with quote
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
Back to top
View user's profile Send private message MSN Messenger
Chris Auckland



Joined: 24 Oct 2006
Posts: 710
Location: Red Gate Software Ltd.
PostPosted: Thu Mar 08, 2012 4:56 pm    Post subject: Reply with quote
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
Back to top
View user's profile Send private message Send e-mail
Chris Auckland



Joined: 24 Oct 2006
Posts: 710
Location: Red Gate Software Ltd.
PostPosted: Thu Mar 08, 2012 5:34 pm    Post subject: Reply with quote
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?
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group