Cannot insert into identity column of linked server

Question

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.

score 3 · Accepted Answer · answered Dec 31 '20 at 17:16

3

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

eg

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

answered Dec 31 '20 at 17:16

David Browne - Microsoft

80,331
6
39
67

1

This solved it. There was one other issue it exposed: the SET IDENTITY_INSERT _tablename_ ON statement is _connection specific_ and has to be issued from the client connection before the insert. I had set it on the server erroneously thinking it would remain set to ON for all connections until set to OFF. – Matthew Tipton Jan 04 '21 at 13:29

Cannot insert into identity column of linked server

1 Answers1