I have the following scenario,
1.merge replication with 3000 subscribers
2.I have dynamic publication (NOT dynamic snapshot)
3.we observed that synchronization is slow whenever we do a data import at
the publisher
4.because of this we ran the snapshot agent immediately after the data import
5.after the snapshot run, we find the subscriber syncs faster
I would like to know what are the implications of running snapshot agent.
Will it remove the delta changes which are pending for sync? Or is it okay if
I run snapshot as much as I like?
Expect slowness whenever you do a data import as there is always the impact
of the dataload and then there is the added data you have to merge.
Are you saying if you run the snapshot you get faster sync's? This only
makes sense if you re-initialize your subscribers after regenerating the
snapshot.
If you have anonymous subscribers the snapshot is always generated each time
you run it. If you have named it is only regenerated if subscribers expire
or require reinitialization.
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
"Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>I have the following scenario,
> 1.merge replication with 3000 subscribers
> 2.I have dynamic publication (NOT dynamic snapshot)
> 3.we observed that synchronization is slow whenever we do a data import at
> the publisher
> 4.because of this we ran the snapshot agent immediately after the data
> import
> 5.after the snapshot run, we find the subscriber syncs faster
>
> I would like to know what are the implications of running snapshot agent.
> Will it remove the delta changes which are pending for sync? Or is it okay
> if
> I run snapshot as much as I like?
>
>
|||Thank you Hilary for you time. I have some more clarifications,
1.I have sql server ce subscribers
2.Hence I need to use anonymous subscription
I have the following questions here,
a)Can I use pre-generated snapshot in my case? (Anonymous + sql ce
subscribers)
b)I also have dynamic filters on the publisher. What impact I will have by
re-running the snapshot second time, on the subscriber?
"Hilary Cotter" wrote:
> Expect slowness whenever you do a data import as there is always the impact
> of the dataload and then there is the added data you have to merge.
> Are you saying if you run the snapshot you get faster sync's? This only
> makes sense if you re-initialize your subscribers after regenerating the
> snapshot.
> If you have anonymous subscribers the snapshot is always generated each time
> you run it. If you have named it is only regenerated if subscribers expire
> or require reinitialization.
> --
> 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
>
> "Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
> news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>
>
Showing posts with label scenario. Show all posts
Showing posts with label scenario. Show all posts
Friday, March 30, 2012
Merge Replication: Insert Trigger ONLY when replicating
Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
Nest level check allows to avoid ping-pong traffic from subscriber to
publisher and vice versa
But in your case that should matter only if you are also replicating table
tblTest
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
|||Great. I'm not replicating this table. Thanks for the information.
"Kestutis Adomavicius" <kicker.lt@.noospaam_tut.by> wrote in message
news:eVz8BD8WFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Nest level check allows to avoid ping-pong traffic from subscriber to
> publisher and vice versa
> But in your case that should matter only if you are also replicating table
> tblTest
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Christian Hamel" <chamel@.NOSPAM.com> wrote in message
> news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>
|||I think it is to prevent recursive triggers from causing duplicate entries
in msmerge_contents. I could be wrong here.
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
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.Nest level check allows to avoid ping-pong traffic from subscriber to
publisher and vice versa
But in your case that should matter only if you are also replicating table
tblTest
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.|||Great. I'm not replicating this table. Thanks for the information.
"Kestutis Adomavicius" <kicker.lt@.noospaam_tut.by> wrote in message
news:eVz8BD8WFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Nest level check allows to avoid ping-pong traffic from subscriber to
> publisher and vice versa
> But in your case that should matter only if you are also replicating table
> tblTest
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Christian Hamel" <chamel@.NOSPAM.com> wrote in message
> news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
>
That would be greatly appreciated. Thanks.>
|||I think it is to prevent recursive triggers from causing duplicate entries
in msmerge_contents. I could be wrong here.
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
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
>
That would be greatly appreciated. Thanks.>
Wednesday, March 28, 2012
merge replication, later wins conflict resolver issue
I am testing a scenario with merge replication when publisher has an entry
1st, and the same data entered into the subscriber later (but before merge
agent runs).
I am using merge replication with 'Microsoft SQL Server DATETIME (Later
Wins) Conflict Resolver'.
I inserted the same row (row with same PK) into publisher, then into
subscriber.
I am expecting to see row from subscriber replicate to publisher at the next
time merge agent runs.
Instead the row from publisher shows up in my subscriber side.
The conflict resolver reports that the publisher won:
The row was inserted at 'EZROUTESUNDB01.repltest' but could not be inserted
at 'EZROUTESTGDB01.repltest'. Violation of PRIMARY KEY constraint 'P_test1'.
Cannot insert duplicate key in object 'test1'.
The conflict table has conflict type 5, "Upload Insert Failed".
I want the latest row from subscriber to win that is why I choose "Later
Wins" resolver.
BTW, when I update the same row on both sides, repl. works like a charm,
later change will won independent of which side had it originally (so
subscriber data migrates to publisher OK if newer).
Also, when new inserts are made in either side, those are merged correctly.
Am I misunderstanding something here?
Thanks,
Zoltan
did you specify the time data type column to use as a basis for the later
wins resolver? You enter this is the Enter Information Needed by the
Resolver text box.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rr news" <znyiri@.hotmail.com> wrote in message
news:HFXad.11969$yP2.4853@.tornado.tampabay.rr.com. ..
> I am testing a scenario with merge replication when publisher has an entry
> 1st, and the same data entered into the subscriber later (but before merge
> agent runs).
> I am using merge replication with 'Microsoft SQL Server DATETIME (Later
> Wins) Conflict Resolver'.
> I inserted the same row (row with same PK) into publisher, then into
> subscriber.
> I am expecting to see row from subscriber replicate to publisher at the
next
> time merge agent runs.
> Instead the row from publisher shows up in my subscriber side.
> The conflict resolver reports that the publisher won:
> The row was inserted at 'EZROUTESUNDB01.repltest' but could not be
inserted
> at 'EZROUTESTGDB01.repltest'. Violation of PRIMARY KEY constraint
'P_test1'.
> Cannot insert duplicate key in object 'test1'.
> The conflict table has conflict type 5, "Upload Insert Failed".
> I want the latest row from subscriber to win that is why I choose "Later
> Wins" resolver.
> BTW, when I update the same row on both sides, repl. works like a charm,
> later change will won independent of which side had it originally (so
> subscriber data migrates to publisher OK if newer).
> Also, when new inserts are made in either side, those are merged
correctly.
> Am I misunderstanding something here?
> Thanks,
> Zoltan
>
|||Hilary,
Thanks for you fast response.
Here are the table definition, the article info, and my test scenarios.
I am only having problem with scenario 3.
Thanks,
Zoltan
CREATE TABLE [test1] (
[num] [int] NOT NULL ,
[date] [datetime] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF__test1__rowguid__353DDB1D] DEFAULT (newid()),
[strcol] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [P_test1] PRIMARY KEY CLUSTERED
(
[num]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sp_helpmergearticle @.article='test1'
id name
source_owner source_object
sync_object_owner sync_object
description status
creation_script conflict_table
article_resolver
subset_filterclause
pre_creation_command schema_option type column_tracking resolver_info
vertical_partition destination_owner
identity_support pub_identity_range identity_range threshold
verify_resolver_signature destination_object
allow_interactive_resolver fast_multicol_updateproc check_permissions
-- ---- --
---- --
--- --
-- ---
-- ---- --
-- ---- --
---- --
-- ---
-- -- -- -- --
-- ---- --
-- ---- --
-- -- -- -- --
-- ----
-- -- -- --
1 test1 dbo
test1 dbo
test1 NULL
2 NULL
conflict_repltest_test1 Microsoft SQL
Server DATETIME (Later Wins) Conflict Resolver NULL
1 0x000000000000CFF1 10 1 date
1 dbo
0 NULL NULL NULL 0
test1 0
1 0
I have a datetime type column named "date" which is entered in the
resolver_info field for article 'test1'.
article_resolver is configured for 'Microsoft SQL Server DATETIME (Later
Wins) Conflict Resolver'
Test scenarios:
1) new (row w/diff PK) inserted into either side, each row replicated
(merged to other side), OK
2) same row updated with different data, then the latest entry wins no
matter if it was created on the publisher side or the subscriber side
3) the only problem I have is when same row (row w/same PK) entered into
both sides.
EZROUTESTGDB01 publisher
EZROUTESUNDB01 sunscriber
--> 2nd scenario, later wins resolver works fine.
-- testing update same row, 1st at subscriber, then publisher
-- insert test row
insert into EZROUTESTGDB01.repltest.dbo.test1 (num, date, strcol) values(
102, getdate(), 'XXX' )
-- wait until it synchs, so both sides have same row
update EZROUTESTGDB01.repltest.dbo.test1 set date=getdate(), strcol='publ'
where num=102
-- wait few seconds
update EZROUTESUNDB01.repltest.dbo.test1 set date=getdate(), strcol='subscr'
where num=102
-- wait until synch, both sides end up with 'subscr' in strcol column, OK
--> 3rd scenario, NOT OK when subscriber has the later entry, publisher
still wins...?!
--clean up, wait until synch
delete from repltest.dbo.test1 where num > 20
-- testing insert same PK into publisher 1st, then subscriber, still
publisher wins, NOT OK!
insert into EZROUTESTGDB01.repltest.dbo.test1(num, date, strcol) values(
101, getdate(),'publisher' )
-- wait few seconds
insert into EZROUTESUNDB01.repltest.dbo.test1(num, date, strcol) values(
101, getdate(),'subscriber' )
-- wait until synch, both sides end up with 'publisher' in strcol column,
NOT OK
Resolver reports:
The row was inserted at 'EZROUTESUNDB01.repltest' but could not be inserted
at 'EZROUTESTGDB01.repltest'. Violation of PRIMARY KEY constraint 'P_test1'.
Cannot insert duplicate key in object 'test1'.
BTW, clocks are synched, I checked it.
Merge agent is configured to run once/minute.
Microsoft SQL Server 2000 - 8.00.760 on both sides.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:em7nkYMsEHA.2144@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> did you specify the time data type column to use as a basis for the later
> wins resolver? You enter this is the Enter Information Needed by the
> Resolver text box.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "rr news" <znyiri@.hotmail.com> wrote in message
> news:HFXad.11969$yP2.4853@.tornado.tampabay.rr.com. ..
entry[vbcol=seagreen]
merge
> next
> inserted
> 'P_test1'.
> correctly.
>
1st, and the same data entered into the subscriber later (but before merge
agent runs).
I am using merge replication with 'Microsoft SQL Server DATETIME (Later
Wins) Conflict Resolver'.
I inserted the same row (row with same PK) into publisher, then into
subscriber.
I am expecting to see row from subscriber replicate to publisher at the next
time merge agent runs.
Instead the row from publisher shows up in my subscriber side.
The conflict resolver reports that the publisher won:
The row was inserted at 'EZROUTESUNDB01.repltest' but could not be inserted
at 'EZROUTESTGDB01.repltest'. Violation of PRIMARY KEY constraint 'P_test1'.
Cannot insert duplicate key in object 'test1'.
The conflict table has conflict type 5, "Upload Insert Failed".
I want the latest row from subscriber to win that is why I choose "Later
Wins" resolver.
BTW, when I update the same row on both sides, repl. works like a charm,
later change will won independent of which side had it originally (so
subscriber data migrates to publisher OK if newer).
Also, when new inserts are made in either side, those are merged correctly.
Am I misunderstanding something here?
Thanks,
Zoltan
did you specify the time data type column to use as a basis for the later
wins resolver? You enter this is the Enter Information Needed by the
Resolver text box.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rr news" <znyiri@.hotmail.com> wrote in message
news:HFXad.11969$yP2.4853@.tornado.tampabay.rr.com. ..
> I am testing a scenario with merge replication when publisher has an entry
> 1st, and the same data entered into the subscriber later (but before merge
> agent runs).
> I am using merge replication with 'Microsoft SQL Server DATETIME (Later
> Wins) Conflict Resolver'.
> I inserted the same row (row with same PK) into publisher, then into
> subscriber.
> I am expecting to see row from subscriber replicate to publisher at the
next
> time merge agent runs.
> Instead the row from publisher shows up in my subscriber side.
> The conflict resolver reports that the publisher won:
> The row was inserted at 'EZROUTESUNDB01.repltest' but could not be
inserted
> at 'EZROUTESTGDB01.repltest'. Violation of PRIMARY KEY constraint
'P_test1'.
> Cannot insert duplicate key in object 'test1'.
> The conflict table has conflict type 5, "Upload Insert Failed".
> I want the latest row from subscriber to win that is why I choose "Later
> Wins" resolver.
> BTW, when I update the same row on both sides, repl. works like a charm,
> later change will won independent of which side had it originally (so
> subscriber data migrates to publisher OK if newer).
> Also, when new inserts are made in either side, those are merged
correctly.
> Am I misunderstanding something here?
> Thanks,
> Zoltan
>
|||Hilary,
Thanks for you fast response.
Here are the table definition, the article info, and my test scenarios.
I am only having problem with scenario 3.
Thanks,
Zoltan
CREATE TABLE [test1] (
[num] [int] NOT NULL ,
[date] [datetime] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF__test1__rowguid__353DDB1D] DEFAULT (newid()),
[strcol] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [P_test1] PRIMARY KEY CLUSTERED
(
[num]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sp_helpmergearticle @.article='test1'
id name
source_owner source_object
sync_object_owner sync_object
description status
creation_script conflict_table
article_resolver
subset_filterclause
pre_creation_command schema_option type column_tracking resolver_info
vertical_partition destination_owner
identity_support pub_identity_range identity_range threshold
verify_resolver_signature destination_object
allow_interactive_resolver fast_multicol_updateproc check_permissions
-- ---- --
---- --
--- --
-- ---
-- ---- --
-- ---- --
---- --
-- ---
-- -- -- -- --
-- ---- --
-- ---- --
-- -- -- -- --
-- ----
-- -- -- --
1 test1 dbo
test1 dbo
test1 NULL
2 NULL
conflict_repltest_test1 Microsoft SQL
Server DATETIME (Later Wins) Conflict Resolver NULL
1 0x000000000000CFF1 10 1 date
1 dbo
0 NULL NULL NULL 0
test1 0
1 0
I have a datetime type column named "date" which is entered in the
resolver_info field for article 'test1'.
article_resolver is configured for 'Microsoft SQL Server DATETIME (Later
Wins) Conflict Resolver'
Test scenarios:
1) new (row w/diff PK) inserted into either side, each row replicated
(merged to other side), OK
2) same row updated with different data, then the latest entry wins no
matter if it was created on the publisher side or the subscriber side
3) the only problem I have is when same row (row w/same PK) entered into
both sides.
EZROUTESTGDB01 publisher
EZROUTESUNDB01 sunscriber
--> 2nd scenario, later wins resolver works fine.
-- testing update same row, 1st at subscriber, then publisher
-- insert test row
insert into EZROUTESTGDB01.repltest.dbo.test1 (num, date, strcol) values(
102, getdate(), 'XXX' )
-- wait until it synchs, so both sides have same row
update EZROUTESTGDB01.repltest.dbo.test1 set date=getdate(), strcol='publ'
where num=102
-- wait few seconds
update EZROUTESUNDB01.repltest.dbo.test1 set date=getdate(), strcol='subscr'
where num=102
-- wait until synch, both sides end up with 'subscr' in strcol column, OK
--> 3rd scenario, NOT OK when subscriber has the later entry, publisher
still wins...?!
--clean up, wait until synch
delete from repltest.dbo.test1 where num > 20
-- testing insert same PK into publisher 1st, then subscriber, still
publisher wins, NOT OK!
insert into EZROUTESTGDB01.repltest.dbo.test1(num, date, strcol) values(
101, getdate(),'publisher' )
-- wait few seconds
insert into EZROUTESUNDB01.repltest.dbo.test1(num, date, strcol) values(
101, getdate(),'subscriber' )
-- wait until synch, both sides end up with 'publisher' in strcol column,
NOT OK
Resolver reports:
The row was inserted at 'EZROUTESUNDB01.repltest' but could not be inserted
at 'EZROUTESTGDB01.repltest'. Violation of PRIMARY KEY constraint 'P_test1'.
Cannot insert duplicate key in object 'test1'.
BTW, clocks are synched, I checked it.
Merge agent is configured to run once/minute.
Microsoft SQL Server 2000 - 8.00.760 on both sides.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:em7nkYMsEHA.2144@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> did you specify the time data type column to use as a basis for the later
> wins resolver? You enter this is the Enter Information Needed by the
> Resolver text box.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "rr news" <znyiri@.hotmail.com> wrote in message
> news:HFXad.11969$yP2.4853@.tornado.tampabay.rr.com. ..
entry[vbcol=seagreen]
merge
> next
> inserted
> 'P_test1'.
> correctly.
>
Monday, March 26, 2012
Merge Replication Weird Scenario
My topology:
On the national Server: SQL 2005 Enterprise
On the mobile clients: SQL 2005 Workgroup.
The Scenario:
2 mobile subscriber S1 and S2 to the same simple merge publication P1 on
server N
Day 1,
S1 and S2 both synch up with N and both go off to do fieldwork
Day3,
S1 and S2 both synch up with N.
S1 goes back to work
S2 shutdown the laptop and goes on 2-week vacation.
2 Weeks late
S1 Synch up wit the server and goes off to do fieldwork.
S2 meets S1 in the field. They workfield is in the North Pole.
S2 has the laptop with data 2-weeks old but no longer can have access to the
master publisher N to synch the replica and get latest changes.
S2 will have to sync with S1 since S1 database is fresh. The challenge is to
have S2 and S1 replica identical
The Questions:
Is it possible for S2 to sync with S1 and if yes then and how to go about
it… we need S1 and S2 to have identical replica on their machines?
Now that S1 and S2 are have identical databases and are both doing their
fieldwork in the northpole. Can they both sync back with the national
publisher N when they have access?
Keep in mind that S2 got its data updated from the replica on S1?
Thank you!
No, what you are describing is multi master replication and SQL Server does
not support it.
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
"maher" <maher@.discussions.microsoft.com> wrote in message
news:325C94F9-A80F-4CB6-95FA-5DEF41A8D1CC@.microsoft.com...
> My topology:
> On the national Server: SQL 2005 Enterprise
> On the mobile clients: SQL 2005 Workgroup.
>
> The Scenario:
> 2 mobile subscriber S1 and S2 to the same simple merge publication P1 on
> server N
>
> Day 1,
> S1 and S2 both synch up with N and both go off to do fieldwork
> Day3,
> S1 and S2 both synch up with N.
> S1 goes back to work
> S2 shutdown the laptop and goes on 2-week vacation.
> 2 Weeks late
> S1 Synch up wit the server and goes off to do fieldwork.
> S2 meets S1 in the field. They workfield is in the North Pole.
> S2 has the laptop with data 2-weeks old but no longer can have access to
> the
> master publisher N to synch the replica and get latest changes.
> S2 will have to sync with S1 since S1 database is fresh. The challenge is
> to
> have S2 and S1 replica identical
>
> The Questions:
> Is it possible for S2 to sync with S1 and if yes then and how to go about
> it. we need S1 and S2 to have identical replica on their machines?
>
> Now that S1 and S2 are have identical databases and are both doing their
> fieldwork in the northpole. Can they both sync back with the national
> publisher N when they have access?
> Keep in mind that S2 got its data updated from the replica on S1?
> Thank you!
>
sql
On the national Server: SQL 2005 Enterprise
On the mobile clients: SQL 2005 Workgroup.
The Scenario:
2 mobile subscriber S1 and S2 to the same simple merge publication P1 on
server N
Day 1,
S1 and S2 both synch up with N and both go off to do fieldwork
Day3,
S1 and S2 both synch up with N.
S1 goes back to work
S2 shutdown the laptop and goes on 2-week vacation.
2 Weeks late
S1 Synch up wit the server and goes off to do fieldwork.
S2 meets S1 in the field. They workfield is in the North Pole.
S2 has the laptop with data 2-weeks old but no longer can have access to the
master publisher N to synch the replica and get latest changes.
S2 will have to sync with S1 since S1 database is fresh. The challenge is to
have S2 and S1 replica identical
The Questions:
Is it possible for S2 to sync with S1 and if yes then and how to go about
it… we need S1 and S2 to have identical replica on their machines?
Now that S1 and S2 are have identical databases and are both doing their
fieldwork in the northpole. Can they both sync back with the national
publisher N when they have access?
Keep in mind that S2 got its data updated from the replica on S1?
Thank you!
No, what you are describing is multi master replication and SQL Server does
not support it.
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
"maher" <maher@.discussions.microsoft.com> wrote in message
news:325C94F9-A80F-4CB6-95FA-5DEF41A8D1CC@.microsoft.com...
> My topology:
> On the national Server: SQL 2005 Enterprise
> On the mobile clients: SQL 2005 Workgroup.
>
> The Scenario:
> 2 mobile subscriber S1 and S2 to the same simple merge publication P1 on
> server N
>
> Day 1,
> S1 and S2 both synch up with N and both go off to do fieldwork
> Day3,
> S1 and S2 both synch up with N.
> S1 goes back to work
> S2 shutdown the laptop and goes on 2-week vacation.
> 2 Weeks late
> S1 Synch up wit the server and goes off to do fieldwork.
> S2 meets S1 in the field. They workfield is in the North Pole.
> S2 has the laptop with data 2-weeks old but no longer can have access to
> the
> master publisher N to synch the replica and get latest changes.
> S2 will have to sync with S1 since S1 database is fresh. The challenge is
> to
> have S2 and S1 replica identical
>
> The Questions:
> Is it possible for S2 to sync with S1 and if yes then and how to go about
> it. we need S1 and S2 to have identical replica on their machines?
>
> Now that S1 and S2 are have identical databases and are both doing their
> fieldwork in the northpole. Can they both sync back with the national
> publisher N when they have access?
> Keep in mind that S2 got its data updated from the replica on S1?
> Thank you!
>
sql
Merge Replication Updates on Initialise (SQLServer 2005)
I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.
On initialisation of the subscription, the user should only get the data that is relevant to their code.
The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.
My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.
Is my understadning incorrect?
Is the some parameter in the creation of the publication that we have set incorrectly?
Is there something wrong with SQLServer 2005 itself?
I am very interested to hear anyones comments or advice around this issue.
Thanks
Steve
Hi Steve,
When you initialize the client, you are probably using dynamic snapshot, and when those dynamic snapshots become old the initializing will also need to get the incremental changes which happened after the old dynamic snapshot generated.
You can choose to have the snapshots refreshed on a schedule so new Subscribers that subscribe to a partition for which a snapshot has been created will receive an up-to-date snapshot.
For more information, please refer to Books online topic: Snapshots for Merge Publications with Parameterized Filters
Hope it helps.
Wanwen
|||I assume you mean the snapshot agent running regularly - which it does every night.Seems to be something else - I'll just have to keep looking|||
If you are using dynamic filtering, there are 2 snapshots to consider:
1. Regular snapshots
2. Dynamic snapshots
The regular snapshot do not have data in them.
The dynamic snapshots are the ones that have data in them.
You need to make sure that both are run before your subscriber syncs so that they can use the bcp files from the dynamic snapshot to complete the sync process in a much more timely manner (faster).
sqlMerge Replication Synchronization Manager
Hi,
after upgrading a client within a Merger Replication scenario from MSDE2000A
to SQL Express SP1 the subscription is no longer listed within the
synchronisation manager.
How to register the subscription within Synchronization Manager?
(I know it is possible within Management Studio but I need to automate this
process).
Thanks in advance,
Thomas
have you tried to pull it again in WSM?
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
"ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
news:F36273CF-38BA-4A7C-A373-CB96CCDE37E4@.microsoft.com...
> Hi,
> after upgrading a client within a Merger Replication scenario from
> MSDE2000A
> to SQL Express SP1 the subscription is no longer listed within the
> synchronisation manager.
> How to register the subscription within Synchronization Manager?
> (I know it is possible within Management Studio but I need to automate
> this
> process).
> Thanks in advance,
> Thomas
|||Hi Hilary,
I haven't tried to pull it again within WSM. I would need to reregister it
within WSM, but since this upgrade occurs within a software upgrade I do not
have access to all client machines and I need a solution that I can integrate
into an installer package.
The subscription registration can still be found under
HKLM\Software\Microsoft\Microsoft SQL Server\80\Replication\Subscriptions\...
If I copy this entry to HKLM\Software\Microsoft\Microsoft SQL
Server\90\Replication\Subscriptions\... the subscription shows up in WSM and
works fine. I'm a little bit worried about the fact that within this key is
entry called subid. When I regenerate this entry using "Management Studio"
the subid is different and there is one additional entry called WebSync.
WebSync is always 0 in our case but I don't have a clue where the changed
subid comes from. If I call sp_helpmergepullsubscription it still shows the
"old" subid.
Greetings,
Thomas
"Hilary Cotter" wrote:
> have you tried to pull it again in WSM?
> --
> 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
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:F36273CF-38BA-4A7C-A373-CB96CCDE37E4@.microsoft.com...
>
>
after upgrading a client within a Merger Replication scenario from MSDE2000A
to SQL Express SP1 the subscription is no longer listed within the
synchronisation manager.
How to register the subscription within Synchronization Manager?
(I know it is possible within Management Studio but I need to automate this
process).
Thanks in advance,
Thomas
have you tried to pull it again in WSM?
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
"ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
news:F36273CF-38BA-4A7C-A373-CB96CCDE37E4@.microsoft.com...
> Hi,
> after upgrading a client within a Merger Replication scenario from
> MSDE2000A
> to SQL Express SP1 the subscription is no longer listed within the
> synchronisation manager.
> How to register the subscription within Synchronization Manager?
> (I know it is possible within Management Studio but I need to automate
> this
> process).
> Thanks in advance,
> Thomas
|||Hi Hilary,
I haven't tried to pull it again within WSM. I would need to reregister it
within WSM, but since this upgrade occurs within a software upgrade I do not
have access to all client machines and I need a solution that I can integrate
into an installer package.
The subscription registration can still be found under
HKLM\Software\Microsoft\Microsoft SQL Server\80\Replication\Subscriptions\...
If I copy this entry to HKLM\Software\Microsoft\Microsoft SQL
Server\90\Replication\Subscriptions\... the subscription shows up in WSM and
works fine. I'm a little bit worried about the fact that within this key is
entry called subid. When I regenerate this entry using "Management Studio"
the subid is different and there is one additional entry called WebSync.
WebSync is always 0 in our case but I don't have a clue where the changed
subid comes from. If I call sp_helpmergepullsubscription it still shows the
"old" subid.
Greetings,
Thomas
"Hilary Cotter" wrote:
> have you tried to pull it again in WSM?
> --
> 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
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:F36273CF-38BA-4A7C-A373-CB96CCDE37E4@.microsoft.com...
>
>
Friday, March 23, 2012
Merge replication scenario - how to have inventory working properly
Hi,
I'm using merge replication to replicate the Customers, Orders,
OrderDetails and Stock tables from server A to server B.
Everythings works as expected except the stockage level for a product.
Think about this scenario:
1) Initially the stockage level of product 1 is 20 units.
2) Server A creates a new order with 5 units of product 1.
Stock table in server A now has 15 units for product 1.
3) Server B creates another order with 3 units of product 1.
Stock table in server B now has 17 units for product 1.
4) Synchronization takes place, and there is an update conflict in the
stock table for product 1. Server A wants to save 15 and server B wants
to save 17.
Either value is incorrect because the stockage level shoud be 12.
Is there any way to have this working as expected? I have thought of
creating a custom resolver, but I think there isn't a way to get the
stockage level after the previous synchronization in the conflict
handler, substract that value from the current stockage level, do the
same with the data from the other server and combine the values to get
the proper result.
Thanks a lot!
Manu,
you could have a table which shows initial stock (20). After that the
remaining stock is a view which is initial stock - sum of orders and in this
case there won't be any conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Inventory is defined as the number of units in stock - the number of units
sold. The application maintains inventory in server a.
When server a and server b sync orders will have to move up from server b to
server a. A trigger off the orderdetails table can fire and update the
inventory table on server a and keep it in sync, this trigger can be
designed to only fire on actions originating from server b.
Then the problem becomes keeping the inventory table in sync in both
locations. This can be done as a download only article, but it will be
updated with the next sync.
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
"Manu" <manunews@.gmail.com> wrote in message
news:1169553887.302039.76620@.m58g2000cwm.googlegro ups.com...
> Hi,
> I'm using merge replication to replicate the Customers, Orders,
> OrderDetails and Stock tables from server A to server B.
> Everythings works as expected except the stockage level for a product.
> Think about this scenario:
> 1) Initially the stockage level of product 1 is 20 units.
> 2) Server A creates a new order with 5 units of product 1.
> Stock table in server A now has 15 units for product 1.
> 3) Server B creates another order with 3 units of product 1.
> Stock table in server B now has 17 units for product 1.
> 4) Synchronization takes place, and there is an update conflict in the
> stock table for product 1. Server A wants to save 15 and server B wants
> to save 17.
> Either value is incorrect because the stockage level shoud be 12.
> Is there any way to have this working as expected? I have thought of
> creating a custom resolver, but I think there isn't a way to get the
> stockage level after the previous synchronization in the conflict
> handler, substract that value from the current stockage level, do the
> same with the data from the other server and combine the values to get
> the proper result.
> Thanks a lot!
>
|||Thanks for the help.
On Jan 23, 2:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> Inventory is defined as the number of units in stock - the number of units
> sold. The application maintains inventory in server a.
> When server a and server b sync orders will have to move up from server b to
> server a. A trigger off the orderdetails table can fire and update the
> inventory table on server a and keep it in sync, this trigger can be
> designed to only fire on actions originating from server b.
> Then the problem becomes keeping the inventory table in sync in both
> locations. This can be done as a download only article, but it will be
> updated with the next sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Manu" <manun...@.gmail.com> wrote in messagenews:1169553887.302039.76620@.m58g2000cwm.go oglegroups.com...
>
>
>
>
>
>
I'm using merge replication to replicate the Customers, Orders,
OrderDetails and Stock tables from server A to server B.
Everythings works as expected except the stockage level for a product.
Think about this scenario:
1) Initially the stockage level of product 1 is 20 units.
2) Server A creates a new order with 5 units of product 1.
Stock table in server A now has 15 units for product 1.
3) Server B creates another order with 3 units of product 1.
Stock table in server B now has 17 units for product 1.
4) Synchronization takes place, and there is an update conflict in the
stock table for product 1. Server A wants to save 15 and server B wants
to save 17.
Either value is incorrect because the stockage level shoud be 12.
Is there any way to have this working as expected? I have thought of
creating a custom resolver, but I think there isn't a way to get the
stockage level after the previous synchronization in the conflict
handler, substract that value from the current stockage level, do the
same with the data from the other server and combine the values to get
the proper result.
Thanks a lot!
Manu,
you could have a table which shows initial stock (20). After that the
remaining stock is a view which is initial stock - sum of orders and in this
case there won't be any conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Inventory is defined as the number of units in stock - the number of units
sold. The application maintains inventory in server a.
When server a and server b sync orders will have to move up from server b to
server a. A trigger off the orderdetails table can fire and update the
inventory table on server a and keep it in sync, this trigger can be
designed to only fire on actions originating from server b.
Then the problem becomes keeping the inventory table in sync in both
locations. This can be done as a download only article, but it will be
updated with the next sync.
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
"Manu" <manunews@.gmail.com> wrote in message
news:1169553887.302039.76620@.m58g2000cwm.googlegro ups.com...
> Hi,
> I'm using merge replication to replicate the Customers, Orders,
> OrderDetails and Stock tables from server A to server B.
> Everythings works as expected except the stockage level for a product.
> Think about this scenario:
> 1) Initially the stockage level of product 1 is 20 units.
> 2) Server A creates a new order with 5 units of product 1.
> Stock table in server A now has 15 units for product 1.
> 3) Server B creates another order with 3 units of product 1.
> Stock table in server B now has 17 units for product 1.
> 4) Synchronization takes place, and there is an update conflict in the
> stock table for product 1. Server A wants to save 15 and server B wants
> to save 17.
> Either value is incorrect because the stockage level shoud be 12.
> Is there any way to have this working as expected? I have thought of
> creating a custom resolver, but I think there isn't a way to get the
> stockage level after the previous synchronization in the conflict
> handler, substract that value from the current stockage level, do the
> same with the data from the other server and combine the values to get
> the proper result.
> Thanks a lot!
>
|||Thanks for the help.
On Jan 23, 2:10 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> Inventory is defined as the number of units in stock - the number of units
> sold. The application maintains inventory in server a.
> When server a and server b sync orders will have to move up from server b to
> server a. A trigger off the orderdetails table can fire and update the
> inventory table on server a and keep it in sync, this trigger can be
> designed to only fire on actions originating from server b.
> Then the problem becomes keeping the inventory table in sync in both
> locations. This can be done as a download only article, but it will be
> updated with the next sync.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Manu" <manun...@.gmail.com> wrote in messagenews:1169553887.302039.76620@.m58g2000cwm.go oglegroups.com...
>
>
>
>
>
>
Wednesday, March 21, 2012
Merge Replication Pull Subscription Error
I have replication scenario using SQL SERVER 2000 as the Distributor/Publisher and multiple MSDE databases as the subscribers. When I set up the scenario to use push subscriptions the replication seems to work well. All changes flow correctly between al
l subscribers and the publisher.
When I try to run a pull subscription scenario the replication will fail. I think that the snapshot agent is failing because of some type of security problem. I get the error:
“SQL Server Agent could not access the replication agent. Use the DCOMCNFG utility to confirm that the SQL Server Agent Windows account has permissions to launch the replication agent. The step failed.”
The server is using windows authentication and has the sp3a on it. Again, when I run the replication using push subscribers it works. When I change it to pull subscribers, I get the error.
Any advice is greatly appreciated,
Phil
Are you using remove agent activation?
If so, you must use your Publisher, Susbcriber, or Distributor as the location of your remote agent.
If not, someone has messed with where your merge.exe program is running.
open up DCOMCnfg, locate Microsoft SQL Server Replication Merge Agent 8.0. click on properties Verify in the location tab, that the program runs locally, in the security tab, click on edit for launch permissions. Make sure the everyone group has special
access.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
-- Phil wrote: --
I have replication scenario using SQL SERVER 2000 as the Distributor/Publisher and multiple MSDE databases as the subscribers. When I set up the scenario to use push subscriptions the replication seems to work well. All changes flow correctly betwe
en all subscribers and the publisher.
When I try to run a pull subscription scenario the replication will fail. I think that the snapshot agent is failing because of some type of security problem. I get the error:
“SQL Server Agent could not access the replication agent. Use the DCOMCNFG utility to confirm that the SQL Server Agent Windows account has permissions to launch the replication agent. The step failed.”
The server is using windows authentication and has the sp3a on it. Again, when I run the replication using push subscribers it works. When I change it to pull subscribers, I get the error.
Any advice is greatly appreciated,
Phil
sql
l subscribers and the publisher.
When I try to run a pull subscription scenario the replication will fail. I think that the snapshot agent is failing because of some type of security problem. I get the error:
“SQL Server Agent could not access the replication agent. Use the DCOMCNFG utility to confirm that the SQL Server Agent Windows account has permissions to launch the replication agent. The step failed.”
The server is using windows authentication and has the sp3a on it. Again, when I run the replication using push subscribers it works. When I change it to pull subscribers, I get the error.
Any advice is greatly appreciated,
Phil
Are you using remove agent activation?
If so, you must use your Publisher, Susbcriber, or Distributor as the location of your remote agent.
If not, someone has messed with where your merge.exe program is running.
open up DCOMCnfg, locate Microsoft SQL Server Replication Merge Agent 8.0. click on properties Verify in the location tab, that the program runs locally, in the security tab, click on edit for launch permissions. Make sure the everyone group has special
access.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
-- Phil wrote: --
I have replication scenario using SQL SERVER 2000 as the Distributor/Publisher and multiple MSDE databases as the subscribers. When I set up the scenario to use push subscriptions the replication seems to work well. All changes flow correctly betwe
en all subscribers and the publisher.
When I try to run a pull subscription scenario the replication will fail. I think that the snapshot agent is failing because of some type of security problem. I get the error:
“SQL Server Agent could not access the replication agent. Use the DCOMCNFG utility to confirm that the SQL Server Agent Windows account has permissions to launch the replication agent. The step failed.”
The server is using windows authentication and has the sp3a on it. Again, when I run the replication using push subscribers it works. When I change it to pull subscribers, I get the error.
Any advice is greatly appreciated,
Phil
sql
Merge Replication Problems after upgrade to SQL Express
Hello,
I have the following scenario. An application using SQL Server Standard
Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on
the client machine. Since the client is offline quite often merge replication
is used to keep the clients in sync.
Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor
upgrade (on the same box) worked fine and all clients could still
synchronize. Fine :-)
Now we try to upgrade the clients to SQL Express SP1. Now the problems start
:-(
1) After the upgrade the entry within the Synchronization Manager is gone
(we can overcome this by using sp_MSregistersubscription or by manually
disable and enable Synchronization manager on the subscription properties)
2) Initial Synchronization (takes a long time but) works fine. But if I try
to reinitialize the clients I get the following error:
Error messages:
The merge process could not clean up the subscription to
'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
MSSQL_REPL-2147200965)
Get help: http://help/MSSQL_REPL-2147200965
New request is not allowed to start because it should come with valid
transaction descriptor. (Source: MSSQLServer, Error number: 3989)
Get help: http://help/3989
Remark the test system I use is a nearly empty db (only with full schema and
a few lookup tables) with 15MB. The same error occures if I drop the
subscription before the upgrade and recreate it afterwards. The only way to
get rid of this error is to drop the database and then recreate the
subscription.
Please help otherwise I'm forced to step back to MSDE.
Thanks in advance,
Thomas Hotz
Just some additional information:
The error seems to come from the exec sp_MSCleanupForPullReinit
N'Product',N'Product',N'tstvmw23' call. If I execute this sp manually
replication works again and the snapshot is reapplied also at the beginning
all tables are enumerated for changes.
All following reinit's will work fine afterwards.
I haven't tried to run sp_MSCleanupForPullReinit within a transaction but
that is the next step.
Greetings,
Thomas
"ThoHot00" wrote:
> Hello,
> I have the following scenario. An application using SQL Server Standard
> Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on
> the client machine. Since the client is offline quite often merge replication
> is used to keep the clients in sync.
> Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor
> upgrade (on the same box) worked fine and all clients could still
> synchronize. Fine :-)
> Now we try to upgrade the clients to SQL Express SP1. Now the problems start
> :-(
> 1) After the upgrade the entry within the Synchronization Manager is gone
> (we can overcome this by using sp_MSregistersubscription or by manually
> disable and enable Synchronization manager on the subscription properties)
> 2) Initial Synchronization (takes a long time but) works fine. But if I try
> to reinitialize the clients I get the following error:
> Error messages:
> The merge process could not clean up the subscription to
> 'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
> MSSQL_REPL-2147200965)
> Get help: http://help/MSSQL_REPL-2147200965
> New request is not allowed to start because it should come with valid
> transaction descriptor. (Source: MSSQLServer, Error number: 3989)
> Get help: http://help/3989
> Remark the test system I use is a nearly empty db (only with full schema and
> a few lookup tables) with 15MB. The same error occures if I drop the
> subscription before the upgrade and recreate it afterwards. The only way to
> get rid of this error is to drop the database and then recreate the
> subscription.
> Please help otherwise I'm forced to step back to MSDE.
> Thanks in advance,
> Thomas Hotz
>
|||Does this help?
http://support.microsoft.com/default.aspx/kb/916002
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
"ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
> Hello,
> I have the following scenario. An application using SQL Server Standard
> Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a)
> on
> the client machine. Since the client is offline quite often merge
> replication
> is used to keep the clients in sync.
> Now we try to upgrade to SQL Server 2005 SP1. The publisher and
> distributor
> upgrade (on the same box) worked fine and all clients could still
> synchronize. Fine :-)
> Now we try to upgrade the clients to SQL Express SP1. Now the problems
> start
> :-(
> 1) After the upgrade the entry within the Synchronization Manager is gone
> (we can overcome this by using sp_MSregistersubscription or by manually
> disable and enable Synchronization manager on the subscription properties)
> 2) Initial Synchronization (takes a long time but) works fine. But if I
> try
> to reinitialize the clients I get the following error:
> Error messages:
> The merge process could not clean up the subscription to
> 'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
> MSSQL_REPL-2147200965)
> Get help: http://help/MSSQL_REPL-2147200965
> New request is not allowed to start because it should come with valid
> transaction descriptor. (Source: MSSQLServer, Error number: 3989)
> Get help: http://help/3989
> Remark the test system I use is a nearly empty db (only with full schema
> and
> a few lookup tables) with 15MB. The same error occures if I drop the
> subscription before the upgrade and recreate it afterwards. The only way
> to
> get rid of this error is to drop the database and then recreate the
> subscription.
> Please help otherwise I'm forced to step back to MSDE.
> Thanks in advance,
> Thomas Hotz
>
|||Hi Hilary,
thanks for your reply. I tried this already. I installed the hotfix on the
subscriber but that didn't help. I haven't applied it to the server since I
thought replication is pulled from the subscriber and the server shouldn't
cause this problem.
Thanks again - Greetings,
Thomas
"Hilary Cotter" wrote:
> Does this help?
> http://support.microsoft.com/default.aspx/kb/916002
> --
> 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
>
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
>
>
|||Hi Hilary,
I applied the hotfix on the server and client now, restarted both machines
and the error is still there.
Greetings,
Thomas
"Hilary Cotter" wrote:
> Does this help?
> http://support.microsoft.com/default.aspx/kb/916002
> --
> 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
>
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
>
>
|||In my experience the error actually referred to a timeout during the
already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
drop all the tables, stored procs, triggers etc within the timeout
period specified in WSM (default 30 seconds). It would be worth
profiling the client to see at what point the error occurs i.e. is it
exactly 30 seconds (or whatever your timeout is set to) after the
stored proc starts.
On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Hi Hilary,
> I applied the hotfix on the server and client now, restarted both machines
> and the error is still there.
> Greetings,
> Thomas
> "Hilary Cotter" wrote:
>
>
>
>
>
>
>
|||Hi Tim,
thanks for the hint. This seems to be the problem (and also explain why this
error was so difficult to track). Do you now if it is possible to increase
the default timeout of WSM.
This also explains why begin trans; exec sp_MScleanupForPullReinit
...;rollback trans; solved the problem quite often. Because all data was
already in memory and therefore exec sp_MScleanupForPullReinit executed
within the 30s (the first time it requires 47s).
Greetings,
Thomas
"Tim" wrote:
> In my experience the error actually referred to a timeout during the
> already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
> drop all the tables, stored procs, triggers etc within the timeout
> period specified in WSM (default 30 seconds). It would be worth
> profiling the client to see at what point the error occurs i.e. is it
> exactly 30 seconds (or whatever your timeout is set to) after the
> stored proc starts.
> On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
> wrote:
>
>
|||Hi Tim,
thanks again. I found out that I need to specify QueryTimeout as DWORD
within HKLM\Software\Microsoft\Microsoft SQL
Server\90\Replication\Subscription\<Server>:<Publi cation>:<Database>. If I
specify 120 there for QueryTimeout synchronization work again and Profiler
shows an execution time of 62241 for sp_MScleanupPullForReinit.
Microsoft should specify a better error message there - that would have
saved me a lot of time.
Greetings,
Thomas
"Tim" wrote:
> In my experience the error actually referred to a timeout during the
> already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
> drop all the tables, stored procs, triggers etc within the timeout
> period specified in WSM (default 30 seconds). It would be worth
> profiling the client to see at what point the error occurs i.e. is it
> exactly 30 seconds (or whatever your timeout is set to) after the
> stored proc starts.
> On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
> wrote:
>
>
sql
I have the following scenario. An application using SQL Server Standard
Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on
the client machine. Since the client is offline quite often merge replication
is used to keep the clients in sync.
Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor
upgrade (on the same box) worked fine and all clients could still
synchronize. Fine :-)
Now we try to upgrade the clients to SQL Express SP1. Now the problems start
:-(
1) After the upgrade the entry within the Synchronization Manager is gone
(we can overcome this by using sp_MSregistersubscription or by manually
disable and enable Synchronization manager on the subscription properties)
2) Initial Synchronization (takes a long time but) works fine. But if I try
to reinitialize the clients I get the following error:
Error messages:
The merge process could not clean up the subscription to
'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
MSSQL_REPL-2147200965)
Get help: http://help/MSSQL_REPL-2147200965
New request is not allowed to start because it should come with valid
transaction descriptor. (Source: MSSQLServer, Error number: 3989)
Get help: http://help/3989
Remark the test system I use is a nearly empty db (only with full schema and
a few lookup tables) with 15MB. The same error occures if I drop the
subscription before the upgrade and recreate it afterwards. The only way to
get rid of this error is to drop the database and then recreate the
subscription.
Please help otherwise I'm forced to step back to MSDE.
Thanks in advance,
Thomas Hotz
Just some additional information:
The error seems to come from the exec sp_MSCleanupForPullReinit
N'Product',N'Product',N'tstvmw23' call. If I execute this sp manually
replication works again and the snapshot is reapplied also at the beginning
all tables are enumerated for changes.
All following reinit's will work fine afterwards.
I haven't tried to run sp_MSCleanupForPullReinit within a transaction but
that is the next step.
Greetings,
Thomas
"ThoHot00" wrote:
> Hello,
> I have the following scenario. An application using SQL Server Standard
> Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on
> the client machine. Since the client is offline quite often merge replication
> is used to keep the clients in sync.
> Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor
> upgrade (on the same box) worked fine and all clients could still
> synchronize. Fine :-)
> Now we try to upgrade the clients to SQL Express SP1. Now the problems start
> :-(
> 1) After the upgrade the entry within the Synchronization Manager is gone
> (we can overcome this by using sp_MSregistersubscription or by manually
> disable and enable Synchronization manager on the subscription properties)
> 2) Initial Synchronization (takes a long time but) works fine. But if I try
> to reinitialize the clients I get the following error:
> Error messages:
> The merge process could not clean up the subscription to
> 'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
> MSSQL_REPL-2147200965)
> Get help: http://help/MSSQL_REPL-2147200965
> New request is not allowed to start because it should come with valid
> transaction descriptor. (Source: MSSQLServer, Error number: 3989)
> Get help: http://help/3989
> Remark the test system I use is a nearly empty db (only with full schema and
> a few lookup tables) with 15MB. The same error occures if I drop the
> subscription before the upgrade and recreate it afterwards. The only way to
> get rid of this error is to drop the database and then recreate the
> subscription.
> Please help otherwise I'm forced to step back to MSDE.
> Thanks in advance,
> Thomas Hotz
>
|||Does this help?
http://support.microsoft.com/default.aspx/kb/916002
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
"ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
> Hello,
> I have the following scenario. An application using SQL Server Standard
> Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a)
> on
> the client machine. Since the client is offline quite often merge
> replication
> is used to keep the clients in sync.
> Now we try to upgrade to SQL Server 2005 SP1. The publisher and
> distributor
> upgrade (on the same box) worked fine and all clients could still
> synchronize. Fine :-)
> Now we try to upgrade the clients to SQL Express SP1. Now the problems
> start
> :-(
> 1) After the upgrade the entry within the Synchronization Manager is gone
> (we can overcome this by using sp_MSregistersubscription or by manually
> disable and enable Synchronization manager on the subscription properties)
> 2) Initial Synchronization (takes a long time but) works fine. But if I
> try
> to reinitialize the clients I get the following error:
> Error messages:
> The merge process could not clean up the subscription to
> 'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
> MSSQL_REPL-2147200965)
> Get help: http://help/MSSQL_REPL-2147200965
> New request is not allowed to start because it should come with valid
> transaction descriptor. (Source: MSSQLServer, Error number: 3989)
> Get help: http://help/3989
> Remark the test system I use is a nearly empty db (only with full schema
> and
> a few lookup tables) with 15MB. The same error occures if I drop the
> subscription before the upgrade and recreate it afterwards. The only way
> to
> get rid of this error is to drop the database and then recreate the
> subscription.
> Please help otherwise I'm forced to step back to MSDE.
> Thanks in advance,
> Thomas Hotz
>
|||Hi Hilary,
thanks for your reply. I tried this already. I installed the hotfix on the
subscriber but that didn't help. I haven't applied it to the server since I
thought replication is pulled from the subscriber and the server shouldn't
cause this problem.
Thanks again - Greetings,
Thomas
"Hilary Cotter" wrote:
> Does this help?
> http://support.microsoft.com/default.aspx/kb/916002
> --
> 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
>
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
>
>
|||Hi Hilary,
I applied the hotfix on the server and client now, restarted both machines
and the error is still there.
Greetings,
Thomas
"Hilary Cotter" wrote:
> Does this help?
> http://support.microsoft.com/default.aspx/kb/916002
> --
> 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
>
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
>
>
|||In my experience the error actually referred to a timeout during the
already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
drop all the tables, stored procs, triggers etc within the timeout
period specified in WSM (default 30 seconds). It would be worth
profiling the client to see at what point the error occurs i.e. is it
exactly 30 seconds (or whatever your timeout is set to) after the
stored proc starts.
On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Hi Hilary,
> I applied the hotfix on the server and client now, restarted both machines
> and the error is still there.
> Greetings,
> Thomas
> "Hilary Cotter" wrote:
>
>
>
>
>
>
>
|||Hi Tim,
thanks for the hint. This seems to be the problem (and also explain why this
error was so difficult to track). Do you now if it is possible to increase
the default timeout of WSM.
This also explains why begin trans; exec sp_MScleanupForPullReinit
...;rollback trans; solved the problem quite often. Because all data was
already in memory and therefore exec sp_MScleanupForPullReinit executed
within the 30s (the first time it requires 47s).
Greetings,
Thomas
"Tim" wrote:
> In my experience the error actually referred to a timeout during the
> already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
> drop all the tables, stored procs, triggers etc within the timeout
> period specified in WSM (default 30 seconds). It would be worth
> profiling the client to see at what point the error occurs i.e. is it
> exactly 30 seconds (or whatever your timeout is set to) after the
> stored proc starts.
> On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
> wrote:
>
>
|||Hi Tim,
thanks again. I found out that I need to specify QueryTimeout as DWORD
within HKLM\Software\Microsoft\Microsoft SQL
Server\90\Replication\Subscription\<Server>:<Publi cation>:<Database>. If I
specify 120 there for QueryTimeout synchronization work again and Profiler
shows an execution time of 62241 for sp_MScleanupPullForReinit.
Microsoft should specify a better error message there - that would have
saved me a lot of time.
Greetings,
Thomas
"Tim" wrote:
> In my experience the error actually referred to a timeout during the
> already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
> drop all the tables, stored procs, triggers etc within the timeout
> period specified in WSM (default 30 seconds). It would be worth
> profiling the client to see at what point the error occurs i.e. is it
> exactly 30 seconds (or whatever your timeout is set to) after the
> stored proc starts.
> On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
> wrote:
>
>
sql
Merge replication problem MSDE
Summary:
Failed to insert detail rows in master-detail scenario during merge replication. Not always, sometimes.
Topology:
I've got 5 servers running MSDE, one of them is central publisher/distributor for merge type of replication.
There is no row or column filtering: all subscribers have all data. Central publisher resolve conflicts with default revolvers.
Some tables have relations: master-detail (i.e. orders-ordersDetails, etc). Relations are defined in tables as FK.
My application which fills data use datasets with relations between tables defined the same way as in the sqlserver. So app first work with dataset which is unable to receive details without master record. When data updated to msde, it is done without errors, means master and details table updated correctly (tables at msde has relations too)
Applications running on 4 different locations and fill data to local db (subscriber to central publisher).
Sync occurs every 15 minutes in the following order (merge agents run at publisher/distributor):
subscriber1: every 15 minutes, starts at 00.00h
subscriber2: every 15 minutes, starts at 00.03h
subscriber3: every 15 minutes, starts at 00.06h
subscriber4: every 15 minutes, starts at 00.09h
Sync lasts for average 15 sec, never 3 min.
Problem details:
Message:
The row was inserted at 'CentPub.myDB' but could not be inserted at 'Sub2.MyDB'. INSERT statement conflicted
with COLUMN FOREIGN KEY constraint 'FK_OrdersDetails_Orders'. The conflict occurred in database 'MyDB',
table 'Orders', column 'OrderID'.
Description
CentPub is central publisher which just collecting data from subscribers. So, Order was made on one of the other subscribers different than Sub2, mean user at location3 insert order with details in local database, after a while, CentPub take this order to its database (MyDB), after that CentPub try to sync with some of the other subscribers (i.e. Sub2 == location2) and then for some unknown reason orderDeatils failed to insert in Sub2's orderDeatil table because constraint 'FK_OrdersDetails_Orders' conflict.
After that happened, thing goes like in http://support.microsoft.com/kb/307482 (generally: in next session, failed order details are deleted
from CentPub, which means deleted from all subscribers after syncs.)
The problem is that subscribers have tables with relations, so Cause isn't as describe in MS kb because my tables have relations at all servers.
After finished replications I have Orders without details records at all subscribers, so I assume that merge agents sometimes (not always) try to insert details prior to master table during the same sync session.
In resolution section of MS kb article they say 'Mark the subscriber foreign key constraints as NOT FOR REPLICATION'.
In relations definition I see 'Enforce relationship for replication) options which is enabled in my tables.
If I turn that option off, is it possible to happen that my subscribers receive details without master record? (My observation of behavior says that will not happen(that will lead to problems in my app because my datasets enforce relations too).
Deleted record I bring back to life with conflict manager in EM, but I'd like it never happen.
Is it a bug, side effect or something I do it the wrong way?
Thanks and regardsThe answer is no. When you change your constraints into "NOT FOR REPLICATION" the merge agent is the only process that will ignore the constraint as it propagates the changes amongst the subscribers. The point at which the data is entered into the tables (whether it be front-end app, sproc, etc.) will still enforce the FK constraints.|||Thanks.
I'll try to remove that option.
Anyway, what is the purpose of this option if merge agents don't care about tables with relations?
It is very strange in my situation because I'm sure that my data is in correct form (master-detail) in every point in time (first master, than details) so I don't expect problems during replication.|||Merge agents do care unless you tell them not to. In previous dealings with M$oft, it appears to be related to the number of transactions and the generations associated with them. The merge agent can be set to transmit up to 2000 generations, but will sometines still split up the parent and child transactions, especially in a heavy OLTP database.
Here is link: http://support.microsoft.com/kb/307356/en-us|||Thanks tomh53.
I'll try to increase generations to 2000 and mark not for replication (it should be enough to avoid the problem)
Regards
g.|||I've tested with max value for generations parameter (2000) and everything work fine.
Thanks and regares
G.
Failed to insert detail rows in master-detail scenario during merge replication. Not always, sometimes.
Topology:
I've got 5 servers running MSDE, one of them is central publisher/distributor for merge type of replication.
There is no row or column filtering: all subscribers have all data. Central publisher resolve conflicts with default revolvers.
Some tables have relations: master-detail (i.e. orders-ordersDetails, etc). Relations are defined in tables as FK.
My application which fills data use datasets with relations between tables defined the same way as in the sqlserver. So app first work with dataset which is unable to receive details without master record. When data updated to msde, it is done without errors, means master and details table updated correctly (tables at msde has relations too)
Applications running on 4 different locations and fill data to local db (subscriber to central publisher).
Sync occurs every 15 minutes in the following order (merge agents run at publisher/distributor):
subscriber1: every 15 minutes, starts at 00.00h
subscriber2: every 15 minutes, starts at 00.03h
subscriber3: every 15 minutes, starts at 00.06h
subscriber4: every 15 minutes, starts at 00.09h
Sync lasts for average 15 sec, never 3 min.
Problem details:
Message:
The row was inserted at 'CentPub.myDB' but could not be inserted at 'Sub2.MyDB'. INSERT statement conflicted
with COLUMN FOREIGN KEY constraint 'FK_OrdersDetails_Orders'. The conflict occurred in database 'MyDB',
table 'Orders', column 'OrderID'.
Description
CentPub is central publisher which just collecting data from subscribers. So, Order was made on one of the other subscribers different than Sub2, mean user at location3 insert order with details in local database, after a while, CentPub take this order to its database (MyDB), after that CentPub try to sync with some of the other subscribers (i.e. Sub2 == location2) and then for some unknown reason orderDeatils failed to insert in Sub2's orderDeatil table because constraint 'FK_OrdersDetails_Orders' conflict.
After that happened, thing goes like in http://support.microsoft.com/kb/307482 (generally: in next session, failed order details are deleted
from CentPub, which means deleted from all subscribers after syncs.)
The problem is that subscribers have tables with relations, so Cause isn't as describe in MS kb because my tables have relations at all servers.
After finished replications I have Orders without details records at all subscribers, so I assume that merge agents sometimes (not always) try to insert details prior to master table during the same sync session.
In resolution section of MS kb article they say 'Mark the subscriber foreign key constraints as NOT FOR REPLICATION'.
In relations definition I see 'Enforce relationship for replication) options which is enabled in my tables.
If I turn that option off, is it possible to happen that my subscribers receive details without master record? (My observation of behavior says that will not happen(that will lead to problems in my app because my datasets enforce relations too).
Deleted record I bring back to life with conflict manager in EM, but I'd like it never happen.
Is it a bug, side effect or something I do it the wrong way?
Thanks and regardsThe answer is no. When you change your constraints into "NOT FOR REPLICATION" the merge agent is the only process that will ignore the constraint as it propagates the changes amongst the subscribers. The point at which the data is entered into the tables (whether it be front-end app, sproc, etc.) will still enforce the FK constraints.|||Thanks.
I'll try to remove that option.
Anyway, what is the purpose of this option if merge agents don't care about tables with relations?
It is very strange in my situation because I'm sure that my data is in correct form (master-detail) in every point in time (first master, than details) so I don't expect problems during replication.|||Merge agents do care unless you tell them not to. In previous dealings with M$oft, it appears to be related to the number of transactions and the generations associated with them. The merge agent can be set to transmit up to 2000 generations, but will sometines still split up the parent and child transactions, especially in a heavy OLTP database.
Here is link: http://support.microsoft.com/kb/307356/en-us|||Thanks tomh53.
I'll try to increase generations to 2000 and mark not for replication (it should be enough to avoid the problem)
Regards
g.|||I've tested with max value for generations parameter (2000) and everything work fine.
Thanks and regares
G.
Monday, March 19, 2012
Merge replication not merging changes
my replication scenario involves a table that has a filter on three columns.
I update this table and the data does not get propogated to the subscriber.
effectively, I am creating "deletes" at the subscriber by invalidating the
data (i.e., updates cause it to fall outside the filter criteria)
The initial subscription works. Then I update so the data should be removed
and it doesn't happen.
I use sp_showrowreplicainfo and I can see that the generation info at the
subscriber is lower than that at the publisher.
I initiate the merge and the data does not get removed from the subscriber.
It's very late for me, so maybe I am missing something obvious.
Suggestions appreciated
regards
Steve
To answer my own question after further experimentation, it appears to depend
completely on which table you hang the join filter off.
If the parent table has its referenced row updated, the change cascades
properly down the chain.
Wow, what a tiring lesson to learn.
"SteveM" wrote:
> my replication scenario involves a table that has a filter on three columns.
> I update this table and the data does not get propogated to the subscriber.
> effectively, I am creating "deletes" at the subscriber by invalidating the
> data (i.e., updates cause it to fall outside the filter criteria)
>
|||Generation numbers are localized to the database and article. If you have
more than one subscriber the generation values for the same article on the
publisher and subscriber can vary wildly. IIRC the generation value for the
publisher in a single subscriber topology will always be one larger than the
value on the subscriber.
Check the conflict viewer to see if there is anything there.
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
"SteveM" <SteveM@.discussions.microsoft.com> wrote in message
news:0AC4EB4D-11DD-40C5-A1B3-7160A559B57B@.microsoft.com...
> my replication scenario involves a table that has a filter on three
> columns.
> I update this table and the data does not get propogated to the
> subscriber.
> effectively, I am creating "deletes" at the subscriber by invalidating the
> data (i.e., updates cause it to fall outside the filter criteria)
> The initial subscription works. Then I update so the data should be
> removed
> and it doesn't happen.
> I use sp_showrowreplicainfo and I can see that the generation info at the
> subscriber is lower than that at the publisher.
> I initiate the merge and the data does not get removed from the
> subscriber.
> It's very late for me, so maybe I am missing something obvious.
> Suggestions appreciated
> regards
> Steve
I update this table and the data does not get propogated to the subscriber.
effectively, I am creating "deletes" at the subscriber by invalidating the
data (i.e., updates cause it to fall outside the filter criteria)
The initial subscription works. Then I update so the data should be removed
and it doesn't happen.
I use sp_showrowreplicainfo and I can see that the generation info at the
subscriber is lower than that at the publisher.
I initiate the merge and the data does not get removed from the subscriber.
It's very late for me, so maybe I am missing something obvious.
Suggestions appreciated
regards
Steve
To answer my own question after further experimentation, it appears to depend
completely on which table you hang the join filter off.
If the parent table has its referenced row updated, the change cascades
properly down the chain.
Wow, what a tiring lesson to learn.
"SteveM" wrote:
> my replication scenario involves a table that has a filter on three columns.
> I update this table and the data does not get propogated to the subscriber.
> effectively, I am creating "deletes" at the subscriber by invalidating the
> data (i.e., updates cause it to fall outside the filter criteria)
>
|||Generation numbers are localized to the database and article. If you have
more than one subscriber the generation values for the same article on the
publisher and subscriber can vary wildly. IIRC the generation value for the
publisher in a single subscriber topology will always be one larger than the
value on the subscriber.
Check the conflict viewer to see if there is anything there.
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
"SteveM" <SteveM@.discussions.microsoft.com> wrote in message
news:0AC4EB4D-11DD-40C5-A1B3-7160A559B57B@.microsoft.com...
> my replication scenario involves a table that has a filter on three
> columns.
> I update this table and the data does not get propogated to the
> subscriber.
> effectively, I am creating "deletes" at the subscriber by invalidating the
> data (i.e., updates cause it to fall outside the filter criteria)
> The initial subscription works. Then I update so the data should be
> removed
> and it doesn't happen.
> I use sp_showrowreplicainfo and I can see that the generation info at the
> subscriber is lower than that at the publisher.
> I initiate the merge and the data does not get removed from the
> subscriber.
> It's very late for me, so maybe I am missing something obvious.
> Suggestions appreciated
> regards
> Steve
Saturday, February 25, 2012
Merge Replication Conflicts
Hi,
I have a strange scenario occuring and have no idea why.
I have SQL Server 2000 & Windows 2003 Server running on two machines.
Two databases are set for merge replication.
The subscriber is used purely for redundancy reasons and no client uses it unless the publisher is unavailable. While the publisher is available, I am receiving conflict messages saying that the same column in the same table has been updated on both serve
rs. I know this is not true as no client is running off the subscriber.
The publisher and subscriber have different identity seeds and the identity seed of the updated record that causes the conflict is that of the publisher (not the subscriber).
Has anyone run into this issue before? Any ideas?
Thanks,
Andrew
Having an identity value assigned by the publisher does not indicate
that it wasn't changed at the subscriber. It only indicates where the
row was originally inserted. The subscriber can still modify the rows
provided it had been replicated to the subscriber.
Having said that, the only way you should be able to have conflicts is
if there are conflicting DML statements on the same row on opposite
sides. You could try checking the MSmerge_contents table on the
subscriber database to see what rows were modified at the subscriber.
Hope this helps,
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Andrew wrote:
> Hi,
> I have a strange scenario occuring and have no idea why.
> I have SQL Server 2000 & Windows 2003 Server running on two machines.
> Two databases are set for merge replication.
> The subscriber is used purely for redundancy reasons and no client uses it unless the publisher is unavailable. While the publisher is available, I am receiving conflict messages saying that the same column in the same table has been updated on both ser
vers. I know this is not true as no client is running off the subscriber.
> The publisher and subscriber have different identity seeds and the identity seed of the updated record that causes the conflict is that of the publisher (not the subscriber).
> Has anyone run into this issue before? Any ideas?
> Thanks,
> Andrew
|||Hi,
I can see quite a few records in the MSmerge_contents table at the subscriber. How can I use these to track down who/what is causing this?
There is not a single client in our organisation that is using the subscriber server, so how can there possibly be modification at the subscriber?
When I run a trace on the subscriber for the database that is generating the conflict, i get nothing. (IE: no-one is using that database on that server)
Andrew
|||Not sure if this is relevant but
Have you any triggers on the tables being inserted or updated ? If so you could have changes being made on the
subscriber that could cause confilicts back on the publisher
I have a strange scenario occuring and have no idea why.
I have SQL Server 2000 & Windows 2003 Server running on two machines.
Two databases are set for merge replication.
The subscriber is used purely for redundancy reasons and no client uses it unless the publisher is unavailable. While the publisher is available, I am receiving conflict messages saying that the same column in the same table has been updated on both serve
rs. I know this is not true as no client is running off the subscriber.
The publisher and subscriber have different identity seeds and the identity seed of the updated record that causes the conflict is that of the publisher (not the subscriber).
Has anyone run into this issue before? Any ideas?
Thanks,
Andrew
Having an identity value assigned by the publisher does not indicate
that it wasn't changed at the subscriber. It only indicates where the
row was originally inserted. The subscriber can still modify the rows
provided it had been replicated to the subscriber.
Having said that, the only way you should be able to have conflicts is
if there are conflicting DML statements on the same row on opposite
sides. You could try checking the MSmerge_contents table on the
subscriber database to see what rows were modified at the subscriber.
Hope this helps,
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Andrew wrote:
> Hi,
> I have a strange scenario occuring and have no idea why.
> I have SQL Server 2000 & Windows 2003 Server running on two machines.
> Two databases are set for merge replication.
> The subscriber is used purely for redundancy reasons and no client uses it unless the publisher is unavailable. While the publisher is available, I am receiving conflict messages saying that the same column in the same table has been updated on both ser
vers. I know this is not true as no client is running off the subscriber.
> The publisher and subscriber have different identity seeds and the identity seed of the updated record that causes the conflict is that of the publisher (not the subscriber).
> Has anyone run into this issue before? Any ideas?
> Thanks,
> Andrew
|||Hi,
I can see quite a few records in the MSmerge_contents table at the subscriber. How can I use these to track down who/what is causing this?
There is not a single client in our organisation that is using the subscriber server, so how can there possibly be modification at the subscriber?
When I run a trace on the subscriber for the database that is generating the conflict, i get nothing. (IE: no-one is using that database on that server)
Andrew
|||Not sure if this is relevant but
Have you any triggers on the tables being inserted or updated ? If so you could have changes being made on the
subscriber that could cause confilicts back on the publisher
Merge Replication changes sequence
I have a question for merge replication.
Scenario: Publication with 3 articles (tables A, B and C) and the sequence
of changes at one subscriber are C (delete row), B (insert row), A (delete
row) and B (insert row).
Questions:
1) Does the merge replication agent keep this sequence when updating the
tables at the Publisher and at the other Subscribers?
2) If not, what's the order the merge agent follows for updating the data if
any?
3) Is there a difference if the SQL server version is 2005, 2000 or 7.0?
Thanks,
Ivar
Answers inline.
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
"Ivar" <Ivar@.discussions.microsoft.com> wrote in message
news:A16A74B3-43AA-4F45-9620-E84EC6B350BA@.microsoft.com...
>I have a question for merge replication.
> Scenario: Publication with 3 articles (tables A, B and C) and the sequence
> of changes at one subscriber are C (delete row), B (insert row), A (delete
> row) and B (insert row).
> Questions:
> 1) Does the merge replication agent keep this sequence when updating the
> tables at the Publisher and at the other Subscribers?
It is impossible to predict what sequence they will be applied in.
> 2) If not, what's the order the merge agent follows for updating the data
> if
> any?
Basically deletes are processed first, then it is done according to the
article id.
> 3) Is there a difference if the SQL server version is 2005, 2000 or 7.0?
No, they all do apply the DML in a random manner, except deletes are
processed first. In SQL 2005 you can do logic records which means that
parents will be modified before the children.
> Thanks,
> Ivar
|||As well as Hilary's answer, this might help you to understand the merge
article processing order: http://support.microsoft.com/kb/307356
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Hilary and Paul,
Your comments have been a great help for me. On my application I'm using
triggers and due to the order the Merge Agent was sending changes, the
trigger was rolling back them and changes did not propagate.
Now I modified the article order on my publication and everything is working
fine.
Thank you,
Ivar
"Ivar" wrote:
> I have a question for merge replication.
> Scenario: Publication with 3 articles (tables A, B and C) and the sequence
> of changes at one subscriber are C (delete row), B (insert row), A (delete
> row) and B (insert row).
> Questions:
> 1) Does the merge replication agent keep this sequence when updating the
> tables at the Publisher and at the other Subscribers?
> 2) If not, what's the order the merge agent follows for updating the data if
> any?
> 3) Is there a difference if the SQL server version is 2005, 2000 or 7.0?
> Thanks,
> Ivar
Scenario: Publication with 3 articles (tables A, B and C) and the sequence
of changes at one subscriber are C (delete row), B (insert row), A (delete
row) and B (insert row).
Questions:
1) Does the merge replication agent keep this sequence when updating the
tables at the Publisher and at the other Subscribers?
2) If not, what's the order the merge agent follows for updating the data if
any?
3) Is there a difference if the SQL server version is 2005, 2000 or 7.0?
Thanks,
Ivar
Answers inline.
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
"Ivar" <Ivar@.discussions.microsoft.com> wrote in message
news:A16A74B3-43AA-4F45-9620-E84EC6B350BA@.microsoft.com...
>I have a question for merge replication.
> Scenario: Publication with 3 articles (tables A, B and C) and the sequence
> of changes at one subscriber are C (delete row), B (insert row), A (delete
> row) and B (insert row).
> Questions:
> 1) Does the merge replication agent keep this sequence when updating the
> tables at the Publisher and at the other Subscribers?
It is impossible to predict what sequence they will be applied in.
> 2) If not, what's the order the merge agent follows for updating the data
> if
> any?
Basically deletes are processed first, then it is done according to the
article id.
> 3) Is there a difference if the SQL server version is 2005, 2000 or 7.0?
No, they all do apply the DML in a random manner, except deletes are
processed first. In SQL 2005 you can do logic records which means that
parents will be modified before the children.
> Thanks,
> Ivar
|||As well as Hilary's answer, this might help you to understand the merge
article processing order: http://support.microsoft.com/kb/307356
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Hilary and Paul,
Your comments have been a great help for me. On my application I'm using
triggers and due to the order the Merge Agent was sending changes, the
trigger was rolling back them and changes did not propagate.
Now I modified the article order on my publication and everything is working
fine.
Thank you,
Ivar
"Ivar" wrote:
> I have a question for merge replication.
> Scenario: Publication with 3 articles (tables A, B and C) and the sequence
> of changes at one subscriber are C (delete row), B (insert row), A (delete
> row) and B (insert row).
> Questions:
> 1) Does the merge replication agent keep this sequence when updating the
> tables at the Publisher and at the other Subscribers?
> 2) If not, what's the order the merge agent follows for updating the data if
> any?
> 3) Is there a difference if the SQL server version is 2005, 2000 or 7.0?
> Thanks,
> Ivar
Merge Replication and Trigger Problem
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. Users/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"
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?
Onre posible cause is that there is an indexed view which references the
table. If you do an update which affects the indexed view, SQL Server will
implement it as a deferred update ie a delete/insert pair. Please see
Simon's blog for more details:
http://sqljunkies.com/WebLog/simons/archive/2006/04/24/Indexed_view_update_performance.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
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. Users/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"
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?
Onre posible cause is that there is an indexed view which references the
table. If you do an update which affects the indexed view, SQL Server will
implement it as a deferred update ie a delete/insert pair. Please see
Simon's blog for more details:
http://sqljunkies.com/WebLog/simons/archive/2006/04/24/Indexed_view_update_performance.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Merge Replication and Trigger Problem
Hi,
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.
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.
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...
>
>
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|||Benzine wrote:
Um, yes, I believe so. (Being a poncy type, I tend to do all this kind
of work through writing SQL rather than using Enterprise Manager, but
it looks like it's the sensible choice).
I suppose it could work if (1) the order is reversed for deletes, either
automatically or by request, or (2) cascade-delete triggers are used.|||Thanks allot for your help.
I will make the changes and issue a new snapshot then cross my fingers.
>
of work through writing SQL rather than using Enterprise Manager, but
it looks like it's the sensible choice).
>
Damien|||Hi Damien,
>
>
>
>
>
>
with any multi-row insert/update/delete statements. You need to get
that fixed.
>
However, I don't think that's your problem...
>
>
>
>
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 -|||Benzine wrote:
I'm afraid not. I believe that the first time I encountered this
problem for a project, what I ended up doing was scripting out all of
the foreign keys to a script file. Then using an editor with support
for regular expressions in find and replace (in my case, Visual
Studio), I edited the file to include the "NOT FOR REPLICATION" at the
appropriate places in the file (see Books On Line for ALTER TABLE to
find the right syntax). Then I wrote another script which just tore
down all existing foreign keys in the database. Applying these scripts
in the correct order produced the necessary changes.
I'm afraid not. I believe that the first time I encountered this
problem for a project, what I ended up doing was scripting out all of
the foreign keys to a script file. Then using an editor with support
for regular expressions in find and replace (in my case, Visual
Studio), I edited the file to include the "NOT FOR REPLICATION" at the
appropriate places in the file (see Books On Line for ALTER TABLE to
find the right syntax). Then I wrote another script which just tore
down all existing foreign keys in the database. Applying these scripts
in the correct order produced the necessary changes.
>
On the second project, I had it included from the start, so I've never
had to do this again.
>
Damien
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"
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?Benzine wrote:
Quote:
Originally Posted by
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...
Quote:
Originally Posted by
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|||Thanks for your reply Damien,
Is unchecking "Enforce relationships for replication" the same as
marking FK "Not for Replication"
Damien wrote:
Quote:
Originally Posted by
Benzine wrote:
Quote:
Originally Posted by
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...
>
Quote:
Originally Posted by
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|||Benzine wrote:
Quote:
Originally Posted by
Thanks for your reply Damien,
>
Is unchecking "Enforce relationships for replication" the same as
marking FK "Not for Replication"
>
Um, yes, I believe so. (Being a poncy type, I tend to do all this kind
of work through writing SQL rather than using Enterprise Manager, but
it looks like it's the sensible choice).
Damien|||Damien wrote:
Quote:
Originally Posted by
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?
I suppose it could work if (1) the order is reversed for deletes, either
automatically or by request, or (2) cascade-delete triggers are used.|||Thanks allot for your help.
I will make the changes and issue a new snapshot then cross my fingers.
On Jan 5, 1:02 am, "Damien" <Damien_The_Unbelie...@.hotmail.comwrote:
Quote:
Originally Posted by
Benzine wrote:
Quote:
Originally Posted by
Thanks for your reply Damien,
>
Quote:
Originally Posted by
Is unchecking "Enforce relationships for replication" the same as
marking FK "Not for Replication"Um, yes, I believe so. (Being a poncy type, I tend to do all this kind
of work through writing SQL rather than using Enterprise Manager, but
it looks like it's the sensible choice).
>
Damien|||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.comwrote:
Quote:
Originally Posted by
Benzine wrote:
Quote:
Originally Posted by
Hi,
>
Quote:
Originally Posted by
I have an issue with my replication at the moment. I will try to
describe the scenario accurately.
>
Quote:
Originally Posted by
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:
>
Quote:
Originally Posted by
"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
>
Quote:
Originally Posted by
select @.projId = project_id from inserted
select @.projcurrtype = proj_curr_type from qt_projects where project_id
= @.projId
>
Quote:
Originally Posted by
--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)
>
Quote:
Originally Posted by
--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...
>
Quote:
Originally Posted by
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:
>
Quote:
Originally Posted by
"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'."
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
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 -|||Benzine wrote:
Quote:
Originally Posted by
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
>
I'm afraid not. I believe that the first time I encountered this
problem for a project, what I ended up doing was scripting out all of
the foreign keys to a script file. Then using an editor with support
for regular expressions in find and replace (in my case, Visual
Studio), I edited the file to include the "NOT FOR REPLICATION" at the
appropriate places in the file (see Books On Line for ALTER TABLE to
find the right syntax). Then I wrote another script which just tore
down all existing foreign keys in the database. Applying these scripts
in the correct order produced the necessary changes.
On the second project, I had it included from the start, so I've never
had to do this again.
Damien|||Thanks again.
Damien wrote:
Quote:
Originally Posted by
Benzine wrote:
>
Quote:
Originally Posted by
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
I'm afraid not. I believe that the first time I encountered this
problem for a project, what I ended up doing was scripting out all of
the foreign keys to a script file. Then using an editor with support
for regular expressions in find and replace (in my case, Visual
Studio), I edited the file to include the "NOT FOR REPLICATION" at the
appropriate places in the file (see Books On Line for ALTER TABLE to
find the right syntax). Then I wrote another script which just tore
down all existing foreign keys in the database. Applying these scripts
in the correct order produced the necessary changes.
>
On the second project, I had it included from the start, so I've never
had to do this again.
>
Damien
Monday, February 20, 2012
Merge Replication and MSDE
I hava a scenario where I have an SQL Server set up as the publisher (merge)
and the subscribers are Laptops with MSDE which work mostly offline.
The subscribers are updated once they are online with the publisher.
My question is: How can the subscriber tell if the MSDN database has
finished the replication with the publisher?
You see some times the laptops are online just a few minutes and the
connection is slow. I need to make sure the subscribers are online just long
enough to complete the merge !
I appreciate any help I get can on this issue :-)
Regards
Peter
You could also hook into the sql server com object on the client. This will
allow you to start the sync manually if required. It also has the benifit of
haveing a callback so you can display a progress. If the server and client
stop talking to each other because of a time out period away from the
network this manual method will reinistialise the connection.
- Mike
"Peter F" <Peter F@.discussions.microsoft.com> wrote in message
news:BB950EB3-A351-4077-96AD-F5BBBDD7F7F0@.microsoft.com...
>I hava a scenario where I have an SQL Server set up as the publisher
>(merge)
> and the subscribers are Laptops with MSDE which work mostly offline.
> The subscribers are updated once they are online with the publisher.
> My question is: How can the subscriber tell if the MSDN database has
> finished the replication with the publisher?
> You see some times the laptops are online just a few minutes and the
> connection is slow. I need to make sure the subscribers are online just
> long
> enough to complete the merge !
> I appreciate any help I get can on this issue :-)
> Regards
> Peter
and the subscribers are Laptops with MSDE which work mostly offline.
The subscribers are updated once they are online with the publisher.
My question is: How can the subscriber tell if the MSDN database has
finished the replication with the publisher?
You see some times the laptops are online just a few minutes and the
connection is slow. I need to make sure the subscribers are online just long
enough to complete the merge !
I appreciate any help I get can on this issue :-)
Regards
Peter
You could also hook into the sql server com object on the client. This will
allow you to start the sync manually if required. It also has the benifit of
haveing a callback so you can display a progress. If the server and client
stop talking to each other because of a time out period away from the
network this manual method will reinistialise the connection.
- Mike
"Peter F" <Peter F@.discussions.microsoft.com> wrote in message
news:BB950EB3-A351-4077-96AD-F5BBBDD7F7F0@.microsoft.com...
>I hava a scenario where I have an SQL Server set up as the publisher
>(merge)
> and the subscribers are Laptops with MSDE which work mostly offline.
> The subscribers are updated once they are online with the publisher.
> My question is: How can the subscriber tell if the MSDN database has
> finished the replication with the publisher?
> You see some times the laptops are online just a few minutes and the
> connection is slow. I need to make sure the subscribers are online just
> long
> enough to complete the merge !
> I appreciate any help I get can on this issue :-)
> Regards
> Peter
Merge replication 101
Hi all,
I am trying to set up replication for the following scenario. Four satellite offices send changes daily up to a master database. Then, at the end of the day, the master database, which incorporates all the updates from the offices, sends an updated copy of the db back to the 4 offices; at the beginning of the next day, each office now has an updated db to work with.
Merge replication worked to a point; I was able to have 2 of the 4 offices merge data up to the master db. So far, so good.
But I cannot get the master db to replicate back down to the satellite offices.
I have seen mention of updatable subscriptions. Is merge by definition supposed to be updatable? And does this mean that all changes at the subscriber are supposed to come back to the publisher?
Thanks in advance for any insight into this.
When you sync (default is upload and download), any changes at the subscriber are uploaded to the Publisher and any changes made at the Publisher are downloaded to the Subscriber. (Note that you may have to handle or plan for conflicts on DML at publisher and subscriber or other subscribers).
That being the case, when you synched after making subscriber changes, data at publisher and the subscriber is in sync.
So when you say:
"But I cannot get the master db to replicate back down to the satellite offices"
what do you mean?
1. Are your changes made at publisher not going down to one subscriber or all subscribers or some subscribers?
2. Were you looking for reinitializing the subscriber?
3. Do you have filters in articles that can prohibit changes from one subscriber not making it to another subscriber?
By default, changes can be made at both the merge publisher and subscriber.
Please describe more clearly your problem as it will help in troubleshooting.
|||Merge is the right publication type for what you want to do. By default, merge subscriptions are updateable and the changes to the replicated data will be synchronized with the Publisher. (The term "updatable subscription" usually refers to a feature of transactional publications that allow immediate updating or queued updating subscriptions.)In this scenario, you will have to do two merges for each subscription to complete the round-trip synchronization:
1) Each subscription synchronizes with the Publisher.
2) After the Publisher has all data from all Subscribers, synchronize again so that the data can be sent to the Subscribers.
Is your publication filtered? If you are using dynamic filtering in your publication such that the different subscriptions receive different partitions of the data, a subscription will never receive data that doesn't fit in its partition. When you synchronize in step 2 above, changed data at the Publisher would never get send to the other subscriptions.
Phil Garding
Subscribe to:
Posts (Atom)