Copying Spatial Data between SQL Server Databases

Aim - to copy a table containing geometry fields between two database servers. The task was made slightly trickier as the two servers are on two different domains.

I wanted to use SQL rather than FME, or a SQL Server wizard, as it would be easier to script the process for the several tables I had to copy.

The first task was to set up linked servers. I used the following SQL (when logged in to my destination server, and using SQL Server Management Studio) to add the source server:

EXEC sp_addLinkedServer 'MY_SOURCE_SERVER_NAME'

I then needed to allow log-in access to the source server. The SQL below assumes there is a sa user with a password of sa on the source server:

sp_addlinkedsrvlogin @rmtsrvname = 'MY_SOURCE_SERVER_NAME'
 ,  @useself =  'FALSE'
 ,  @locallogin =  null
 ,  @rmtuser =  'sa'  
 ,  @rmtpassword =  'sa'

I thought I was then ready to run a “SELECT INTO” query to copy records from the source table into a new table in the destination database. However I was met by the following error:

Msg 7325, Level 16, State 1, Line 1
Objects exposing columns with CLR types are not allowed in distributed queries.
Please use a pass-through query to access remote object '"MY_SOURCE_SERVER_NAME"."schema_name"."TableName"'.

It appeared that the geometry columns containing the SQL Server spatial types were causing the problem.  After reading up on pass-through queries  OPENQUERY seemed to be the solution.  I tried the following SQL:

SELECT * INTO newschema.MyNewTableName
FROM OPENQUERY([MY_SOURCE_SERVER_NAME],
'SELECT * from [source_database_name].[schema_name].[TableName]');