I am trying to insert rows from server1.table1 to server2.table2. I already create a linked server in order to query tables in both servers and it works.

However because server2.table2 has an identity column,and though I put the hint set identity_insert table2 on, the insert operation failed and I got the following errors in french:

L'objet "" est introuvable, car il n'existe pas ou vous ne disposez pas des autorisations requises.

which mean that the object is not found or we don't have the required permission to do the operation.

when I create the same table without the identity column the insert operation succeed!!

The query was executed from server1

Is there a specific permission that allow the operation to succeed or the problem is not at permission level?

Thank you

  • It's not a permission issue. You can't specify the value for an identity column. You can still make that column be the primary key and/or clustered index, however. Oct 18 '16 at 13:58
  • When you wrote set identity_insert table2 on did you qualify it with server and schema name? I never tried to do indentity_insert across linked server. Oct 18 '16 at 14:28
  • You can specify the value for an identity column, provided that you set identity_insert on, as Op as has mentioned here.
    – Nic
    Oct 18 '16 at 14:29

Microsoft documentation indicates that you can only use a 3-part naming convention for the SET IDENTITY_INSERT command.

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Somebody else had this same issue, and provided a solution using a temp table, and remote stored procedure to do the work.

  • Yes I already did it with a temp table but I was surprised that we cannot do it directly with the set identity_insert clause. Oct 18 '16 at 15:34
  • It's certainly unfortunate. Using a remote procedure is a good workaround. You might want to consider creating a Connect item with Microsoft to see if they would put that in the product.
    – Nic
    Oct 18 '16 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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