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] [xml] 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
   

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://darkslategrey-bat-805937.hostingersite.com)