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

5 years ago

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.

Create a linked server (SQL2019 in my demo)

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

Insert some dummy data (not a mandatory step)

INSERT INTO [dbo].[EmpInfo] ([Id] ,[FName] ,[MoreInfo])
     VALUES (1,'SQL','&lt;LName&gt;Server&lt;/LName&gt;')
GO

Now, run a select statement from the source server to reproduce the server.

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

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

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)

SQL Error Messages, SQL Scripts, SQL Server, SQL Server 2019, SQL XML

SQL SERVER – FIX: Cannot Connect to SQL in Azure Virtual Machine From Laptop

SQL SERVER – FIX: Msg 8180 – Statement(s) Could not be Prepared. Deferred Prepare Could not be Completed

7 Comments. Leave new

RK
January 11, 2019 10:30 am
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
Bryan Rebok
February 6, 2019 5:33 am
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
Amir
May 6, 2019 1:47 pm
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
Sim
March 27, 2020 9:39 pm
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
Dzianis
March 16, 2021 12:13 pm
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
Jon Ellison
June 15, 2022 10:33 pm
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