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.
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.