Merge replication does not working as expected.

Hi,
I am writing again because I've now confirmed that merge
replication does not work in my case. I'll try to
describe my case in a detailed way, as I need to find the
solution (fix) for this.
I have two servers. One main server A, and one subserver
B (there will be more of subservers).
Server B is receiving data from various applications in a
very irregular way. The data are then supposed to be
moved to server A (moved, not copied).
Server A is Publisher and its own Distributor. Server B
is Subscriber. Subscription is "pull" and "anonymous".
The merge replication with filtering is used. The filter
clause indicates a condition impossible. Thanks to this,
following scenario occurs:
- no rows are initiallyu copied from A server to B,
because A has no rows that fulfill the impossible
condition.
- when there are some rows added on B, when
synchronization occurs, the rows are copied from B to A.
Then all rows received on A are checked against the
impossible filter - because none of it satisfies that
condition, all are deleted on B server.
This works OK when the data are inserted on B "outside"
the replication phase.
But when you will insert the data to B when the
replication is in progress, the merge replication (and
subsequent replications) will fail to clear all the rows
on B, and as a result, table on B will stil have some
records from previous replications (all records are
copied, but not all are deleted). I consider this
behaviour as a bug in sql server replication, as I think
that it should be consistent in all situations.
The best way to reproduce this is to create a stored
procedure that inserts for example 10000 records to B,
and run this procedure few seconds before the start of
replication.
I hope that I gave you some light on the subject. I hope
that there are some MS guys related to replication, and
maybe one of them will be able to help me with this (you
can write directly if you need detailed information).
I'll appreciate any help.
Best regards,
Krzysztof Kruszynski
Paul,
Thanks for the link.
When I was reproducing the issue, I found that sometimes I was unable to disturb the first replication. But the seubsequent runs gave me allways some abandoned rows.
Regards,
Krzysztof
"Paul Ibison" wrote:

> Krzysztof,
> I'll try to repro this sometime later this week. Just as
> an aside, as you want to target your newsgroup comment to
> Microsoft, you can use this interface:
> http://communities2.microsoft.com/co.../newsgroups/en
> -us/default.aspx?
> dg=microsoft.public.sqlserver.replication&cat=en-us-
> servers-sqlserver&lang=en&cr=US
> This newsgroup webpage allows you to categorise/filter
> your queries.
> Regards,
> Paul Ibison
>
|||Krzysztof,
yes I can replicate your error. Subsequently doing a dummy
update on the subscriber still didn't ultimately remove
the row from the subscriber. The only way I could resolve
it was to reinitialize, which sounds drastic, but in this
case it merely readds the empty table but resets the
incorrect generation numbers. I'm not on sp3a on this
site, but if you can reproduce it on sp3a, I'd log this
with MS as a bug. Anyway, to resolve your issue, you could
resort to DTS - after all what you are doing is
essentially bypassing normal replication procedures, or
you could reinitialize frequently.
HTH,
Paul Ibison
|||Hi Paul

> I'm not on sp3a on this site, but if you can reproduce
> it on sp3a, I'd log this with MS as a bug.
I'll try to apply the sp3a and let you know about the results. But I don't know where to log it as a bug (or you will log it?).

> Anyway, to resolve your issue, you could
> resort to DTS - after all what you are doing is
> essentially bypassing normal replication procedures
Yep - I know. and I will probably use DTS or something else, not the replication.
Thanks for your help,
Krzysztof
|||You could post it on the feedback area
(http://register.microsoft.com/mswish/suggestion.asp).
Alternatively you could repost it here FAO Microsoft.
Alternatively a MVP (Hilary?) who sees this might have
some special powers to raise it directly with MS. Probably
just leaving it as it is will be sufficient as these
newsgroups are monitored by MS staff as a matter of course.
Regards,
Paul Ibison

Merge Replication and Trigger Problem

Hi,

I have an issue with my replication at the moment. I will try to
describe the scenario accurately.

I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on
one table which updates another, here is an example of the trigger:

"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
FOR INSERT, UPDATE, DELETE
AS
declare @.projTotal as money
declare @.projId as int
declare @.projcurrtype as int

select @.projId = project_id from inserted
select @.projcurrtype = proj_curr_type from qt_projects where project_id
= @.projId

--Get project total from the sum of table [qt_quotes]
select @.projTotal = (select
sum(dbo.fConvertCurrency(quot_grnd_totl,quot_curr_ type,@.projcurrtype))
as quoteTotal from qt_quotes where project_id = @.projId)

--Update projects record with new project total
update qt_projects
set proj_act_totl = @.projTotal
where project_id = @.projId"

I feel my trigger maybe setup incorrectly in that replication thinks an
insert is occurring instead of an update. (Im quite new to triggers)
What is happening is a conflict is occuring with the following message:

"The row was inserted at Server.Publisher' but could not be inserted at
'Subscriber.database'. INSERT statement conflicted with COLUMN FOREIGN
KEY constraint 'FK_qt_quotes_qt_projects'. The conflict occurred in
database 'Publisher', table 'qt_projects', column 'project_id'."

What is also happening as a result of this conflict (I think) is the
record in question is getting deleted from the Publisher. This is
causing huge problems as it is proving quite difficult to get these
records back in the system due to identity values.

Can anyone guide me to what might be happeing here, is it the trigger?Benzine wrote:

Quote:

Originally Posted by

Hi,
>
I have an issue with my replication at the moment. I will try to
describe the scenario accurately.
>
I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on
one table which updates another, here is an example of the trigger:
>
"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
FOR INSERT, UPDATE, DELETE
AS
declare @.projTotal as money
declare @.projId as int
declare @.projcurrtype as int
>
select @.projId = project_id from inserted
select @.projcurrtype = proj_curr_type from qt_projects where project_id
= @.projId
>
--Get project total from the sum of table [qt_quotes]
select @.projTotal = (select
sum(dbo.fConvertCurrency(quot_grnd_totl,quot_curr_ type,@.projcurrtype))
as quoteTotal from qt_quotes where project_id = @.projId)
>
--Update projects record with new project total
update qt_projects
set proj_act_totl = @.projTotal
where project_id = @.projId"
>

First thing to notice here is that your trigger is going to have issues
with any multi-row insert/update/delete statements. You need to get
that fixed.

However, I don't think that's your problem...

Quote:

Originally Posted by

I feel my trigger maybe setup incorrectly in that replication thinks an
insert is occurring instead of an update. (Im quite new to triggers)
What is happening is a conflict is occuring with the following message:
>
"The row was inserted at Server.Publisher' but could not be inserted at
'Subscriber.database'. INSERT statement conflicted with COLUMN FOREIGN
KEY constraint 'FK_qt_quotes_qt_projects'. The conflict occurred in
database 'Publisher', table 'qt_projects', column 'project_id'."
>
What is also happening as a result of this conflict (I think) is the
record in question is getting deleted from the Publisher. This is
causing huge problems as it is proving quite difficult to get these
records back in the system due to identity values.
>
Can anyone guide me to what might be happeing here, is it the trigger?

Merge replication is funny. So far as I can work out, in 2000, you
cannot force the merges to happen in a particular order. So it's
possible for it to merge data in the referencing table before it merges
data in the referenced table, for a particular foreign key.

In our systems, we've marked all of the foreign keys as "not for
replication", which has eliminated these kinds of errors for us. I'm
not sure what your options are if you cannot cope with "orphan" rows
appearing for brief moments of time.

On a side note (not applicable to OP), in 2005 you can specify the
order in which articles are processed. But I can't see how that can be
useful to anyone, since, in general, you would want to process inserts
in one order (for foreign keys to always work), and deletes in the
opposite order, surely?

Damien|||Thanks for your reply Damien,

Is unchecking "Enforce relationships for replication" the same as
marking FK "Not for Replication"

Damien wrote:

Quote:

Originally Posted by

Benzine wrote:

Quote:

Originally Posted by

Hi,

I have an issue with my replication at the moment. I will try to
describe the scenario accurately.

I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on
one table which updates another, here is an example of the trigger:

"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
FOR INSERT, UPDATE, DELETE
AS
declare @.projTotal as money
declare @.projId as int
declare @.projcurrtype as int

select @.projId = project_id from inserted
select @.projcurrtype = proj_curr_type from qt_projects where project_id
= @.projId

--Get project total from the sum of table [qt_quotes]
select @.projTotal = (select
sum(dbo.fConvertCurrency(quot_grnd_totl,quot_curr_ type,@.projcurrtype))
as quoteTotal from qt_quotes where project_id = @.projId)

--Update projects record with new project total
update qt_projects
set proj_act_totl = @.projTotal
where project_id = @.projId"

First thing to notice here is that your trigger is going to have issues
with any multi-row insert/update/delete statements. You need to get
that fixed.
>
However, I don't think that's your problem...
>

Quote:

Originally Posted by

I feel my trigger maybe setup incorrectly in that replication thinks an
insert is occurring instead of an update. (Im quite new to triggers)
What is happening is a conflict is occuring with the following message:

"The row was inserted at Server.Publisher' but could not be inserted at
'Subscriber.database'. INSERT statement conflicted with COLUMN FOREIGN
KEY constraint 'FK_qt_quotes_qt_projects'. The conflict occurred in
database 'Publisher', table 'qt_projects', column 'project_id'."

What is also happening as a result of this conflict (I think) is the
record in question is getting deleted from the Publisher. This is
causing huge problems as it is proving quite difficult to get these
records back in the system due to identity values.

Can anyone guide me to what might be happeing here, is it the trigger?

>
Merge replication is funny. So far as I can work out, in 2000, you
cannot force the merges to happen in a particular order. So it's
possible for it to merge data in the referencing table before it merges
data in the referenced table, for a particular foreign key.
>
In our systems, we've marked all of the foreign keys as "not for
replication", which has eliminated these kinds of errors for us. I'm
not sure what your options are if you cannot cope with "orphan" rows
appearing for brief moments of time.
>
On a side note (not applicable to OP), in 2005 you can specify the
order in which articles are processed. But I can't see how that can be
useful to anyone, since, in general, you would want to process inserts
in one order (for foreign keys to always work), and deletes in the
opposite order, surely?
>
Damien

|||Benzine wrote:

Quote:

Originally Posted by

Thanks for your reply Damien,
>
Is unchecking "Enforce relationships for replication" the same as
marking FK "Not for Replication"
>

Um, yes, I believe so. (Being a poncy type, I tend to do all this kind
of work through writing SQL rather than using Enterprise Manager, but
it looks like it's the sensible choice).

Damien|||Damien wrote:

Quote:

Originally Posted by

On a side note (not applicable to OP), in 2005 you can specify the
order in which articles are processed. But I can't see how that can be
useful to anyone, since, in general, you would want to process inserts
in one order (for foreign keys to always work), and deletes in the
opposite order, surely?

I suppose it could work if (1) the order is reversed for deletes, either
automatically or by request, or (2) cascade-delete triggers are used.|||Thanks allot for your help.
I will make the changes and issue a new snapshot then cross my fingers.

On Jan 5, 1:02 am, "Damien" <Damien_The_Unbelie...@.hotmail.comwrote:

Quote:

Originally Posted by

Benzine wrote:

Quote:

Originally Posted by

Thanks for your reply Damien,

>

Quote:

Originally Posted by

Is unchecking "Enforce relationships for replication" the same as
marking FK "Not for Replication"Um, yes, I believe so. (Being a poncy type, I tend to do all this kind

of work through writing SQL rather than using Enterprise Manager, but
it looks like it's the sensible choice).
>
Damien

|||Hi Damien,

You wouldnt by chance have a script that can update the
status_for_replication to Not_For_Replication for all FK's?

Ben

On Jan 4, 7:37 pm, "Damien" <Damien_The_Unbelie...@.hotmail.comwrote:

Quote:

Originally Posted by

Benzine wrote:

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I have an issue with my replication at the moment. I will try to
describe the scenario accurately.

>

Quote:

Originally Posted by

I am using MS SQL 2000 SP4 with Merge Replication. Subscribers connect
to the publisher to upload/download changes. I have a trigger set up on
one table which updates another, here is an example of the trigger:

>

Quote:

Originally Posted by

"CREATE TRIGGER qt_t_projTotal ON dbo.qt_quotes
FOR INSERT, UPDATE, DELETE
AS
declare @.projTotal as money
declare @.projId as int
declare @.projcurrtype as int

>

Quote:

Originally Posted by

select @.projId = project_id from inserted
select @.projcurrtype = proj_curr_type from qt_projects where project_id
= @.projId

>

Quote:

Originally Posted by

--Get project total from the sum of table [qt_quotes]
select @.projTotal = (select
sum(dbo.fConvertCurrency(quot_grnd_totl,quot_curr_ type,@.projcurrtype))
as quoteTotal from qt_quotes where project_id = @.projId)

>

Quote:

Originally Posted by

--Update projects record with new project total
update qt_projects
set proj_act_totl = @.projTotal
where project_id = @.projId"First thing to notice here is that your trigger is going to have issues

with any multi-row insert/update/delete statements. You need to get
that fixed.
>
However, I don't think that's your problem...
>

Quote:

Originally Posted by

I feel my trigger maybe setup incorrectly in that replication thinks an
insert is occurring instead of an update. (Im quite new to triggers)
What is happening is a conflict is occuring with the following message:

>

Quote:

Originally Posted by

"The row was inserted at Server.Publisher' but could not be inserted at
'Subscriber.database'. INSERT statement conflicted with COLUMN FOREIGN
KEY constraint 'FK_qt_quotes_qt_projects'. The conflict occurred in
database 'Publisher', table 'qt_projects', column 'project_id'."

>

Quote:

Originally Posted by

What is also happening as a result of this conflict (I think) is the
record in question is getting deleted from the Publisher. This is
causing huge problems as it is proving quite difficult to get these
records back in the system due to identity values.

>

Quote:

Originally Posted by

Can anyone guide me to what might be happeing here, is it the trigger?Merge replication is funny. So far as I can work out, in 2000, you

cannot force the merges to happen in a particular order. So it's
possible for it to merge data in the referencing table before it merges
data in the referenced table, for a particular foreign key.
>
In our systems, we've marked all of the foreign keys as "not for
replication", which has eliminated these kinds of errors for us. I'm
not sure what your options are if you cannot cope with "orphan" rows
appearing for brief moments of time.
>
On a side note (not applicable to OP), in 2005 you can specify the
order in which articles are processed. But I can't see how that can be
useful to anyone, since, in general, you would want to process inserts
in one order (for foreign keys to always work), and deletes in the
opposite order, surely?
>
Damien- Hide quoted text -- Show quoted text -

|||Benzine wrote:

Quote:

Originally Posted by

Hi Damien,
>
You wouldnt by chance have a script that can update the
status_for_replication to Not_For_Replication for all FK's?
>
Ben
>

I'm afraid not. I believe that the first time I encountered this
problem for a project, what I ended up doing was scripting out all of
the foreign keys to a script file. Then using an editor with support
for regular expressions in find and replace (in my case, Visual
Studio), I edited the file to include the "NOT FOR REPLICATION" at the
appropriate places in the file (see Books On Line for ALTER TABLE to
find the right syntax). Then I wrote another script which just tore
down all existing foreign keys in the database. Applying these scripts
in the correct order produced the necessary changes.

On the second project, I had it included from the start, so I've never
had to do this again.

Damien|||Thanks again.

Damien wrote:

Quote:

Originally Posted by

Benzine wrote:
>

Quote:

Originally Posted by

Hi Damien,

You wouldnt by chance have a script that can update the
status_for_replication to Not_For_Replication for all FK's?

Ben

I'm afraid not. I believe that the first time I encountered this
problem for a project, what I ended up doing was scripting out all of
the foreign keys to a script file. Then using an editor with support
for regular expressions in find and replace (in my case, Visual
Studio), I edited the file to include the "NOT FOR REPLICATION" at the
appropriate places in the file (see Books On Line for ALTER TABLE to
find the right syntax). Then I wrote another script which just tore
down all existing foreign keys in the database. Applying these scripts
in the correct order produced the necessary changes.
>
On the second project, I had it included from the start, so I've never
had to do this again.
>
Damien