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

Error:

Msg 9514, Level 16, State 1, Line 1
Xml data type is not supported in distributed queries. Remote object ‘OPENROWSET’ has xml column(s).

This error happens when XML Data Type is queried using OPENROWSET. It was very interesting for me to find the workaround for this.

Fix/WorkAround/Soulution:
I CAST the retrieved the XML data type to VARCHAR(MAX).

Example:
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID, CAST(MyXML as VARCHAR(MAX))
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name'
) AS a;

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Fix : Error : Msg 9514, Level 16, State 1, Line 1 Xml data type is not supported in distributed queries. Remote object ‘OPENROWSET’ has xml column(s).

  1. Hi,

    I am archiving data from source table having xml datatype and I am not allowed to create any new objects in the source.
    So I am unable to use the common work around of converting the xml datatype to varchar while inserting data through link server.

    Kindly help me to load xml data from source to target with link server.

    Thanks,
    Akila.

  2. Hi

    When any single row of the selected set has an xml data column exceeding varchar(max) in size (8000 on our instance), the whole query fails.

    Can you suggest how I can take the first 8000 characters of these problem records – perhaps truncating the xml field prior to casting or casting with truncation?

    Many Thanks
    Brett

  3. Hi Brett,

    How are you inserting the records? In SSIS there is feature to ignore and store such problem records.
    Otherwise you can insert only left 8000 character using LEFT or SUBSTRING t-sql function.

    Regards,
    Pinal Dave

  4. workaround that worked for me – create view to the XML-containing table on the remote server excluding(if you don’t need it) or converting the XML columns to nvarchar… this will “mask” it to the SQL engine enough to accept distributed query…

    • thanks!. the view was a quick an easy solution. when i tried openrowset still had some configuration issues that caused a different error than what started this posting.

      great webiste
      -Paul

  5. Hi ,

    I am firing trigger on table which will i have to update one column on another server of table.. but i am getting error… as belows…….

    Xml data type is not supported in distributed queries.

    table which i have to update using trigger have one xml column but i am not updating that column..

    Thanks….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s