Getting an Error on inserting data when using Linked Server Queries

sql-server insert linked-server
Vishal picture Vishal · Oct 5, 2010 · Viewed 10.6k times · Source

UPDATE : The issue was col1 was hiereachyid type and even a select didnt work for it.

Hi all,

I am getting this error -

Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through query to access remote object '"RemoteDb"."dbo"."RemoteTable"'.

I have already setup the linked server [RemoteServer.dev.com].I was trying to perform an bulk insert from a remote table into the current table something like this -

INSERT INTO [CurrentDb].[dbo].[Mytable]
     (
       col1,
       col2
      )
 SELECT
  col1,col2
 FROM [RemoteServer.dev.com].[RemoteDb].[dbo].[RemoteTable]

Can anyone please help me out..thanks.

Answer

Joe Stefanelli picture Joe Stefanelli · Oct 5, 2010

As the error indicates, you need a pass-through query here because of the datatypes. Try this:

INSERT INTO [CurrentDb].[dbo].[Mytable]
     (
       col1,
       col2
      )
    SELECT col1, col2 
        FROM OPENQUERY([RemoteServer.dev.com], 'SELECT col1, col2 FROM [RemoteDb].[dbo].[RemoteTable]')

Related questions

How to insert a row into a linked server table?

I have a server SourceServer I am connected to which has a linked server TargetServer. How should an insert statement looks like (I need to reference Linked server, database, namespace, table): //Connected to [SourceServer] USE [SourceDatabase] DECLARE @HelloWorld NVARCHAR(255) SELECT @…

Read More
error when insert into linked server

I want to insert some data on the local server into a remote server, and used the following sql: select * into linkservername.mydbname.dbo.test from localdbname.dbo.test But it throws the following error The object name 'linkservername.mydbname.…

Read More
Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More