Force column order - Identity_Insert when no PK defined

• 
  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?

  Chris Auckland March 05, 2012 15:39

  - 0 +
• 
  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 March 06, 2012 04:33

  - 0 +
• 
  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

  Lxocram March 08, 2012 10:53

  - 0 +
• 
  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 March 08, 2012 10:56

  - 0 +
• 
  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 Auckland March 08, 2012 11:34

  - 0 +

Add comment

Please sign in to leave a comment.