Replication conflict

Hi,
I am using SQL Server 2005 replication. In the Conflict Viewer I have the
following message for the conflict being reported.
The same row was updated at both
'[Publisher]'
and '[Subscriber]'.
The resolver chose the update from '[Publisher]' as the winner.
The table is not filtered and does have one Trigger:
ALTER TRIGGER [dbo].[Last_mod_date] ON [dbo].[TitleMaster]
FOR INSERT, UPDATE NOT FOR REPLICATION
AS
DECLARE @.chvLastModUser VARCHAR(20), @.dtmLastModDate DATETIME, @.uidID
UNIQUEIDENTIFIER
SELECT @.uidID = tm_row_id
FROM inserted
SELECT @.chvLastModUser =
REPLACE(SUSER_SNAME(),LEFT(SUSER_SNAME(),CHARINDEX ('\',SUSER_SNAME())),'')
SELECT @.dtmLastModDate = GETDATE()
UPDATE titlemaster SET
tm_last_mod_date = @.dtmLastModDate,
tm_last_mod_user = @.chvLastModUser
WHERE tm_row_id = @.uidID
This problem does not take place for all records.
Help will be greatly appreaciated.
Thanks!
Ben
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
|||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
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:A2702486-3172-4A02-84F0-36061BEB7944@.microsoft.com...
> Hi,
> I am using SQL Server 2005 replication. In the Conflict Viewer I have the
> following message for the conflict being reported.
> The same row was updated at both
> '[Publisher]'
> and '[Subscriber]'.
> The resolver chose the update from '[Publisher]' as the winner.
>
> The table is not filtered and does have one Trigger:
> ALTER TRIGGER [dbo].[Last_mod_date] ON [dbo].[TitleMaster]
> FOR INSERT, UPDATE NOT FOR REPLICATION
> AS
> DECLARE @.chvLastModUser VARCHAR(20), @.dtmLastModDate DATETIME, @.uidID
> UNIQUEIDENTIFIER
> SELECT @.uidID = tm_row_id
> FROM inserted
> SELECT @.chvLastModUser =
> REPLACE(SUSER_SNAME(),LEFT(SUSER_SNAME(),CHARINDEX ('\',SUSER_SNAME())),'')
> SELECT @.dtmLastModDate = GETDATE()
> UPDATE titlemaster SET
> tm_last_mod_date = @.dtmLastModDate,
> tm_last_mod_user = @.chvLastModUser
> WHERE tm_row_id = @.uidID
> This problem does not take place for all records.
> Help will be greatly appreaciated.
> Thanks!
> Ben
|||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
"Paul Ibison" wrote:

> 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
>
>
|||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
"Hilary Cotter" wrote:

> 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
>
> "Ben" <Ben@.discussions.microsoft.com> wrote in message
> news:A2702486-3172-4A02-84F0-36061BEB7944@.microsoft.com...
>
>
|||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
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:3674B984-520E-4FE2-8366-AEC079D3F410@.microsoft.com...[vbcol=seagreen]
> 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
>
> "Hilary Cotter" wrote:
|||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
|||Hi Hilary,
They are both in the same city. The computer time could be slightly off.
Thanks
Ben
"Hilary Cotter" wrote:

> 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
>
> "Ben" <Ben@.discussions.microsoft.com> wrote in message
> news:3674B984-520E-4FE2-8366-AEC079D3F410@.microsoft.com...
>
>
|||Paul,
I will look to see what is different.
Thanks
Ben
"Paul Ibison" wrote:

> 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
>
>
>
|||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
"Hilary Cotter" wrote:

> 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
>
> "Ben" <Ben@.discussions.microsoft.com> wrote in message
> news:3674B984-520E-4FE2-8366-AEC079D3F410@.microsoft.com...
>
>