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:

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

I need to update PA:


  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))
  [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) 
  (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.

1 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:

FROM [DB].[dbo].[table1] TA2
    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:

FROM [DB].[dbo].[table1] TA2
    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)
  • Ok, @spaghettidba, but how to update the filed on linked server? I have derived table, which is not updatable :(. Jul 26, 2016 at 13:49
  • @BogdanBogdanov what exactly are you trying to update? Jul 26, 2016 at 13:53
  • I need to make synchronization. So I first compare all columns except ID. Then I need to update all columns with difference. But I use derived table to make comparison (to be sure which fields to update). And it is not updatable, @spaghettidba. Jul 26, 2016 at 13:57
  • @BogdanBogdanov show us some code. It's impossible to answer without seeing the code. Post it as a new question. Jul 26, 2016 at 13:59
  • So, @spaghettidba, how I can use that query for insert or update, or I have to create stored procedure on the linked server to call it to make that operations? Jul 27, 2016 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.