Copying table from one sql server instance to another instance

I have added the remote server as a linked server in Management Studio to import data from that server into my local SQL Express instance.

Running the following query works fine for most tables:

Select * Into TheTable
From [LinkedServer].[RemoteDB].[dbo].[TheTable]

But a particular table containing spatial data, I received this error message:

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

To solve this problem I had to run the following query:

Select * Into TheTable
From Select * from openquery([LinkedServere], ‘select * from [RemoteDB].[dbo].[TheTable]’)

And it worked fine!

 

Leave a Reply

Your email address will not be published. Required fields are marked *