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.

Joe Stefanelli · Accepted Answer · 2010-10-05 15:26:02Z

19

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

answered Oct 5, 2010 at 15:26

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vishal Over a year ago

Hey thanks for you answer ....the issue was with the hierarchy id type I had on one of the fields

Joe Stefanelli Over a year ago

@Misnomer: Did this answer resolve your issue or did you use another technique?

Vishal Over a year ago

Ya I used OpenQuery to insert data for that table which had hierarchyid...rest of them work without openquery..

Collectives™ on Stack Overflow

Getting an Error on inserting data when using Linked Server Queries

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related