May 31, 2007 at 5:51 am
I am trying to copy record rows into a linked server via the following SQL syntax where XMLCol is an xml column type
(note this is running in SQL Express SP2):
INSERT INTO OPENDATASOURCE('SQLNCLI','DataSource=MACHINENAME\SQLEXPRESS;Integrated Security=SSPI').[MyDatabase].dbo.MyTable([IndexKey],
[FirstName], [LastName], [XMLCol] SELECT [IndexKey], [FirstName], [LastName], [XMLCol]
FROM [MyDatabase].dbo.MyTable WHERE [MyDatabase].dbo.MyTable.KeyIndex = 1
However when I run this query I get the following error from SQL Server Manager Studio:
Msg 9514, Level 16, State 1, Line 1Xml data type is not supported in distributed queries.
Remote object 'OPENDATASOURCE' has xml column(s).
Further looking has taken me to "SQL Server 2005 Books Online Guidelines for Using Distributed Queries" which states:
"Tables that have xml columns cannot be queried, even if the query accesses non-xml columns of the table."
Does anyone know of a work around so one could use XML columns in distributed queries?
June 1, 2007 at 12:02 pm
I don't know if it is possible to script the custom XML type in SQL Express, but if you can do so. Make sure to change all the DB references to the target DB then create all those used XML types in the target DB and after that you may be able to run yor INSERT query.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply