Grant_csi


I am using Push Merge Replication. I am seeing some very disturbing behavior related to the xml datatype. An insert into a table with an xml value seems to consistently fail to replicate the xml value. The row is replicated, but the xml column is null. Also updates to an xml column do not always get replicated.

Is this a known problem I cannot stress how big a problem this is for my business.





Re: XML columns failing to Replicate

Grant_csi


Upon further investigation replication is not inserting null for the missing xml values. They are set to zero length strings. Also the behavior is not consistent. I have seen the xml values replicated correctly, but it is failing fairly consistently.

Really need some input here.







Re: XML columns failing to Replicate

Grant_csi

THIS IS A HUGE PROBLEM!

XML columns are being replicated as 0 length strings causing data corruption in both the publication and subscription databases!

Please respond.







Re: XML columns failing to Replicate

Hilary Cotter

I am trying to repro this. What are you updating the XML columns to Empty strings or are you giving them a value.





Re: XML columns failing to Replicate

Grant_csi

Our application never sets an xml column to a blank, sometimes null but never blank. In fact I was surprised that blank was a valid xml value. I have also tested by setting the columns through SQL statements, using values like '<xml/>'.
Thanks for looking at this.





Re: XML columns failing to Replicate

Grant_csi

I case it has some effect on this issue, I should let you know that the tables I am seeing this issue with are linked with a logical relationship filter (sp_addmergefilter @filter_type = 2). I have not done any work to reproduce this problem in other tables that do not have this type of filter relationship.





Re: XML columns failing to Replicate

Hilary Cotter

do these columns have xml schema's on them





Re: XML columns failing to Replicate

Grant_csi

No they do not.






Re: XML columns failing to Replicate

Hilary Cotter

Hi Grant - it works for me - here is a repro

--can you provide us with a table script, publication and subscription script as well as how you do the update.

Thanks
create database MergeXML
GO
create database MergeXMLSub
GO
sp_replicationdboption 'MergeXML','merge publish','true'
GO
use MergeXML
GO
create table MergeXMLTable
(PK int not null primary key,
Charcol char(20),
XMLCol XML)
GO
sp_addmergepublication 'MergeXML'
GO
sp_addpublication_snapshot 'MergeXML'
GO
sp_addmergearticle 'MergeXML','MergeXMLtable','MergeXMLtable'
GO
sp_addmergesubscription 'MergeXML', @@servername, 'MergeXMLSub'
GO
--start agent
insert into MergeXMLTable (PK, Charcol,XMLCol) values(1,'test insert','<root><node1>value1</node1></root>')
GO
select * From mergexmlsub.dbo.mergexmltable
GO
update MergeXMLTable set XMLCol='<root><node1>value2</node1></root>'
GO
--run agent
select * From mergexmlsub.dbo.mergexmltable
GO
--successful








Re: XML columns failing to Replicate

Grant_csi

I am using RMO to perform the sync.

Before I create a script please add the following lines to the end of your script...

delete from MergeXMLTable

GO
insert into MergeXMLTable (PK, Charcol,XMLCol) values(1,'test insert','<root><node1>value1</node1></root>')
GO
--run agent
select * From mergexmlsub.dbo.mergexmltable
GO
--should see XMLCol = '' here






Re: XML columns failing to Replicate

Hilary Cotter

Nope.

1 test insert <root><node1>value1</node1></root> A3838007-8995-4FF2-83D3-12062B3A4AEF

Can you send me your schema and the exact update statement you use





Re: XML columns failing to Replicate

Grant_csi

I tested your scirpt using my existing databases and publication. It was successful for me as well. My guess at this point is that it is related to the logical relationship filters. Here is one of the tables I am seeing the problem in. There is a logical relationship filter based on the foreign key constraint named CMElementExDetail_FK_CMElement. I will try to modify your script to reflect this issue.

CREATE TABLE CMElementExDetail

(

CMElement uniqueidentifier NOT NULL,

CONSTRAINT CMElementExDetail_FK_CMElement FOREIGN KEY (CMElement) REFERENCES CMElement (CMElement),-- ON DELETE CASCADE

CMElementExDetail uniqueidentifier NOT NULL ROWGUIDCOL

CONSTRAINT CMElementExDetail_UQ_CMElementExDetail UNIQUE NONCLUSTERED (CMElementExDetail),

CONSTRAINT CMElementExDetail_PK_CMPersonnelCMElementExDetail PRIMARY KEY CLUSTERED (CMElement, CMElementExDetail),

Data xml NOT NULL

)






Re: XML columns failing to Replicate

Hilary Cotter

I doubt it it related to the filter. I can't seem to repro your problem with this table either.

Can I see

1) how you update the xml

2) your publication script

3) the schema for the CMElement table,

Also what version of SQL 2005 are you running and what sp






Re: XML columns failing to Replicate

Hilary Cotter

I doubt it is related to the filter. I can't seem to repro your problem with this table either.

Can I see

1) how you update the xml

2) your publication script

3) the schema for the CMElement table,

Also what version of SQL 2005 are you running and what sp






Re: XML columns failing to Replicate

Grant_csi

Thank you for all your help so far. The problem does definitely seem to be related to logical filters. Here is a script that reproduces the problem.

create database MergeXML

GO

create database MergeXMLSub

GO

sp_replicationdboption 'MergeXML','merge publish','true'

GO

use MergeXML

GO

CREATE TABLE XMLTest

(

XMLTest uniqueidentifier NOT NULL ROWGUIDCOL

CONSTRAINT XMLTest_PK_XMLTest PRIMARY KEY CLUSTERED (XMLTest),

Value varchar(10) NULL,

Data xml NULL

)

GO

CREATE TABLE XMLTestDetail

(

XMLTest uniqueidentifier NOT NULL,

CONSTRAINT XMLTestDetail_FK_XMLTest FOREIGN KEY (XMLTest) REFERENCES XMLTest (XMLTest),

XMLTestDetail uniqueidentifier NOT NULL ROWGUIDCOL

CONSTRAINT XMLTestDetail_UQ_XMLTestDetail UNIQUE NONCLUSTERED (XMLTestDetail),

CONSTRAINT XMLTestDetail_PK_XMLTestDetail PRIMARY KEY CLUSTERED (XMLTest, XMLTestDetail),

Data xml NOT NULL

)

GO

sp_addmergepublication 'MergeXML'

GO

sp_addpublication_snapshot 'MergeXML'

GO

sp_addmergearticle 'MergeXML','XMLTest','XMLTest'

GO

sp_addmergearticle 'MergeXML','XMLTestDetail','XMLTestDetail'

GO

sp_addmergefilter

@publication = 'MergeXML',

@article = 'XMLTestDetail',

@filtername = 'XMLTestDetail_FK_XMLTest',

@filter_type = 2,

@join_articlename = 'XMLTest',

@join_filterclause = 'XMLTestDetail.XMLTest = XMLTest.XMLTest',

@join_unique_key = 1,

@force_invalidate_snapshot = 1,

@force_reinit_subscription = 1

GO

sp_addmergesubscription 'MergeXML', @@servername, 'MergeXMLSub'

GO

--start agent

insert into XMLTest (XMLTest, Value, Data) select newid(), 'value1', '<root><node1>value1</node1></root>'

insert into XMLTestDetail (XMLTest, XMLTestDetail, Data) select XMLTest, newid(), Data from XMLTest

GO

select * from XMLTest

select * from XMLTestDetail

GO

select * from mergexmlsub.dbo.XMLTest

select * from mergexmlsub.dbo.XMLTestDetail

GO

--run agent

select * from XMLTest

select * from XMLTestDetail

GO

select * from mergexmlsub.dbo.XMLTest

select * from mergexmlsub.dbo.XMLTestDetail

GO

--successful

delete from XMLTestDetail

insert into XMLTestDetail (XMLTest, XMLTestDetail, Data) select XMLTest, newid(), Data from XMLTest

GO

--run agent

select * from XMLTest

select * from XMLTestDetail

GO

select * from mergexmlsub.dbo.XMLTest

select * from mergexmlsub.dbo.XMLTestDetail

GO

--should see XMLTestDetail.Data = '' here