Getting an Error on inserting data when using Linked Server Queries

Question

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 1

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]')