SQL ERROR Fix : Error : Msg 9514, Level 16, State 1, Line 1 Xml data type is not supported in distributed queries. Remote object ‘LINKED_SERVER.Database.dbo.Table’ has xml column(s).

I ran into a rather senseless restriction put by MS when using INSERT INTO…SELECT FROM to transfer few rows through a Linked Server from a table that has a XML data type column even if that column is not listed in “insert into” statement. See detailed the reproduction of this error.

The insert statement from this example uses a variable for filtering options.

Statement:

DECLARE @maxid as nvarchar (max)
SET @maxid=(SELECT TOP 1 col1 from Table_Local ORDER BY col1 DESC)
Insert into Database.dbo.Table_Local (col1, col2, col3, XMLcol4)
select col1, col2, col3, XMLcol4
from  [Linked_Server].Database.dbo.Table_Remote t2  where col1 > @maxid )t2

Error:

Msg 9514, Level 16, State 1, Line 4
Xml data type is not supported in distributed queries.
Remote object 'LINKED_SERVER.Database.dbo.Table' has xml column(s).

Resolution:

1. Change the statement using OpenQuery function instead of Linked Server.
2. Cast the XML column retrieved to a varchar (max).

Basically, the data is queried on the remote server, converts the XML data to a varchar, sends the data to the requesting server and then reconverts it back to XML.

Here is the right statement:

DECLARE @TSQL varchar(8000)
DECLARE @maxid as nvarchar (max)
SET @maxid=(SELECT TOP 1 col1 from Table_Local ORDER BY col1 DESC)
SELECT  @TSQL = 'insert into Database.dbo.Table_Local
                          (col1, col2, col3, XMLcol4)
select col1, col2, col3, Cast(t2.XMLcol4 as XML) as XMLcol4
from OPENQUERY([LINKED_SERVER_NAME],''SELECT col1, col2, col3,
Cast(XMLcol4 as Varchar(max)) as XMLcol4
FROM
DATABASE.dbo.Table_Remote where col1 >  ''''' + @maxid + ''''''') t2'
EXEC (@TSQL)
  1. Thank u for document.

  2. to be reversed, from local to remote inserted, how could it? 😥

  3. this should work if you have the rights to insert: INSERT into openquery(LinkedServer, ‘dbo.table’) values (”,”)

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>