How to avoid Msg 9514, Level 16, State 1, Line 3 error for linked server?

Question

I have a query to compare data between my local SQL Server and linked SQL Server. My problem is that the table I compare contains XML column. I get:

Msg 9514, Level 16, State 1, Line 3 Xml data type is not supported in distributed queries. Remote object [srv].[DB].dbo.[table]' has xml column(s).

I don't know how to fix this, because I don't show this column inside the select statement. I though that I can CAST, but when the column is not in the select list what to cast?

I see that there is workaround with cast by I can not. So may be I miss something.

Any ideas?

My test code looks like:

SELECT 
TA2.ID FROM [DB].[dbo].[table1] TA2
  INNER JOIN [linkedsrv].[DB].[dbo].[table1] PA ON (TA2.ID = PA.ID)

I need to update PA:

--UPDATE ALL ON PA

UPDATE XSRV
SET 
  XSRV.[Description] = SRV.[Description], 
  XSRV.[Code] = SRV.[Code], 
  XSRV.[Active] = SRV.[Active], 
  XSRV.[Expired] = SRV.[Expired], 
  XSRV.[SType] = SRV.[SType], 
  XSRV.SupportParameters = CAST(SRV.SupportParameters AS VARCHAR(MAX))
FROM 
  [PANI_TEST].dbo.[Services] AS SRV 
  INNER JOIN (SELECT X.ID, X.Code, X.[Description], X.Code, X.Active, X.Expired, 
      X.SType, CAST(X.XmlParams AS VARCHAR(MAX)) AS XmlParams, X.CreatedDate, X.CreatedBy, 
      X.ModifiedDate, X.ModifiedBy FROM [PANI_TEST].[dbo].[Services] AS X) AS XSRV ON 
    (SRV.ID = XSRV.ID) 
WHERE
  (SRV.[Description]     <> XSRV.[Description] ) OR
  (SRV.[Code]            <> XSRV.[Code]        ) OR
  (SRV.[Active]          <> XSRV.[Active]      ) OR
  (SRV.[Expired]         <> XSRV.[Expired]     ) OR
  (SRV.[SType]           <> XSRV.[SType]       ) OR 
  (CAST(SRV.XmlParams AS VARCHAR(MAX)) <> XSRV.XmlParams);

Of course the code above is not working because I use derived table, which is not updatable. I don't figure out right now how to update. If I use simple CAST without derived table I still get error.

Answer 1

What is probably happening is that SQL Server wants to download the whole remote table to your tempdb before attempting to join to the local table, including the XML column.

Try something like this:

SELECT TA2.ID 
FROM [DB].[dbo].[table1] TA2
INNER JOIN (
    SELECT col1, col2, col3, CAST(xmlCol AS varchar(max)) AS xmlCol
    FROM [linkedsrv].[DB].[dbo].[table1] 
) AS PA 
    ON (TA2.ID = PA.ID)

Or like this:

SELECT TA2.ID 
FROM [DB].[dbo].[table1] TA2
INNER JOIN (
    SELECT * 
    FROM OPENQUERY(linkedsrv, '
        SELECT col1, col2, col3, CAST(xmlCol AS varchar(max)) AS xmlCol 
        FROM [DB].[dbo].[table1]
    ')
) AS PA 
    ON (TA2.ID = PA.ID)