Cannot perform SET operation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cannot perform SET operation

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 08:23:32

IDENTITY_INSERT is already ON for table 'Elbalazo.dbo.DeliveryOption'. Cannot perform SET operation for table 'City'

Here's my entire script:
--------------------------------------------------------------------

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'

/*Enable Constraints & Triggers again*/
--exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

-- City
SET IDENTITY_INSERT City ON
INSERT INTO Elbalazo.dbo.City (
[CityID]
,[CityName]
,[CountyID]
,[Active])
SELECT [CityID],[CityName],[CountyID],1
FROM [ElbalazoProduction].dbo.tbl_City
SET IDENTITY_INSERT City OFF

-- State
SET IDENTITY_INSERT [State] ON
INSERT INTO Elbalazo.dbo.State (
[StateID]
,[State]
,[Active])
SELECT [StateID],[State],1
FROM [ElbalazoProduction].dbo.tbl_State
SET IDENTITY_INSERT [State] OFF

-- NumberOfPeopleOption
SET IDENTITY_INSERT NumberOfPeopleOption ON
INSERT INTO [Elbalazo].[dbo].[NumberOfPeopleOption]
([NumberOfPeopleOptionID]
,[NumberOfPeopleNameOption]
,[Active])
SELECT [NumberOfPeopleID], [NumberOfPeopleName],1
FROM [ElbalazoProduction].dbo.tbl_NumberOfPeople
SET IDENTITY_INSERT NumberOfPeopleOption OFF

-- DeliveryOption
SET IDENTITY_INSERT DeliveryOption ON
INSERT INTO [Elbalazo].[dbo].[DeliveryOption]
([DeliveryOptionID]
,[DeliveryOptionName]
,[Active])
SELECT [DeliveryOptionID], [DeliveryOptionName],1
FROM [ElbalazoProduction].dbo.tbl_DeliveryOption
SET IDENTITY_INSERT DeliveryOption OFF

-- User
SET IDENTITY_INSERT [User] ON
INSERT INTO [Elbalazo].[dbo].[User]
([UserID]
,[FirstName]
,[LastName]
,[Address1]
,[Address2]
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[Email]
,[CreateDate]
,[Active])

SELECT [CustomerID]
,[FirstName]
,[LastName]
,[AddressLine1]
,NULL
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[EmailPrefix] + '@' + [EmailSuffix]
,[CreateDate]
,1
FROM [ElbalazoProduction].dbo.tbl_Customer
SET IDENTITY_INSERT [User] OFF

-- EntreeOption
SET IDENTITY_INSERT EntreeOption ON
INSERT INTO [Elbalazo].[dbo].[EntreeOption]
([EntreeOptionID]
,[EntreeOptionName]
,[Active])
SELECT [EntreeOptionID]
,[EntreeOptionName]
,1
FROM [ElbalazoProduction].dbo.tbl_EntreeOption
SET IDENTITY_INSERT EntreeOption OFF

-- CateringOrder
SET IDENTITY_INSERT CateringOrder ON
INSERT INTO [Elbalazo].[dbo].[CateringOrder]
([CateringOrderID]
,[UserID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID])
SELECT [CateringOrderID]
,[CustomerID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder
SET IDENTITY_INSERT CateringOrder OFF

-- CateringOrder_EntreeItem
SET IDENTITY_INSERT CateringOrderEntreeItem ON
INSERT INTO [Elbalazo].[dbo].[CateringOrderEntreeItem]
([CateringOrderEntreeItemID]
,[CateringOrderID]
,[EntreeItemID])
SELECT [CateringORder_EntreeItemID]
,[CateringOrderID]
,[EntreeItemID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder_EntreeItem
SET IDENTITY_INSERT CateringOrderEntreeItem OFF

select * from BeanOption
select * from CateringItemIncluded
select * from CateringOrder
select * from CateringOrderEntreeItem
select * from CateringOrderEntrees
select * from City
select * from Country
select * from DeliveryOption
select * from EntreeOption
select * from NumberOfPeopleOption
select * from [State]
select * from [User]

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 08:30:45

city is the first one you try so I guess the indentity insert on DeliveryOption is on from a previous failed run of the script or a test that you did before running it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 08:44:31

Cant spot anything obvious. But what you could do is to try prefixing the db.schema names to tables used in SET IDENTITY_INSERT and also try a batch seperator GO after each table population between identity insert off and next tables identity insert on.

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 16:47:08

>>>city is the first one you try so I guess the indentity insert on DeliveryOption is on from a previous failed run of the script or a test that you did before running it.

right I was thinking the same thing. But. I even disabled that delete script before it. I even turned it off explicitly but I still get that error, makes no difference what I do.

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 16:47:49

quote:
Originally posted by visakh16

Cant spot anything obvious. But what you could do is to try prefixing the db.schema names to tables used in SET IDENTITY_INSERT and also try a batch seperator GO after each table population between identity insert off and next tables identity insert on.

I'll try that next. Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 17:11:16

The delete won't affect it.
It's 'Elbalazo.dbo.DeliveryOption' that's the problem not city.
did you turn DeliveryOption off?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 17:19:37

Ok, unfortunately this did not work:

--********* INSERT SCRIPTS TO TRANSFER PRODUCTION DATA TO NEW ELBALAZO DATABASE *********--

--TODO go through delete all records and reinsert again but this time with identity
-- so that all keys match up just like they were in production
-- Make sure to keep current bean options but also include old and disable them for record history

-- Clean DB of all records

-- Stolen from http://dotnetslackers.com/community/blogs/mosessaur/archive/2007/12/09/sql-server-2005-clean-your-database-records-amp-reset-identity-columns-all-in-6-lines.aspx

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? -- NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

-- City
ALTER TABLE City
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.City ON
INSERT INTO Elbalazo.dbo.City (
[CityID]
,[CityName]
,[CountyID]
,[Active])
SELECT [CityID],[CityName],[CountyID],1
FROM [ElbalazoProduction].dbo.tbl_City
SET IDENTITY_INSERT City OFF

GO

-- State
ALTER TABLE State
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.[State] ON
INSERT INTO Elbalazo.dbo.State (
[StateID]
,[State]
,[Active])
SELECT [StateID],[State],1
FROM [ElbalazoProduction].dbo.tbl_State
SET IDENTITY_INSERT [State] OFF

GO

-- NumberOfPeopleOption
ALTER TABLE NumberOfPeopleOption
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.NumberOfPeopleOption ON
INSERT INTO [Elbalazo].[dbo].[NumberOfPeopleOption]
([NumberOfPeopleOptionID]
,[NumberOfPeopleNameOption]
,[Active])
SELECT [NumberOfPeopleID], [NumberOfPeopleName],1
FROM [ElbalazoProduction].dbo.tbl_NumberOfPeople
SET IDENTITY_INSERT [NumberOfPeople] OFF

GO

-- DeliveryOption
ALTER TABLE DeliveryOption
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.DeliveryOption ON
INSERT INTO [Elbalazo].[dbo].[DeliveryOption]
([DeliveryOptionID]
,[DeliveryOptionName]
,[Active])
SELECT [DeliveryOptionName],[DeliveryOptionName], 1
FROM [ElbalazoProduction].dbo.tbl_DeliveryOption
SET IDENTITY_INSERT DeliveryOption OFF

GO

-- User
ALTER TABLE [User]
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.[User] ON
INSERT INTO [Elbalazo].[dbo].[User]
([UserID]
,[FirstName]
,[LastName]
,[Address1]
,[Address2]
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[Email]
,[CreateDate]
,[Active])

SELECT [CustomerID]
,[FirstName]
,[LastName]
,[AddressLine1]
,NULL
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[EmailPrefix] + '@' + [EmailSuffix]
,[CreateDate]
,1
FROM [ElbalazoProduction].dbo.tbl_Customer
SET IDENTITY_INSERT [User] OFF

GO

-- EntreeOption
ALTER TABLE EntreeOption
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.EntreeOption ON
INSERT INTO [Elbalazo].[dbo].[EntreeOption]
([EntreeOptionID]
,[EntreeOptionName]
,[Active])
SELECT [EntreeOptionID]
,[EntreeOptionName]
,1
FROM [ElbalazoProduction].dbo.tbl_EntreeOption
SET IDENTITY_INSERT EntreeOption OFF

GO

-- CateringOrder
ALTER TABLE CateringOrder
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrder ON
INSERT INTO [Elbalazo].[dbo].[CateringOrder]
([CateringOrderID]
,[UserID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID])
SELECT [CateringOrderID]
,[CustomerID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder
SET IDENTITY_INSERT CateringOrder OFF

GO

-- CateringOrder_EntreeItem
ALTER TABLE CateringOrderEntreeItem
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrderEntreeItem ON
INSERT INTO [Elbalazo].[dbo].[CateringOrderEntreeItem]
([CateringOrderEntreeItemID]
,[CateringOrderID]
,[EntreeItemID])
SELECT [CateringORder_EntreeItemID]
,[CateringOrderID]
,[EntreeItemID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder_EntreeItem
SET IDENTITY_INSERT CateringOrderEntreeItem OFF

GO

select * from BeanOption
select * from CateringItemIncluded
select * from CateringOrder
select * from CateringOrderEntreeItem
select * from CateringOrderEntrees
select * from City
select * from Country
select * from DeliveryOption
select * from EntreeOption
select * from NumberOfPeopleOption
select * from [State]
select * from [User]

Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(316 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 17:20:56

This is wierd. If I take out the enabling of the identity insert and stuff and just do a straight up insert all is fine for this script. But that's not all that I want. I want to ensure I'm inserting the same keys when I port this stuff over in my script so I need to definitely have this identity insert working properly.

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 17:21:59

I don't mean to double post but, I added the dbo to the end sets also

--********* INSERT SCRIPTS TO TRANSFER PRODUCTION DATA TO NEW ELBALAZO DATABASE *********--

--TODO go through delete all records and reinsert again but this time with identity
-- so that all keys match up just like they were in production
-- Make sure to keep current bean options but also include old and disable them for record history

-- Clean DB of all records

-- Stolen from http://dotnetslackers.com/community/blogs/mosessaur/archive/2007/12/09/sql-server-2005-clean-your-database-records-amp-reset-identity-columns-all-in-6-lines.aspx

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? -- NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

-- City
ALTER TABLE City
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.City ON
INSERT INTO Elbalazo.dbo.City (
[CityID]
,[CityName]
,[CountyID]
,[Active])
SELECT [CityID],[CityName],[CountyID],1
FROM [ElbalazoProduction].dbo.tbl_City
SET IDENTITY_INSERT Elbalazo.dbo.City OFF

GO

-- State
ALTER TABLE State
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.[State] ON
INSERT INTO Elbalazo.dbo.State (
[StateID]
,[State]
,[Active])
SELECT [StateID],[State],1
FROM [ElbalazoProduction].dbo.tbl_State
SET IDENTITY_INSERT Elbalazo.dbo.[State] OFF

GO

-- NumberOfPeopleOption
ALTER TABLE NumberOfPeopleOption
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.NumberOfPeopleOption ON
INSERT INTO [Elbalazo].[dbo].[NumberOfPeopleOption]
([NumberOfPeopleOptionID]
,[NumberOfPeopleNameOption]
,[Active])
SELECT [NumberOfPeopleID], [NumberOfPeopleName],1
FROM [ElbalazoProduction].dbo.tbl_NumberOfPeople
SET IDENTITY_INSERT Elbalazo.dbo.[NumberOfPeople] OFF

GO

-- DeliveryOption
ALTER TABLE DeliveryOption
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.DeliveryOption ON
INSERT INTO [Elbalazo].[dbo].[DeliveryOption]
([DeliveryOptionID]
,[DeliveryOptionName]
,[Active])
SELECT [DeliveryOptionName],[DeliveryOptionName], 1
FROM [ElbalazoProduction].dbo.tbl_DeliveryOption
SET IDENTITY_INSERT Elbalazo.dbo.DeliveryOption OFF

GO

-- User
ALTER TABLE [User]
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.[User] ON
INSERT INTO [Elbalazo].[dbo].[User]
([UserID]
,[FirstName]
,[LastName]
,[Address1]
,[Address2]
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[Email]
,[CreateDate]
,[Active])

SELECT [CustomerID]
,[FirstName]
,[LastName]
,[AddressLine1]
,NULL
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[EmailPrefix] + '@' + [EmailSuffix]
,[CreateDate]
,1
FROM [ElbalazoProduction].dbo.tbl_Customer
SET IDENTITY_INSERT Elbalazo.dbo.[User] OFF

GO

-- EntreeOption
ALTER TABLE EntreeOption
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.EntreeOption ON
INSERT INTO [Elbalazo].[dbo].[EntreeOption]
([EntreeOptionID]
,[EntreeOptionName]
,[Active])
SELECT [EntreeOptionID]
,[EntreeOptionName]
,1
FROM [ElbalazoProduction].dbo.tbl_EntreeOption
SET IDENTITY_INSERT Elbalazo.dbo.EntreeOption OFF

GO

-- CateringOrder
ALTER TABLE CateringOrder
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrder ON
INSERT INTO [Elbalazo].[dbo].[CateringOrder]
([CateringOrderID]
,[UserID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID])
SELECT [CateringOrderID]
,[CustomerID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrder OFF

GO

-- CateringOrder_EntreeItem
ALTER TABLE CateringOrderEntreeItem
-- NOCHECK CONSTRAINT ALL
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrderEntreeItem ON
INSERT INTO [Elbalazo].[dbo].[CateringOrderEntreeItem]
([CateringOrderEntreeItemID]
,[CateringOrderID]
,[EntreeItemID])
SELECT [CateringORder_EntreeItemID]
,[CateringOrderID]
,[EntreeItemID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder_EntreeItem
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrderEntreeItem OFF

GO

select * from BeanOption
select * from CateringItemIncluded
select * from CateringOrder
select * from CateringOrderEntreeItem
select * from CateringOrderEntrees
select * from City
select * from Country
select * from DeliveryOption
select * from EntreeOption
select * from NumberOfPeopleOption
select * from [State]
select * from [User]

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 17:32:05

Even if I explicitely run this

SET IDENTITY_INSERT Elbalazo.dbo.City OFF

Then just run a test using just one of those inserts here, I still get the same error saying Identity Insert is already ON even though I just disabled it:

SET IDENTITY_INSERT Elbalazo.dbo.City ON
INSERT INTO Elbalazo.dbo.City (
[CityID]
,[CityName]
,[CountyID]
,[Active])
SELECT [CityID],[CityName],[CountyID],1
FROM [ElbalazoProduction].dbo.tbl_City
SET IDENTITY_INSERT Elbalazo.dbo.City OFF

Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'Elbalazo.dbo.NumberOfPeopleOption'. Cannot perform SET operation for table 'Elbalazo.dbo.City'.

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 17:32:48

AHHHHHHHHHHHHHHHHHHHH! I HATE SQL. (Sorry, I'm a C# developer who knows a good amout but...) frustrated!

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 17:33:31

I guess I should say I hate DEBUGGING a language that is not easily debugged like it is in OOP. :( no harm meant. But it's true.

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 17:39:09

What's also strange is, if I got into Management Studio, right-click the City table for example, then modify..I notice that the Identity Specification is set to NO. Well fine but I also notice that I'm not able to enable it manually as the (Is Identity) dropdown right below it is greyed out. I check, and my database is not read-only so what gives? I have the proper permissions with db_owner access. So something is up. Not sure what's limiting/blocking me but it sure is driving me crazy with this entire situation and this script.

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 17:40:50

Alright so I tested again as I'm just testint on one table just to see what happens (SET IDENTITY_INSERT Elbalazo.dbo.City OFF). Ran this and sure enough it does set the Identity and Is Identity propertys of my table back to YES. But this still doesn't fix my script problem per my last script post.

dba123
Yak Posting Veteran

90 Posts

Posted - 2008-04-14 : 17:43:54

Ok, resolved (hell if I know). I just went through an manually ran each SET IDENTITY_INSERT OFF for each and every table. Then ran it again and again and now it's fine.

--********* INSERT SCRIPTS TO TRANSFER PRODUCTION DATA TO NEW ELBALAZO DATABASE *********--

--TODO go through delete all records and reinsert again but this time with identity
-- so that all keys match up just like they were in production
-- Make sure to keep current bean options but also include old and disable them for record history

-- Clean DB of all records

-- Stolen from http://dotnetslackers.com/community/blogs/mosessaur/archive/2007/12/09/sql-server-2005-clean-your-database-records-amp-reset-identity-columns-all-in-6-lines.aspx

/*Disable Constraints & Triggers*/
--exec sp_MSforeachtable 'ALTER TABLE ? -- NOCHECK CONSTRAINT ALL'
--exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
--exec sp_MSforeachtable 'DELETE ?'

/*Enable Constraints & Triggers again*/
--exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
--exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
--exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

-- City
--ALTER TABLE Elbalazo.dbo.City
SET IDENTITY_INSERT Elbalazo.dbo.City ON
INSERT INTO Elbalazo.dbo.City (
[CityID]
,[CityName]
,[CountyID]
,[Active])
SELECT [CityID],[CityName],[CountyID],1
FROM [ElbalazoProduction].dbo.tbl_City
SET IDENTITY_INSERT Elbalazo.dbo.City OFF

-- State
--ALTER TABLE Elbalazo.dbo.State
SET IDENTITY_INSERT Elbalazo.dbo.[State] ON
INSERT INTO Elbalazo.dbo.State (
[StateID]
,[State]
,[Active])
SELECT [StateID],[State],1
FROM [ElbalazoProduction].dbo.tbl_State
SET IDENTITY_INSERT Elbalazo.dbo.[State] OFF

-- NumberOfPeopleOption
--ALTER TABLE Elbalazo.dbo.NumberOfPeopleOption
SET IDENTITY_INSERT Elbalazo.dbo.NumberOfPeopleOption ON
INSERT INTO [Elbalazo].[dbo].[NumberOfPeopleOption]
([NumberOfPeopleOptionID]
,[NumberOfPeopleNameOption]
,[Active])
SELECT [NumberOfPeopleID], [NumberOfPeopleName],1
FROM [ElbalazoProduction].dbo.tbl_NumberOfPeople
SET IDENTITY_INSERT Elbalazo.dbo.[NumberOfPeopleOption] OFF

-- DeliveryOption
--ALTER TABLE Elbalazo.dbo.DeliveryOption
SET IDENTITY_INSERT Elbalazo.dbo.DeliveryOption ON
INSERT INTO [Elbalazo].[dbo].[DeliveryOption]
([DeliveryOptionID]
,[DeliveryOptionName]
,[Active])
SELECT [DeliveryOptionID],[DeliveryOptionName], 1
FROM [ElbalazoProduction].dbo.tbl_DeliveryOption
SET IDENTITY_INSERT Elbalazo.dbo.DeliveryOption OFF

-- User
--ALTER TABLE Elbalazo.dbo.[User]
SET IDENTITY_INSERT Elbalazo.dbo.[User] ON
INSERT INTO [Elbalazo].[dbo].[User]
([UserID]
,[FirstName]
,[LastName]
,[Address1]
,[Address2]
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[Email]
,[CreateDate]
,[Active])

SELECT [CustomerID]
,[FirstName]
,[LastName]
,[AddressLine1]
,NULL
,[CityID]
,[StateID]
,[Zip]
,[PhoneAreaCode]
,[PhonePrefix]
,[PhoneSuffix]
,[EmailPrefix] + '@' + [EmailSuffix]
,[CreateDate]
,1
FROM [ElbalazoProduction].dbo.tbl_Customer
SET IDENTITY_INSERT Elbalazo.dbo.[User] OFF

-- EntreeOption
--ALTER TABLE Elbalazo.dbo.EntreeOption
SET IDENTITY_INSERT Elbalazo.dbo.EntreeOption ON
INSERT INTO [Elbalazo].[dbo].[EntreeOption]
([EntreeOptionID]
,[EntreeOptionName]
,[Active])
SELECT [EntreeOptionID]
,[EntreeOptionName]
,1
FROM [ElbalazoProduction].dbo.tbl_EntreeOption
SET IDENTITY_INSERT Elbalazo.dbo.EntreeOption OFF

-- CateringOrder
--ALTER TABLE Elbalazo.dbo.CateringOrder
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrder ON
INSERT INTO [Elbalazo].[dbo].[CateringOrder]
([CateringOrderID]
,[UserID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID])
SELECT [CateringOrderID]
,[CustomerID]
,[NumberOfPeopleID]
,[BeanOptionID]
,[TortillaOptionID]
,[CreateDate]
,[Notes]
,[EventDate]
,[DeliveryOptionID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrder OFF

-- CateringOrder_EntreeItem
--ALTER TABLE Elbalazo.dbo.CateringOrderEntreeItem
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrderEntreeItem ON
INSERT INTO [Elbalazo].[dbo].[CateringOrderEntreeItem]
([CateringOrderEntreeItemID]
,[CateringOrderID]
,[EntreeItemID])
SELECT [CateringORder_EntreeItemID]
,[CateringOrderID]
,[EntreeItemID]
FROM [ElbalazoProduction].dbo.tbl_CateringOrder_EntreeItem
SET IDENTITY_INSERT Elbalazo.dbo.CateringOrderEntreeItem OFF

select * from BeanOption
select * from CateringItemIncluded
select * from CateringOrder
select * from CateringOrderEntreeItem
select * from CateringOrderEntrees
select * from City
select * from Country
select * from DeliveryOption
select * from EntreeOption
select * from NumberOfPeopleOption
select * from [State]
select * from [User]

yasirbam
Starting Member

1 Post

Posted - 2008-11-14 : 12:30:19

This is what BOL says about this issue:

"At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for."

Using GO between the statements to break down the batch as suggested by vishakh16 should work.

Yasir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources