SQL Server Replication Gotcha – Blank XML

SQL Server Replication Gotcha - Blank XMLOriginally published on

Here is another SQL Server Replication Gotcha – Blank XML.  Transactional replication in SQL Server 2005\2008 can handle the XML datatype just fine with few exceptions – one in particular being when the XML value is blank. I’ll save the argument about whether or not a blank (or empty string if you prefer) value is well formed XML for another day because the point is that SQL Server allows it. Consider the following table:

CREATE TABLE [dbo].[XMLReplTest]
                            NOT NULL ,
      [SomeXML]  NOT NULL ,
        ( [XMLReplTestID] ASC ) ON [PRIMARY]

Execute the following statement and you’ll see that SQL Server handles it just fine:

        ( SomeXML )
VALUES  ( '' )

Now let’s add this table to a transactional replication publication:

-- Adding the transactional publication
EXEC sp_addpublication @publication = N'XML Replication Test',
    @description = N'Sample publication to demonstrate blank XML gotcha',
    @sync_method = N'concurrent', @retention = 0, @allow_push = N'true',
    @allow_pull = N'true', @allow_anonymous = N'false',
    @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
    @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',
    @allow_subscription_copy = N'false', @add_to_active_directory = N'false',
    @repl_freq = N'continuous', @status = N'active',
    @independent_agent = N'true', @immediate_sync = N'false',
    @allow_sync_tran = N'false', @autogen_sync_procs = N'false',
    @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1,
    @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',
    @enabled_for_het_sub = N'false'
EXEC sp_addpublication_snapshot @publication = N'XML Replication Test',
    @frequency_type = 1, @frequency_interval = 0,
    @frequency_relative_interval = 0, @frequency_recurrence_factor = 0,
    @frequency_subday = 0, @frequency_subday_interval = 0,
    @active_start_time_of_day = 0, @active_end_time_of_day = 235959,
    @active_start_date = 0, @active_end_date = 0, @job_login = NULL,
    @job_password = NULL, @publisher_security_mode = 1

-- Adding the transactional articles
EXEC sp_addarticle @publication = N'XML Replication Test',
    @article = N'XMLReplTest', @source_owner = N'dbo',
    @source_object = N'XMLReplTest', @type = N'logbased', @description = N'',
    @creation_script = N'', @pre_creation_cmd = N'drop',
    @schema_option = 0x00000000080350DF,
    @identityrangemanagementoption = N'manual',
    @destination_table = N'XMLReplTest', @destination_owner = N'dbo',
    @status = 8, @vertical_partition = N'false',
    @ins_cmd = N'CALL [dbo].[sp_MSins_dboXMLReplTest]',
    @del_cmd = N'CALL [dbo].[sp_MSdel_dboXMLReplTest]',
    @upd_cmd = N'SCALL [dbo].[sp_MSupd_dboXMLReplTest]'

Assume we’ve created the publication, added a subscriber, taken & applied the snapshot, and we’re ready to start changing data. Let’s throw a monkey wrench into the works by executing the insert statement with the blank XML again and watch what happens to the log reader agent:

Log Reader Agent Error

That’s not a very nice error (or resolution)! I’ve been able to reproduce this behavior in SQL 2005 & 2008 but I have not tried it in 2008 R2. I’ve entered a Connect bug report so hopefully this is fixed in a forthcoming cumulative update. In the meantime there is a simple workaround – add a check constraint. Since we’re working with the SQL Server replication blank XML datatype the only option for checking length with a scalar function is DATALENGTH. The DATALENGTH for a blank xml value is 5 so we want to check that any inserted or updated value is greater than 5:


If you are affected by this behavior please consider taking a moment to go vote for it on Connect.


About the Author

Microsoft SQL Server MVP & Principal Consultant

Kendal Van Dyke

UpSearch Alum Kendal Van Dyke is a database strategist, community advocate, public speaker and blogger. He is passionate about helping leaders use Microsoft's SQL Server to solve complex problems that protect, unlock and optimize data's value.

Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting. Kendal excels at disaster recovery, high availability planning/implementation and debugging/troubleshooting mission critical SQL Server environments.

Kendal Van Dyke served the SQL Server community as Founder and President of MagicPass, the Orlando, FL based chapter of the Professional Association for SQL Server (PASS). In 2012, Kendal served as a member of the PASS Board of Directors.

Kendal remains active in the SQL Server community as a speaker and blogger. He teaches SQL Server enthusiast and technology leaders how to protect, unlock and optimize data’s value. Since 2008, Kendal has operated a SQL Server focused blog at

Microsoft acknowledged Kendal for his support and outstanding contributions to the SQL Server community by awarding him Microsoft MVP (2011-15). Learn more about Kendal Van Dyke

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.

<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>


About UpSearch


UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.