SQL SERVER – FIX: Msg 9514 – XML Data Type is Not Supported in Distributed Queries

Sometimes, I get some quick questions via my blog comments and I do spend the time to search on them and reply or write a blog. In this blog we would learn how to fix: Msg 9514 – XML data type is not supported in distributed queries.

Here is the complete error message while running a query via linked server.

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

The error message is self-explanatory. Here is the way to reproduce the error.

  1. Create a linked server (SQL2019 in my demo)
  2. Create a table in the database (XMLDB in my demo) using below script.
    CREATE TABLE [dbo].[EmpInfo](
    	[Id] [int] NULL,
    	[FName] [nchar](10) NULL,
    	[MoreInfo]  NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
  3. Insert some dummy data (not a mandatory step)
    INSERT INTO [dbo].[EmpInfo] ([Id] ,[FName] ,[MoreInfo])
         VALUES (1,'SQL','<LName>Server</LName>')
    GO
    
  4. Now, run a select statement from the source server to reproduce the server.
    SELECT [Id]
    ,[FName]
    ,[MoreInfo]
    FROM [SQL2019].[XMLDB].[dbo].[EmpInfo]
    GO
    

SQL SERVER - FIX: Msg 9514 - XML Data Type is Not Supported in Distributed Queries xml-err-01

SOLUTION/WORKAROUND

The error message is clear that there is an XML column which we are selecting and that can’t happen in a distributed transaction. So, we need to fool SQL Server and tell that this is not an XML column.

Here is what worked for me:

SELECT op.[Id]
	,op.[FName]
	,CAST(op.[MoreInfo] AS XML) AS MoreInfo
FROM (
	SELECT *
	FROM OPENQUERY([SQL2019], 'SELECT [Id] ,[FName]
	,cast([MoreInfo] as varchar(max)) AS [MoreInfo] 
	FROM [XMLDB].[dbo].[EmpInfo]')
	) AS op

SQL SERVER - FIX: Msg 9514 - XML Data Type is Not Supported in Distributed Queries xml-err-02

You can simplify the query, but it gives you an idea about the solution. As you can see below, we have the data which we inserted.

Is there any other solution which you have can think of?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL Error Messages, SQL Scripts, SQL Server, SQL Server 2019, SQL XML
Previous Post
SQL SERVER – FIX: Cannot Connect to SQL in Azure Virtual Machine From Laptop
Next Post
SQL SERVER – FIX: Msg 8180 – Statement(s) Could not be Prepared. Deferred Prepare Could not be Completed

Related Posts

7 Comments. Leave new

  • Hi Pinal,

    You have initially used a Distrubuted Query and later changed it to an Open Query. Why is it so? Is it not possible to have a workaround solution with Distributed Query?
    BTW, a Distributed Query will NOT allow Spatial Data as well.

    Reply
  • Hi, Pinal.

    It looks like you are missing the XML column type declaration for the MoreInfo column in your create table script for [dbo].[EmpInfo].

    Reply
  • I would like to also contribute here other scenarios involving Delete, Update and Insert:
    — Insert —
    INSERT INTO OPENQUERY(MyLinkServer, ‘SELECT * FROM unit’)
    VALUES ( 1, 5, ‘fa’, ‘0’, GETDATE(), ‘1’, GETDATE(), ‘1’ );
    —————————————————————————————-
    — Update —
    –1
    UPDATE OPENQUERY(MyLinkServer , ‘SELECT * FROM unit WHERE id=4’)
    SET [is_deleted] = ‘0’;
    –2
    EXEC(‘UPDATE unit set is_deleted=”1” where id=4;’) AT MyLinkServer ;
    — 3
    UPDATE OPENQUERY(MyLinkServer , ‘SELECT * FROM unit’)
    SET [is_deleted] = ‘0’
    WHERE id = 4;
    —————————————————————————————-
    — Delete —
    DELETE OPENQUERY(MyLinkServer, ‘select * from unit where id=4;’);

    Reply
  • thanks a lot, it was really helpful

    Reply
  • Miguel Benitez
    August 5, 2020 1:46 am

    To work around this, it was simpler for me to create a view on the source server that parsed the XML elements into columns.

    Reply
  • It’s not best way.

    OPENQUERY ‘SELECT [Id] ,[FName]
    ,cast([MoreInfo] as varchar(max)) AS [MoreInfo]
    FROM [XMLDB].[dbo].[EmpInfo]’)

    Every time will execute queries and give us full dataset (all records).
    in a big table [EmpInfo] will be worst performance

    Let’s try create a new view in [XMLDB]

    Create view EmpInfo_withoutxml
    AS
    SELECT [Id] ,[FName]
    ,cast([MoreInfo] as varchar(max)) AS [MoreInfo]
    FROM [dbo].[EmpInfo]

    And access it via the linked server

    SELECT [Id]
    ,[FName]
    ,[MoreInfo]
    FROM [SQL2019].[XMLDB].[dbo].[EmpInfo_withoutxml]

    Any additional conditionals {where or other} will execute in [SQL2019] server and send only results by network.

    Reply
  • I encountered this issue moving table data from SQL 2012 to SQL 2016 via LinkedServer. To resolve this issue, I created a view of the Source table but CAST the two columns of XML data as nvarchar(MAX). I then was simply able to run “insert into target.table select * from remoteserver.databasename.dbo.vw_viewname” with no issues. My target table had the two columns formatted as XML type. Hope this helps others.

    Reply

Leave a Reply