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:
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.
About Kendal
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
2 comments
Wouldn't using a typed XML column (attaching it to a schema collection) keep this from happening? I'm not saying you could do that in your case, but that could be a work around if you could.
Interesting.
You got my vote.
Post a Comment