I was doing some work this afternoon and needed to make inserts into some linked server. My syntax was fairly simple:
[on serverA\serverA]
insert into [server2\server2].dbname.dbo.copy_stats
select top 1 * from load_stats order by loadid desc
This should work but instead I got the following error:
The OLE DB provider “SQLNCLI” for linked server “server1\server1″ could not INSERT INTO table “[server2\server2].[dbname].[dbo].[copy_stats]” because of column “Loadid”. The user did not have permission to write to the column.
I tried a few things but ended with the same error. Then I looked at the table and realized there was an index on the table and also an identity is set on loadID column. After dropping the table and recreating it w/o any index and no identity set, I was able to make my insert.