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

SQL
5 Comments

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)

, , , ,
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

5 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

Leave a Reply

Menu