1

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.

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)
  • Ok, @spaghettidba, but how to update the filed on linked server? I have derived table, which is not updatable :(. – Bogdan Bogdanov Jul 26 '16 at 13:49
  • @BogdanBogdanov what exactly are you trying to update? – spaghettidba Jul 26 '16 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. – Bogdan Bogdanov Jul 26 '16 at 13:57
  • @BogdanBogdanov show us some code. It's impossible to answer without seeing the code. Post it as a new question. – spaghettidba Jul 26 '16 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? – Bogdan Bogdanov Jul 27 '16 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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