Tag Archive for: XML

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]
    (
      [XMLReplTestID] [int] IDENTITY(1, 1) NOT FOR REPLICATION
                            NOT NULL ,
      [SomeXML]  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 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:

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.