http://www.sqlservercentral.com/blogs/sqlrepl/2011/05/20/replicating-xml-columns/

Printed 2014/10/25 05:31AM

Replicating XML Columns

By Brandon J Williams, 2011/05/20

One caveat that’s worth mentioning is replicating the XML data type in SQL Server using Merge Replication.  Sometimes, when replicating a table that contains a column of type XML via a Merge Publication, the XML column data in updated rows sent from Publisher to Subscriber, and vice-versa, arrive at the destination as empty, leading to non-convergence.


If you're experiencing this behavior, verify you have the latest Service Pack applied.  If the problem continues to persist, execute sp_helpmergearticle on the Publisher to retrieve information about the article in question and examine the value of the article property stream_blob_columns.


sp_helpmergearticle

sp_helpmergearticle_result

If the value of stream_blob_columns is set to true then this is the culprit.  When stream_blob_columns is set to true, SQL Server uses data stream optimization when replicating LOBs.  Somewhere in this optimization process things go south leading to non-convergence.  To workaround this, execute sp_changemergearticle, set stream_blob_columns to false, and synchronize your subscriptions.

 

sp_changemergearticle

 

After setting stream_blob_columns to false, articles containing XML columns should converge correctly in subsequent synchronizations.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.