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.

EDIT: See answer here: http://stackoverflow.com/questions/19119303/how-to-set-identity-insert-to-on-in-sqlserver

I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created an 'admin' account and gave it permissions to do anything (to the best of my knowledge).

Whenever I run the command under either account I get the result of '0 rows affected', then I am unable to run the query to insert the rows I wish to insert with the following error:

Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.

Additionally I've tried to include the schema name in the query like:

set identity_insert mySchema.table_name on; 

but I get the following error:

Cannot find the object "mySchema.table_name" because it does not exist or you do not have permissions.

Also of note, my project looks like:

localhost\SQLEXPRESS
-Databases
--System Databases
--mySchema

Does anyone know a solution to this problem? Thanks.

share|improve this question
add comment

1 Answer 1

You are using the right syntax:

SET IDENTITY_INSERT dbo.sometable ON;

However, the schema in SQL Server is not the same as the database: http://stackoverflow.com/questions/5323740/difference-between-database-and-schema

The default schema for objects is dbo as in my example. You do not need to specify the database name if you are connected to it, but you could like this:

SET IDENTITY_INSERT DbName.dbo.sometable ON;

There seems to be no way to get the current setting as it is session dependend only and not persisted: http://stackoverflow.com/questions/10637976/how-do-you-check-if-identity-insert-is-set-to-on-or-off-in-sql-server

If you run this query in your database it returns the fully qualified names for all tables in there:

SELECT QUOTENAME(DB_NAME())+'.'+QUOTENAME(OBJECT_SCHEMA_NAME(object_id))+'.'+QUOTENAME(OBJECT_NAME(object_id)) AS FullTableName FROM sys.tables

That should help you with the error you are getting.

share|improve this answer
    
Yep, just double checked. –  Joe Essey Oct 1 '13 at 14:56
    
See my update above. –  Sebastian Meine Oct 1 '13 at 14:59
    
Unfortunately, I'm still experiencing the same result. I've tried both dbo.sometable and DbName.dbo.sometable. But thank you for explaining that db and schema are different here. –  Joe Essey Oct 1 '13 at 15:03
    
Is there a way I can query to see the setting of identity_insert for a given table? –  Joe Essey Oct 1 '13 at 15:06
    
See my update above. –  Sebastian Meine Oct 1 '13 at 15:12
show 2 more comments

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.