Hi Paul,

Thank you for your update! In my opinion, as a workaround, you may manually
run UPDATE statement to solve the conflict instead of using the conflict
viewer. For information about UPDATE, please refer to SQL Books
Online\Transact-SQL Reference\UPDATE.

However, I would like to continue researching this issue to see if there is
other solution, to expedite the process, coule you please post the
following information?

- the table schema
- the trigger
- the log table's schema

Thanks,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Paul de Goede" <paul@nospam.kineticmds.com>
| Sender: "Paul de Goede" <paul@nospam.kineticmds.com>
| References: <146301c35cc7$731c55f0$3501280a@phx.gbl>
<hAEcT2YXDHA.1856@cpmsftngxa06.phx.gbl>
<074201c35d93$11843ac0$a601280a@phx.gbl>
<zo9AOQAYDHA.2000@cpmsftngxa06.phx.gbl>
| Subject: RE: conflict resolution
| Date: Tue, 12 Aug 2003 04:21:10 -0700
| Lines: 338
| Message-ID: <0c8601c360c3$d4d23120$a401280a@phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNgw9TPOM9pvkNuSjuMcjxBOXkWLQ==
| Newsgroups: microsoft.public.sqlserver.replication
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.replication:42478
| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| Hi William,
|
| Sorry if I haven't been clear. Let me illustrate with an
| example.
|
| We have sql2ksp3 publisher and distributor on same server.
| It serves up publication PubTest
| Publication PubTest has 1 article in it: Article1.
| Article1 has automatic identity range management on it.
|
| Subsriber Sub1 is a SQL CE 2.0 subscription (anonymous).
| It connects and get's a snapshot. It then does a local
| insert into Article1. It inserts a row at ID 1001. It then
| executes a merge at the publisher. Pushlisher has range 1-
| 1000, subscribers get a 1000 long (wide? big? sized?)
| range.
|
| In the process of this merge there is a trigger on
| Article1 which fails. (in this case there was an
| insert/update/delete trigger on Article1 which created a
| copy of the old values and inserted them into an Audit
| table for audit trail purposes however the merge agent
| didn't have sufficient rights to insert into the audit
| table thus the trigger failed to execute). So the insert
| into the publisher Article1 table fails. This causes a row
| to be inserted at conflict_PubTest_Article1.
|
| I then change the rights of the identity the merge agent
| runs under to be able to insert into the audit table. I
| now want to push those conflicts through. I go to the
| interactive conflict resolver. I choose the looser and say
| resolve with these values. It fails. The error message is
| that the publisher identity range is full (it is not full).
| Obviously the problem is that the publisher id constraints
| (marked as not replication) are forcing the conflict
| resolver to fail the insert. However I would expect the
| interactive conflict resolver to be able to bypass these
| constraints so I can resolve this conflict.
|
| It seems like a bug in the resolver to me.
|
| Paul
| >-----Original Message-----
| >Hi Paul,
| >
| >I am still not sure the real situation on your side.
| Could you please post
| >the exact reproduce steps so that we could perform
| further research?
| >
| >Sincerely,
| >
| >William Wang
| >Microsoft Partner Online Support
| >
| >Get Secure! - www.microsoft.com/security
| >=====================================================
| >When responding to posts, please "Reply to Group" via
| >your newsreader so that others may learn and benefit
| >from your issue.
| >=====================================================
| >
| >This posting is provided "AS IS" with no warranties, and
| confers no rights.
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "Paul de Goede" <paul@nospam.kineticmds.com>
| >| Sender: "Paul de Goede" <paul@nospam.kineticmds.com>
| >| References: <146301c35cc7$731c55f0$3501280a@phx.gbl>
| ><hAEcT2YXDHA.1856@cpmsftngxa06.phx.gbl>
| >| Subject: RE: conflict resolution
| >| Date: Fri, 8 Aug 2003 02:54:33 -0700
| >| Lines: 216
| >| Message-ID: <074201c35d93$11843ac0$a601280a@phx.gbl>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| >| Thread-Index: AcNdkxGEDaPoYdJjQ2uzDAhOdIU1GQ==
| >| Newsgroups: microsoft.public.sqlserver.replication
| >| Path: cpmsftngxa06.phx.gbl
| >| Xref: cpmsftngxa06.phx.gbl
| microsoft.public.sqlserver.replication:42377
| >| NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
| >| X-Tomcat-NG: microsoft.public.sqlserver.replication
| >|
| >| Hi William,
| >|
| >| Thanks for the info however that's all pretty general
| >| stuff and not really about my specific problem. I have
| >| auto identity ranges set with replicating to sql ce 2.0.
| >|
| >| My problem is that conflicts have occured due to
| triggers
| >| on the published articles having a problem - thus when
| the
| >| sql ce 2.0 subscribers merged the row's failed to
| insert
| >| and were put into the conflict tables. I can now push
| that
| >| data through without any changes (I have fixed the
| >| triggers on the articles). But I need to know how to re-
| >| insert that data.
| >|
| >| The conflicting rows have been removed from the sql ce
| 2.0
| >| subscribers so when they sync it doesn't insert that
| data.
| >| And I can't resolve them via the interactive conflict
| >| resolver since it gives me an error inserting into the
| >| publisher range. Obviously it was a row created on the
| >| subscriber so it violates the identity range management
| >| constraints on that article at the publisher.
| >|
| >| My question is how can I now re-insert those conflicts
| >| into the publisher? Doesn't the interactive resolver
| >| pretend to be a merge process thereby bypassing the
| >| identity range constraints on those published articles?
| >|
| >|
| >| My only avenue now is to remove the identity range

no response from customer.

Hi William,

We did write some sql to manually move the stuff over from
the conflict table to the article after disabling the
constraint for id ranges on that article.

We had this on quite a few tables - here is the table
schema, conflict table schema and audit trigger from one
of the simpler ones.
The failure occured because the replication user didn't
have sufficient rights to insert rows into Audit_Action
table. All of this I understand after some investigation
into the MS_merge_XXXXX role etc. I just would have
thought that the conflict resolution could have bypassed
the publisher id constraint when resolving conflicts.

thanks.

CREATE TABLE [Action] (
[iID] [int] IDENTITY (1, 1) NOT FOR REPLICATION
NOT NULL ,
[cTitle] [nvarchar] (50) COLLATE
Latin1_General_CI_AI NOT NULL ,
[cDetail] [nvarchar] (200) COLLATE
Latin1_General_CI_AI NULL ,
[iCreator] [int] NOT NULL ,
[dCreateDate] [datetime] NOT NULL ,
[dRemindDate] [datetime] NULL ,
[dActionDate] [datetime] NULL ,
[iPriority] [int] NULL ,
[iAssignee] [int] NOT NULL ,
[iActionObjectID] [int] NOT NULL ,
[iActionObjectType] [int] NOT NULL ,
[iState] [int] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
CONSTRAINT [DF__Action__rowguid__0C4DD891] DEFAULT (newid
()),
CONSTRAINT [PK_Action] PRIMARY KEY CLUSTERED
(
[iID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Action_Action_Priority] FOREIGN KEY
(
[iPriority]
) REFERENCES [ActionPriority] (
[iID]
),
CONSTRAINT [FK_Action_Assignee_Employee] FOREIGN
KEY
(
[iAssignee]
) REFERENCES [Employee] (
[iID]
),
CONSTRAINT [FK_Action_BusinessObject] FOREIGN KEY
(
[iActionObjectType]
) REFERENCES [BusinessObject] (
[iID]
),
CONSTRAINT [FK_Action_Creator_Employee] FOREIGN
KEY
(
[iCreator]
) REFERENCES [Employee] (
[iID]
),
CONSTRAINT
[repl_identity_range_pub_D6537857_4A0F_4435_A6D8_C10DFA6DA1
2D] CHECK NOT FOR REPLICATION ([iID] > 4500 and [iID] <
5500)
) ON [PRIMARY]
GO



CREATE TABLE [conflict_KMDSHeinekenTMDSP6_Action] (
[iID] [int] NOT NULL ,
[cTitle] [nvarchar] (50) COLLATE
Latin1_General_CI_AI NOT NULL ,
[cDetail] [nvarchar] (200) COLLATE
Latin1_General_CI_AI NULL ,
[iCreator] [int] NOT NULL ,
[dCreateDate] [datetime] NOT NULL ,
[dRemindDate] [datetime] NULL ,
[dActionDate] [datetime] NULL ,
[iPriority] [int] NULL ,
[iAssignee] [int] NOT NULL ,
[iActionObjectID] [int] NOT NULL ,
[iActionObjectType] [int] NOT NULL ,
[iState] [int] NOT NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
[origin_datasource] [nvarchar] (255) COLLATE
Latin1_General_CI_AI NULL ,
[conflict_type] [int] NULL ,
[reason_code] [int] NULL ,
[reason_text] [nvarchar] (720) COLLATE
Latin1_General_CI_AI NULL ,
[pubid] [uniqueidentifier] NULL ,
[MSrepl_create_time] [datetime] NULL CONSTRAINT
[DF__conflict___MSrep__740D0603] DEFAULT (getdate())
) ON [PRIMARY]
GO



CREATE TRIGGER TR_Audit_Action
ON Action
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @columnchanges varbinary(100)
DECLARE @columnlist nvarchar(1000)
DECLARE @columninsertedlist nvarchar(1000)
DECLARE @audittype int
DECLARE @deletecount int
DECLARE @insertcount int
DECLARE @personid int
IF (ISNUMERIC(HOST_NAME()) = 1)
BEGIN
SET @personid = CONVERT(int,HOST_NAME())
END
ELSE
BEGIN
SET @personid = null
END

SELECT @insertcount = COUNT(1) FROM inserted
SELECT @deletecount = COUNT(1) FROM deleted

IF @insertcount = 0 SET @audittype = 2 /* delete */
ELSE
BEGIN
IF @deletecount = 0 SET @audittype = 0 /* insert */
ELSE SET @audittype = 1 /* update */
END

SET @columnchanges = COLUMNS_UPDATED()

EXEC sp_ColumnlistFromVarbinary @tablename = 'Action',
@columnbits = @columnchanges,
@alwaysreturnidandrowguidcolumns = 1, @pretext = '',
@imagepretext = '', @@columnlist = @columnlist OUTPUT

DECLARE @sqlstring nvarchar(2000)
SELECT
inserted.iID,inserted.cTitle,inserted.cDetail,inserted.iCre
ator,inserted.dCreateDate,inserted.dRemindDate,inserted.dAc
tionDate,inserted.iPriority,inserted.iAssignee,inserted.iAc
tionObjectID,inserted.iActionObjectType,inserted.iState,ins
erted.rowguid, GETUTCDATE() AS auditcolumn_audittime,
@personid AS auditcolumn_auditperson, @columnchanges AS
auditcolumn_auditcolumnschanged, @audittype AS
auditcolumn_auditaction, CURRENT_USER AS
auditcolumn_currentuser INTO #TR_Audit_ActionTempTable
FROM inserted INNER JOIN Action ON inserted.iID =
Action.iID
IF @insertcount > 0
BEGIN
SET @sqlstring = 'INSERT INTO Audit_Action(' +
@columnlist + ' , auditcolumn_audittime,
auditcolumn_auditperson, auditcolumn_auditcolumnschanged,
auditcolumn_auditaction, auditcolumn_currentuser) SELECT '
+ @columnlist + ', auditcolumn_audittime,
auditcolumn_auditperson, auditcolumn_auditcolumnschanged,
auditcolumn_auditaction, auditcolumn_currentuser FROM
#TR_Audit_ActionTempTable'
END
ELSE
BEGIN
IF @deletecount > 0
BEGIN
SELECT iID, GETUTCDATE() AS
auditcolumn_audittime, @personid AS
auditcolumn_auditperson, @columnchanges AS
auditcolumn_auditcolumnschanged, @audittype AS
auditcolumn_auditaction, CURRENT_USER AS
auditcolumn_currentuser INTO #TR_Audit_ActionTempTable2
FROM deleted
SET @sqlstring = 'INSERT INTO Audit_Action
(iID , auditcolumn_audittime, auditcolumn_auditperson,
auditcolumn_auditcolumnschanged, auditcolumn_auditaction,
auditcolumn_currentuser) SELECT iID,
auditcolumn_audittime, auditcolumn_auditperson,
auditcolumn_auditcolumnschanged, auditcolumn_auditaction,
auditcolumn_currentuser FROM #TR_Audit_ActionTempTable2'
END
ELSE
BEGIN
SET @sqlstring = null
END
END
EXEC(@sqlstring)
DROP TABLE #TR_Audit_ActionTempTable
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[quoted text, click to view]