Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I might be forgetting something, but essentially, I'm struggling with IDENTITY_INSERT.

I'm trying to move data between tables, both of which have an identity column (similar schema).

SET IDENTITY_INSERT DestinationTable ON;

returns that IDENTITY_INSERT is already ON for that table. (Error Msg 8107) Fine, it's already ON, I can work with that, right?

Then:

INSERT INTO DestinationTable (Id, ColA) SELECT Id, ColA FROM SourceTable;

returns that I can't insert an explicit value .. when IDENTITY_INSERT is OFF (Error Msg 544). But it just said it was ON.

So, what's going on?

EDIT: Actual script (save for table names and structure):

SET IDENTITY_INSERT User2.DestinationTable ON;
INSERT INTO User2.DestinationTable (Id, ColA) SELECT Id, ColA FROM User1.SourceTable;
SET IDENTITY_INSERT User2.DestinationTable OFF;
share|improve this question
    
Dropping and recreating the table does not resolve the issue. –  MPelletier Mar 20 '12 at 14:46
    
Are you doing this through 2 query windows? Only one connection can have that set for a single table I believe. –  Thomas Stringer Mar 20 '12 at 14:50
    
@Shark - They both can have it set but obviously setting in one only then doesn't automatically allow the other to insert explicit values. –  Martin Smith Mar 20 '12 at 14:52
    
Same window. My script is exactly that (save for the actual table structure. The Set followed by the Insert. Followed by a final Set to turn it back off. –  MPelletier Mar 20 '12 at 14:52
    
@MPelletier - So you aren't using any dynamic SQL or stored procedures or anything? If turned on in a child batch it gets reset automatically when that exits. –  Martin Smith Mar 20 '12 at 14:56

1 Answer 1

up vote 4 down vote accepted

Is it possible you have two tables with the same name in different schemas? Some random doc I found online states that message 8107 appears when you already have IDENTITY_INSERT set to ON for another table in the db...

Another debugging step to check: You state that the issue keep occurring even after you drop and recreate the table, but what if you drop and don't recreate the table, and try to enable IDENTITY_INSERT on another table? What happens then?

share|improve this answer
    
Same table names. Dammit, I can't post my actual code (as per contract) and that makes you guys look at the wrong things. Alright, I'll work with that. –  MPelletier Mar 20 '12 at 15:25
1  
@MPelletier always specify schema name. sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/… –  Aaron Bertrand Mar 20 '12 at 15:45
    
@AaronBertrand That I always did, on my side. I don't know if it's the connection, but ultimately I started a new one, encapsulated the operations in a transaction, and now it works. –  MPelletier Mar 20 '12 at 16:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.