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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

You are using the right syntax:

SET IDENTITY_INSERT dbo.sometable ON;

However, a schema in SQL Server is not the same as a database.

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-scoped and not persisted.

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;

SET IDENTITY_INSERT ON... must be run in the same session as the insert.

share|improve this answer

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.