October 23, 2008 at 8:54 am
I've written a query which works fine across databases locally which takes the content of some holding tables and copies the data into a live table. The live table contains an identity column which is set to auto increment.
The problem lies in that when I try and do the same across linked servers an error is thrown telling me that I haven't specified the correct number of column values
This is because I don't include the identity column in the select list for the INSERT INTO statement - if I try to add a value for that column (such as 0) the server tells me that I don't have permission to write to that column on the linked server table.
INSERT INTO [servername].DocumentHeader_Live.dbo.Table
SELECT
-- ID, column not needed as ID should be auto increment - but doesn't work with or without it!
Column1,
Column2
FROM DocumentHeader_Holding
give me
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
If I add the ID column I get
Msg 7344, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "wxp-a66837" could not INSERT INTO table "[wxp-a66837].[EliorCybiz].[dbo].[AvTransactionsUM]" because of column "TransactionID". The user did not have permission to write to the column.
How do I INSERT INTO a linked server table from a database table on my server?
Thanks in advance!
October 23, 2008 at 8:56 am
Oh I always do that, figure it out 2 seconds after I post - I included the column list and it worked! 😛
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply