http://www.sqlservercentral.com/blogs/kendalvandyke/2010/08/24/replication-gotcha_3A00_-blank-xml/

Printed 2014/07/27 11:04PM

Replication Gotcha: Blank XML

By Kendal Van Dyke, 2010/08/24

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]
   
(
     
[XMLReplTestID] [int] IDENTITY(1, 1) NOT FOR REPLICATION
                           
NOT NULL ,
     
[SomeXML] [xml] NOT NULL ,
     
CONSTRAINT [PK_XMLReplTest] PRIMARY KEY CLUSTERED
       
( [XMLReplTestID] ASC ) ON [PRIMARY]
   
)
ON  [PRIMARY]
GO

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

INSERT  INTO dbo.XMLReplTest
       
( 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'
GO
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
GO

-- 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]'
GO

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

ALTER TABLE dbo.XMLReplTest ADD CONSTRAINT
  
CK_XMLReplTest_SomeXML CHECK (DATALENGTH(SomeXML) > 5)
GO

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


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