Replication conflict -- sql server replication

Ben
2/6/2007 4:38:01 AM

Hi Paul,

- There should not be a conflict. The row was modified at the Publisher but
not at the Subscriber.
- I don't think the trigger is causing the problem, according to the NOT FOR
REPLICATION statement, it is not supposed to happen. I just wanted to add
information that could aid in troubleshooting.
- The tm_row_id is unique and there are no duplicates.

Thanks for your help.

Ben

[quoted text, click to view]

Ben
2/6/2007 4:51:01 AM

Hi Hilary,

In this case, the same row was not modified in both ends. Will removing the
trigger stop this problem?

The data into this table is added by an end user at the Publisher. The
Publisher is the only one that has any interaction with this table when it
comes to inserting and updating.

Thanks for your help

Ben

[quoted text, click to view]

Ben
2/6/2007 5:27:01 AM

Hi Hilary,

They are both in the same city. The computer time could be slightly off.

Thanks

Ben

[quoted text, click to view]

Ben
2/6/2007 5:28:01 AM

Paul,

I will look to see what is different.

Thanks

Ben

[quoted text, click to view]

Hilary Cotter
2/6/2007 6:36:05 AM

First off I think your trigger is incorrect. It is only good for singleton
inserts and updates. Are you certain you only have singleton inserts and
updates? In other words do you only insert and update one row at a time?

I think the trigger should look like this

alter TRIGGER [dbo].[Last_mod_date] ON [dbo].[TitleMaster]
FOR INSERT, UPDATE NOT FOR REPLICATION
AS
UPDATE titlemaster SET
tm_last_mod_date = GETDATE(),
tm_last_mod_user =
REPLACE(SUSER_SNAME(),LEFT(SUSER_SNAME(),CHARINDEX('\',SUSER_SNAME())),'')
from titlemaster ,inserted
WHERE titlemaster.tm_row_id = inserted.tm_row_id

You might find this performs better, not to mention being more accurate:)

Secondly, it looks like this statement
REPLACE(SUSER_SNAME(),LEFT(SUSER_SNAME(),CHARINDEX('\',SUSER_SNAME())),''),
is removing the domain name, whereas this will perform better for you.

select case when charindex(SUSER_SNAME(),'\')>0 then
right(SUSER_SNAME(),CHARINDEX('\',SUSER_SNAME()))
else SUSER_SNAME() end

You need the case statement in there to handle sql authenticated users.

Thirdly there is the whole issue of using a guid column as a pk, consult
http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

Finally your replication problem. If the same row is updated on both sides
between sync's you will have a conflict. The way your trigger is written
this will always be the case, even with column level tracking. The not for
replication clause ensures that replication processes do not cause this
conflict.

How is the data updated/inserted? Is it from identical feeds?

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

Hilary Cotter
2/6/2007 8:03:25 AM

Actually I think the problem could be time zone related. Are both servers in
the same time zone?

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

Paul Ibison
2/6/2007 8:20:40 AM

Ben,
are you saying there shouldn't be a conflict at all ie the same row hasn't
been changed at publisher and subscriber? Also are you saying that the
trigger is causing this problem? I'm not too clear. Although I'm guessing
here because of not knowing the above issues, is the column "tm_row_id"
unique ie are there any duplicates of "tm_row_id"?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Paul Ibison
2/6/2007 1:08:28 PM

Ben - what I'd do is when it occurs, see what is the difference between the
rows ie what has changed on the subscriber which is unexpected? Then perhaps
use a log explorer tool to see what is causing the subscriber row to change
in this way.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Ben
2/12/2007 7:03:00 PM

Hi Hilary,

I made the change to the trigger, but I am still having conflicts. I had to
re-do the replication and while the publisher was creating the publication I
noticed the following in the log:

the foreign key 'dbo.Order.FK_Orders_Stores' will not be scripted for
article 'Orders' because it references the key contraint
'dbo.Stores.IX_st_row_id' that is replicated only as an index

I could not find any reference to this message, and I was wondering if you
knew or could point me in the right direction.

Thanks for your help!

Ben

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

sql server replication : Replication conflict