[SOLVED] Cannot insert into identity column of linked server

Cannot insert into identity column of linked server

Even when I set the IDENTITY_INSERT ON and specify the columns, I still get an error when inserting to a linked server.

When on the server directly:

CREATE TABLE InsertTest (
    PrimaryAutoKey int NOT NULL
        IDENTITY(1, 1) PRIMARY KEY,
    ID nvarchar(10) NOT NULL,
    Description nvarchar(50)
)

INSERT INTO InsertTest(ID, Description)
VALUES ('Test01', 'First record; key auto-assigned')

SET IDENTITY_INSERT InsertTest ON

INSERT INTO InsertTest(PrimaryAutoKey, ID, Description)
VALUES (10, 'Test10', 'Second record; key specified')

SELECT * FROM InsertTest
/*
PrimaryAutoKey ID         Description
-------------- ---------- --------------------------------------------------
1              Test01     First record; key auto-assigned
10             Test10     Second record; key specified
*/

All good so far. But when I issue the following from the remote server that has this as a linked server

INSERT INTO [LinkedServer].[DB].[dbo].InsertTest(PrimaryAutoKey, ID, Description)
VALUES (3, 'Test03', 'Third record; key specified')

I get the error:

OLE DB provider "MSOLEDBSQL" for linked server "LinkedServer" 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 10
The OLE DB provider "MSOLEDBSQL" for linked server "LinkedServer" could not INSERT INTO table "[LinkedServer].[DB].[dbo].[InsertTest]" because of column "PrimaryAutoKey". The user did not have permission to write to the column.

I am connecting to the linked server using the sa context and RPC IN/OUT are both allowed.

Solution

Send a whole batch to the linked server and run it there.

declare @sql nvarchar(max) = N'

INSERT INTO InsertTest(ID, Description)
VALUES (''Test01'', ''First record; key auto-assigned'')

SET IDENTITY_INSERT InsertTest ON

INSERT INTO InsertTest(PrimaryAutoKey, ID, Description)
VALUES (10, ''Test10'', ''Second record; key specified'')

'

exec (@sql) at LinkedServer