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

SQLREPL

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.


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.


Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.