Search code examples
sqlsql-serverremote-server

for linked server could not INSERT INTO table because of column "Id". The user did not have permission to write to the column

I am trying to copy over the missing rows from one MS sql server to the other, by using following query. I know connection works because up to insert the joined select works fine.

insert into [192.168.0.200].webanalyzer.dbo.sale
select * from WebAnalyzer.dbo.Sale where url COLLATE DATABASE_DEFAULT not in
(
    select url COLLATE DATABASE_DEFAULT From [192.168.0.200].webanalyzer.dbo.sale
)

however I am getting following error

Msg 7344, Level 16, State 1, Line 1 The OLE DB provider "MSOLEDBSQL" for linked server "192.168.0.200" could not INSERT INTO table "[192.168.0.200].[webanalyzer].[dbo].[sale]" because of column "Id". The user did not have permission to write to the column.

How do I give permission to remote server?

Solution

  • If the Id column has auto generated values, you cannot use explicit inserts. So change your query to:

    insert into [192.168.0.200].webanalyzer.dbo.sale ([columns])
select [columns] from WebAnalyzer.dbo.Sale where url COLLATE DATABASE_DEFAULT not in
(
    select url COLLATE DATABASE_DEFAULT From [192.168.0.200].webanalyzer.dbo.sale
)

    where [columns] are the columns you need to insert into