Posted by Benzine on 01/05/07 00:31

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.com> wrote:
> Benzine wrote:
> > 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...
>
> > 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- Hide quoted text -- Show quoted text -

[Back to original message]