June 10, 2009 at 7:24 pm
Hello friends,
I have a problem that i would like to get some advice on. I been replicating a db for a while and after it failed we switched to one of the subscribers for a few hours. What happened next was kind of disturbing, i noticed that none of the default values, triggers or non-clustered index's where created on the subscriber(they where there originally). I later found out that the publication re-built the tables without the above mentioned attributes(by default).
To cut a long story short, i've included the attributes into the publications, but it seems whenever i try to include the triggers, it falls over saying
Error executing a batch of commands. Retrying individual commands.
The row was not found at the Subscriber when applying the replicated command.
Now the trigger is hardly what you would call complex
declare @MaxID int
select @MaxID = Max(TableID) + 1
from [tableA]
update [tableA]
set TableID = @MaxID
where TableID = 0
I was wondering if anyone else has come across this before and could suggest a solution
Cheers 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
June 10, 2009 at 7:34 pm
Can you post the entire trigger script, exactly as it comes out of the scripter?
Thanks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 10, 2009 at 10:41 pm
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_TableAIdentity]
ON [dbo].[TableA]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @MaxID int
select @MaxID = Max(TableAID) + 1
from [TableA]
update [TableA]
set TableAID = @MaxID
where TableAID = 0
END
Cheers
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
June 11, 2009 at 12:57 am
Well, I can't see anything wrong...
I suggest setting up profiler on the subscriber and then re-build it again and capture what commands are actually being executed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 11, 2009 at 6:54 am
Sounds like one of the tables being refrenced in the trigger hasn't been created yet. The thing I've noticed with the publishing of articles is that you cant always tell which articles are going to be applied to the subscriber first.
There is a processing_order parameter that you can use when adding the article to the publication but I've never had luck getting it to work. I had to break up the publication in several phases to control which articles are getting created on the subscriber.
June 11, 2009 at 5:39 pm
That's interesting to hear, the trigger is updating the same table that its attached too. So i'm not sure that is the problem, unless it builds the trigger first and then the table??
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
June 11, 2009 at 6:09 pm
Can you suggest a template for the profiler or which events would be most useful to capture.
Cheers
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
June 12, 2009 at 6:36 am
Sorry I thought I saw two tables in your script one secound look I see only. Our issue was with triggers posting to multiple tables. The trigger wouldnt get created with out the source table its attached to exisiting first.
I didnt use a trace to see our what the problems was. I watched the status of objects being applied through our application that was performing the sync through .Net and RMO.
I would think a Standard Template should catch the order of the events firing. Another approach would be to enable verbose logging in the subscription agent.
June 12, 2009 at 7:11 am
Yes, I think the Standard template with the three Object events (created, altered, deleted?) added in should do the trick.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 15, 2009 at 12:59 am
Thanks gents for the advice. After a bit of digging it seems that its is creating the trigger on the subscription db but once its been created its then when the error comes up. I've created the publication minus the trigger and added it manually and i get the same problem.
So i guess that its checking if the TableAID = 0 and then tries to update it to = 1, but because its an subscription DB causes the replication to fail. But what puzzles me is that the publication DB has the same trigger and before the data is sent to the subscriber TableAID is set to 1 on insert so TableAID never gets to the subscriber as 0.
A bit convoluted, but it still leaves me with a subscription DB minus triggers.
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply