IDENTITY_INSERT is already ON for table xyz

toebens

hi,
i have the latest SQL Data compare installed and generated a complette sync script for every object.

when i run the script in MS Sql management studio (due to the fact http://www.red-gate.com/messageboard/vi ... t+datetime )

i get the following error(s):

Msg 8107, Level 16, State 1, Line 93
IDENTITY_INSERT is already ON for table 'MyDB.dbo.Fiscal'. Cannot perform SET operation for table 'dbo.Dimension'.
Checking identity information: current identity value 'NULL', current column value '30'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value 'NULL', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value 'NULL', current column value '6'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

these is/are the line(s) mentioned above with the following insert statements:
-- Add 6 rows to [dbo].[Dimension]
SET IDENTITY_INSERT [dbo].[Dimension] ON
INSERT INTO [dbo].[Dimension] ([DimensionID], [DimensionInternalName]) VALUES (1, 'Basiskompetenz')
INSERT INTO [dbo].[Dimension] ([DimensionID], [DimensionInternalName]) VALUES (2, 'Unternehmerische Kompetenz')
INSERT INTO [dbo].[Dimension] ([DimensionID], [DimensionInternalName]) VALUES (3, 'Methodenkompetenz')
INSERT INTO [dbo].[Dimension] ([DimensionID], [DimensionInternalName]) VALUES (4, 'Soziale Kompetenz')
INSERT INTO [dbo].[Dimension] ([DimensionID], [DimensionInternalName]) VALUES (5, 'Managementkompetenz')
INSERT INTO [dbo].[Dimension] ([DimensionID], [DimensionInternalName]) VALUES (6, 'Persönliche Kompetenz')
SET IDENTITY_INSERT [dbo].[Dimension] OFF

it seems that the generated sync script is buggy:
The column DimensionID is already set to "Identity Specification" yes in the schema.
And it should NOT be set OFF after the insert statements!

maybe it should be set OFF before the insert statements and set ON after these!???

can you please help me ASAP?
thanks, toebens

toebens

the generated script for version 6.1 and 7.0 don't differ very much. they differ in the format of the datetime value:

Script created by SQL Data Compare version 7.0.0.512 from Red Gate Software Ltd at 22.09.2008 14:30:52

there i had to add manually:
SET LANGUAGE 'US_ENGLISH'
GO

....

-- Add 2 rows to [dbo].[Fiscal]
SET IDENTITY_INSERT [dbo].[Fiscal] ON
INSERT INTO [dbo].[Fiscal] ([FiscalID], [FiscalYear], [BaroVoteBeginDate], [BaroVoteEndDate]) VALUES (1, 2008, '2008-09-17 00:00:00.000', '2008-09-30 00:00:00.000')
INSERT INTO [dbo].[Fiscal] ([FiscalID], [FiscalYear], [BaroVoteBeginDate], [BaroVoteEndDate]) VALUES (2, 2009, '2009-07-15 00:00:00.000', '2009-07-15 00:00:00.000')
SET IDENTITY_INSERT [dbo].[Fiscal] OFF


Script created by SQL Data Compare version 6.1.1.6 from Red Gate Software Ltd at 22.09.2008 17:08:54

(here i don't have to set the language explicitly - manually)

-- Add 2 rows to [dbo].[Fiscal]
SET IDENTITY_INSERT [dbo].[Fiscal] ON
INSERT INTO [dbo].[Fiscal] ([FiscalID], [FiscalYear], [BaroVoteBeginDate], [BaroVoteEndDate]) VALUES (1, 2008, '20080917 00:00:00.000', '20080930 00:00:00.000')
INSERT INTO [dbo].[Fiscal] ([FiscalID], [FiscalYear], [BaroVoteBeginDate], [BaroVoteEndDate]) VALUES (2, 2009, '20090715 00:00:00.000', '20090715 00:00:00.000')
SET IDENTITY_INSERT [dbo].[Fiscal] OFF

please fix this error in the new version ASAP

Michelle T

7.1 ought to fix your datetime problem. You should be able to get it by checking for updates in 7.0 or downloading the current version from the website.