Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in


A blog about SQL Server Replication.

Replicating XML Columns

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.



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.




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


No comments.

Leave a Comment

Please register or log in to leave a comment.