The OLE DB provider "SQLNCLI11" for linked server could not INSERT

Question

I have currently setup a linked server.
We have two SQL servers: server A, server B
On server A i have create a linked server to server B

Now on server A i must do a insert on server B DB.
My select works fine.
Also i have given full access to the user.
Am getting the following error below:

OLE DB provider "SQLNCLI11" for linked server "serverB" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7344, Level 16, State 1, Line 1 The OLE DB provider "SQLNCLI11" for linked server "serverb" could not INSERT INTO table "[serverb].[Data].[dbo].[test]" because of column "test_ID". The user did not have permission to write to the column

Answer 1

I've just had the same problem, and as Ben suggested above, it does seem related to an identity column. It's not a permissions problem, it seems that if you're inserting into unspecified columns over a linked server, SQL won't increment the destination identity column but instead gives you the misleading error message. So instead of doing what I initially did (and I guess what you did) and running:

INSERT dbo.tblA
SELECT * FROM RemoteServer.RemoteDB.dbo.tblA

try specifying the columns:

INSERT dbo.tblA (ColB, ColC)
SELECT ColB, ColC FROM RemoteServer.RemoteDB.dbo.tblA.

but omit the identity column.

It worked for me in SQL2014.