0

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 "192.168.0.36.wt_delivery.dbo.table2" est introuvable, car il n'existe pas ou vous ne disposez pas des autorisations requises.

which mean that the object 192.168.0.36.wt_delivery.dbo.table2 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

3
  • 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. Commented Oct 18, 2016 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. Commented Oct 18, 2016 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
    Commented Oct 18, 2016 at 14:29

1 Answer 1

4

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.

2
  • 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. Commented Oct 18, 2016 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
    Commented Oct 18, 2016 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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