HI,
The situation is like this, merge replication is setup on server(Publisher),
and 4 clients machines and these are subscribers.
I have two table with primary key and foreign key relationship. Both the
tables have primary key with Uniqueidentifier column. First uniqueidnetifer
column is mapped to second table as Foregein key.
here in some situation inserts into primary key and foregein key tables
happyining in the sequence like insert first in Primary Key table, second in
Foregein Key table.
In some situations the process is reversing, like first inserting foregein
key table then primary key table. With this type of activity i am loosing
most important data in teh foregein key tabel. Suggest me in this how can i
proceed.
Regards
Satish
After you make some data inserts on publisher, merge replication is applying
those changes on subscriber, but these modifications can be applied in
different sequence than you originally perfomed on publisher. The common way
to avoid foreign key conflicts is adding of NOT FOR REPLICATION flag to
foreign key constraints. When this flag is set - foreign key constraint is
not checked for replicated data. I suggest you to check Books Online for
more information.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Satish" <Satish@.discussions.microsoft.com> wrote in message
news:B0F93C30-505A-47FC-B5A4-19ADD5FD94C3@.microsoft.com...
> HI,
> The situation is like this, merge replication is setup on
server(Publisher),
> and 4 clients machines and these are subscribers.
> I have two table with primary key and foreign key relationship. Both the
> tables have primary key with Uniqueidentifier column. First
uniqueidnetifer
> column is mapped to second table as Foregein key.
> here in some situation inserts into primary key and foregein key tables
> happyining in the sequence like insert first in Primary Key table, second
in
> Foregein Key table.
> In some situations the process is reversing, like first inserting foregein
> key table then primary key table. With this type of activity i am loosing
> most important data in teh foregein key tabel. Suggest me in this how can
i
> proceed.
>
> Regards
> Satish
|||Thank you for quick reply. Most of the wesites are telling to create Foreign
Key with NOT FOR REPLICATION option.
I have got one more question that, why merge replication is not inserting
the data in the sequence manner, first Primary key data and then Foreign key
data. Any specific reason in this.
Regards
Satish
"Kestutis Adomavicius" wrote:
> After you make some data inserts on publisher, merge replication is applying
> those changes on subscriber, but these modifications can be applied in
> different sequence than you originally perfomed on publisher. The common way
> to avoid foreign key conflicts is adding of NOT FOR REPLICATION flag to
> foreign key constraints. When this flag is set - foreign key constraint is
> not checked for replicated data. I suggest you to check Books Online for
> more information.
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Satish" <Satish@.discussions.microsoft.com> wrote in message
> news:B0F93C30-505A-47FC-B5A4-19ADD5FD94C3@.microsoft.com...
> server(Publisher),
> uniqueidnetifer
> in
> i
>
>
|||Satish,
This is standard behaviour in SQL Server 2000 and is improved in SQL Server
2005.
Have a look at these articles for more details:
http://support.microsoft.com/default.aspx?scid=kb;[LN];307356
http://support.microsoft.com/kb/308266/EN-US/
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label merge. Show all posts
Showing posts with label merge. Show all posts
Thursday, March 29, 2012
Monday, March 26, 2012
Forcing SQL Express to synchronize
I have a situation where I have SQL Server 2005 as my Publisher/Distributor
and I have it setup to run Push Merge Replication. The subscriber is an
instance of SQL Server Express. Obviously SQL Server Express doesn't have
the means to force synchronization. I do have it setup at the moment to run
Continiously but this will definitely be resource intensive running Merge
Replication on a 1000 or so databases...so I would like to be able to set it
up as Run on Demand and force the synchronization from the application. I
have seen BOL but that RMO code is all in .Net, I am programming in
VB6...and furthermore it looks like I have to use a Pull Subscription in
order to code for RMO...is that correct?
How should I go about setting this up for optimum performance?
Thanks!
RSH
I would advise you to use a pull subscription and use the activeX controls
(you can use the SQL 2005 activeX control with VB6 - use set
test=CreateObject("SQLDistribution.90.SQLDistribut ion")
), or use WSM for that.
You want to use a pull as it has a lower impact on the publisher and you can
run it from the subscriber which is especially important if your publisher
is offline from time to time.
For WSM go to Start, All Programs, Accessories and select Synchronize. Click
on MS SQL Server and select Properties to select your publication and set
subscription properties.
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
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>I have a situation where I have SQL Server 2005 as my Publisher/Distributor
>and I have it setup to run Push Merge Replication. The subscriber is an
>instance of SQL Server Express. Obviously SQL Server Express doesn't have
>the means to force synchronization. I do have it setup at the moment to
>run Continiously but this will definitely be resource intensive running
>Merge Replication on a 1000 or so databases...so I would like to be able to
>set it up as Run on Demand and force the synchronization from the
>application. I have seen BOL but that RMO code is all in .Net, I am
>programming in VB6...and furthermore it looks like I have to use a Pull
>Subscription in order to code for RMO...is that correct?
> How should I go about setting this up for optimum performance?
> Thanks!
> RSH
>
|||Hilary,
Thanks for your reply.
Question 1:
I set up a Pull subscription scenerio and on the SQLServer Express instance
when I run WSM I get the following error:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber."
"The process could not read the file 'C:\Program Files...' due to OS Error
3"
That directory does not exist from the "unc\" part of the path so I am
assuming that a snapshot hasn't been created. If I am correct how do I
generate a snapshot in SQL Express? Or is it created on the SQL Server
instance and copied over?
Question 2:
The VB Code below is my quick attempt at using the Active X control. The
code generates the following error: "The subscription to publication
'TestSub1' has expired or does not exist" which is not right...it does in
fact exist as illustrated in my screenshot...what am I missing here?
Option Explicit
' Add Reference Microsoft SQL Distribution Control 8.0
Private mobjDistr As SQLDISTXLib.SQLDistribution
Private Sub main()
Set mobjDistr = New SQLDISTXLib.SQLDistribution
On Error GoTo Errhandler
With mobjDistr
'Set up the Publisher
.Publisher = "VIRTUAL1"
.PublisherDatabase = "00010101"
.Publication = "TPTestSub1"
.PublisherSecurityMode = NT_AUTHENTICATION
'Set up the Subscriber.
.Subscriber = "PARENTVM\TPSVCCTR01"
.SubscriberDatabase = "00010101"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberLogin = "V1"
.SubscriberPassword = "pw"
'Set up the Subscription.
.SubscriptionType = PULL
.SynchronizationType = AUTOMATIC
'Synchronize the data.
.Initialize
.Run
.Terminate
MsgBox "Complete"
End With
Exit Sub
Errhandler:
MsgBox Error
End Sub
Thanks a ton for your help!
RSH
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I would advise you to use a pull subscription and use the activeX controls
>(you can use the SQL 2005 activeX control with VB6 - use set
>test=CreateObject("SQLDistribution.90.SQLDistribu tion")
> ), or use WSM for that.
> You want to use a pull as it has a lower impact on the publisher and you
> can run it from the subscriber which is especially important if your
> publisher is offline from time to time.
> For WSM go to Start, All Programs, Accessories and select Synchronize.
> Click on MS SQL Server and select Properties to select your publication
> and set subscription properties.
> --
> 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
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>
|||This almost always means a permissions problem. From what it looks like you
are connecting from your subscriber to c:\Program files\... on your
publisher, or at least it should be your publisher. Make sure when you
configured your distributor that your snapshot location was defined as a
share. If not you can go back and fix it by right clicking on your
replication folder and selecting distributor properties.Select the
Publishers node, and for your publisher select the browse button to the
right of your publisher and in the default snapshot folder make it a share.
For your second question, the reason you most frequently get this message is
because the snapshot has not being generated for this publication.
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
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> Thanks for your reply.
> Question 1:
> I set up a Pull subscription scenerio and on the SQLServer Express
> instance when I run WSM I get the following error:
> "The Schema script 'Accrual_2.sch' could not be propogated to the
> Subscriber."
> "The process could not read the file 'C:\Program Files...' due to OS Error
> 3"
> That directory does not exist from the "unc\" part of the path so I am
> assuming that a snapshot hasn't been created. If I am correct how do I
> generate a snapshot in SQL Express? Or is it created on the SQL Server
> instance and copied over?
>
> Question 2:
>
> The VB Code below is my quick attempt at using the Active X control. The
> code generates the following error: "The subscription to publication
> 'TestSub1' has expired or does not exist" which is not right...it does in
> fact exist as illustrated in my screenshot...what am I missing here?
> Option Explicit
> ' Add Reference Microsoft SQL Distribution Control 8.0
> Private mobjDistr As SQLDISTXLib.SQLDistribution
> Private Sub main()
> Set mobjDistr = New SQLDISTXLib.SQLDistribution
> On Error GoTo Errhandler
> With mobjDistr
> 'Set up the Publisher
> .Publisher = "VIRTUAL1"
> .PublisherDatabase = "00010101"
> .Publication = "TPTestSub1"
> .PublisherSecurityMode = NT_AUTHENTICATION
> 'Set up the Subscriber.
> .Subscriber = "PARENTVM\TPSVCCTR01"
> .SubscriberDatabase = "00010101"
> .SubscriberDatasourceType = SQL_SERVER
> .SubscriberSecurityMode = DB_AUTHENTICATION
> .SubscriberLogin = "V1"
> .SubscriberPassword = "pw"
> 'Set up the Subscription.
> .SubscriptionType = PULL
> .SynchronizationType = AUTOMATIC
> 'Synchronize the data.
> .Initialize
> .Run
> .Terminate
> MsgBox "Complete"
> End With
> Exit Sub
> Errhandler:
> MsgBox Error
> End Sub
> Thanks a ton for your help!
> RSH
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks.
How do I generate the snapshot?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
> This almost always means a permissions problem. From what it looks like
> you are connecting from your subscriber to c:\Program files\... on your
> publisher, or at least it should be your publisher. Make sure when you
> configured your distributor that your snapshot location was defined as a
> share. If not you can go back and fix it by right clicking on your
> replication folder and selecting distributor properties.Select the
> Publishers node, and for your publisher select the browse button to the
> right of your publisher and in the default snapshot folder make it a
> share.
> For your second question, the reason you most frequently get this message
> is because the snapshot has not being generated for this publication.
> --
> 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
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
>
|||Hillary,
I set the Snapshot folder up as a Share as you described yesterday. I am no
longer getting the error about:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber." and I see the files being setup in the share.
I am still getting the pesky error: "The subscription to publication
'TestSub1' has expired or does not exist"
I assume that as part of the setup that somewhere the subscriber is aware of
where to look for the snapshot...I did not see any properties for this when
setting up the subscriber. Also something that might be a bit odd is that I
am generating the snapshot from the Publisher side...is this correct? Or
should I somehow generate it from the subscriber (if so how?) and if that is
the case how do I make the Publisher aware of the fact that it was created?
Thanks alot for your time!
Ron
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZBocEb9FHA.500@.TK2MSFTNGP15.phx.gbl...
> Thanks.
> How do I generate the snapshot?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
>
and I have it setup to run Push Merge Replication. The subscriber is an
instance of SQL Server Express. Obviously SQL Server Express doesn't have
the means to force synchronization. I do have it setup at the moment to run
Continiously but this will definitely be resource intensive running Merge
Replication on a 1000 or so databases...so I would like to be able to set it
up as Run on Demand and force the synchronization from the application. I
have seen BOL but that RMO code is all in .Net, I am programming in
VB6...and furthermore it looks like I have to use a Pull Subscription in
order to code for RMO...is that correct?
How should I go about setting this up for optimum performance?
Thanks!
RSH
I would advise you to use a pull subscription and use the activeX controls
(you can use the SQL 2005 activeX control with VB6 - use set
test=CreateObject("SQLDistribution.90.SQLDistribut ion")
), or use WSM for that.
You want to use a pull as it has a lower impact on the publisher and you can
run it from the subscriber which is especially important if your publisher
is offline from time to time.
For WSM go to Start, All Programs, Accessories and select Synchronize. Click
on MS SQL Server and select Properties to select your publication and set
subscription properties.
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
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>I have a situation where I have SQL Server 2005 as my Publisher/Distributor
>and I have it setup to run Push Merge Replication. The subscriber is an
>instance of SQL Server Express. Obviously SQL Server Express doesn't have
>the means to force synchronization. I do have it setup at the moment to
>run Continiously but this will definitely be resource intensive running
>Merge Replication on a 1000 or so databases...so I would like to be able to
>set it up as Run on Demand and force the synchronization from the
>application. I have seen BOL but that RMO code is all in .Net, I am
>programming in VB6...and furthermore it looks like I have to use a Pull
>Subscription in order to code for RMO...is that correct?
> How should I go about setting this up for optimum performance?
> Thanks!
> RSH
>
|||Hilary,
Thanks for your reply.
Question 1:
I set up a Pull subscription scenerio and on the SQLServer Express instance
when I run WSM I get the following error:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber."
"The process could not read the file 'C:\Program Files...' due to OS Error
3"
That directory does not exist from the "unc\" part of the path so I am
assuming that a snapshot hasn't been created. If I am correct how do I
generate a snapshot in SQL Express? Or is it created on the SQL Server
instance and copied over?
Question 2:
The VB Code below is my quick attempt at using the Active X control. The
code generates the following error: "The subscription to publication
'TestSub1' has expired or does not exist" which is not right...it does in
fact exist as illustrated in my screenshot...what am I missing here?
Option Explicit
' Add Reference Microsoft SQL Distribution Control 8.0
Private mobjDistr As SQLDISTXLib.SQLDistribution
Private Sub main()
Set mobjDistr = New SQLDISTXLib.SQLDistribution
On Error GoTo Errhandler
With mobjDistr
'Set up the Publisher
.Publisher = "VIRTUAL1"
.PublisherDatabase = "00010101"
.Publication = "TPTestSub1"
.PublisherSecurityMode = NT_AUTHENTICATION
'Set up the Subscriber.
.Subscriber = "PARENTVM\TPSVCCTR01"
.SubscriberDatabase = "00010101"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberLogin = "V1"
.SubscriberPassword = "pw"
'Set up the Subscription.
.SubscriptionType = PULL
.SynchronizationType = AUTOMATIC
'Synchronize the data.
.Initialize
.Run
.Terminate
MsgBox "Complete"
End With
Exit Sub
Errhandler:
MsgBox Error
End Sub
Thanks a ton for your help!
RSH
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I would advise you to use a pull subscription and use the activeX controls
>(you can use the SQL 2005 activeX control with VB6 - use set
>test=CreateObject("SQLDistribution.90.SQLDistribu tion")
> ), or use WSM for that.
> You want to use a pull as it has a lower impact on the publisher and you
> can run it from the subscriber which is especially important if your
> publisher is offline from time to time.
> For WSM go to Start, All Programs, Accessories and select Synchronize.
> Click on MS SQL Server and select Properties to select your publication
> and set subscription properties.
> --
> 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
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:O5%230v1Q9FHA.3044@.TK2MSFTNGP10.phx.gbl...
>
|||This almost always means a permissions problem. From what it looks like you
are connecting from your subscriber to c:\Program files\... on your
publisher, or at least it should be your publisher. Make sure when you
configured your distributor that your snapshot location was defined as a
share. If not you can go back and fix it by right clicking on your
replication folder and selecting distributor properties.Select the
Publishers node, and for your publisher select the browse button to the
right of your publisher and in the default snapshot folder make it a share.
For your second question, the reason you most frequently get this message is
because the snapshot has not being generated for this publication.
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
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hilary,
> Thanks for your reply.
> Question 1:
> I set up a Pull subscription scenerio and on the SQLServer Express
> instance when I run WSM I get the following error:
> "The Schema script 'Accrual_2.sch' could not be propogated to the
> Subscriber."
> "The process could not read the file 'C:\Program Files...' due to OS Error
> 3"
> That directory does not exist from the "unc\" part of the path so I am
> assuming that a snapshot hasn't been created. If I am correct how do I
> generate a snapshot in SQL Express? Or is it created on the SQL Server
> instance and copied over?
>
> Question 2:
>
> The VB Code below is my quick attempt at using the Active X control. The
> code generates the following error: "The subscription to publication
> 'TestSub1' has expired or does not exist" which is not right...it does in
> fact exist as illustrated in my screenshot...what am I missing here?
> Option Explicit
> ' Add Reference Microsoft SQL Distribution Control 8.0
> Private mobjDistr As SQLDISTXLib.SQLDistribution
> Private Sub main()
> Set mobjDistr = New SQLDISTXLib.SQLDistribution
> On Error GoTo Errhandler
> With mobjDistr
> 'Set up the Publisher
> .Publisher = "VIRTUAL1"
> .PublisherDatabase = "00010101"
> .Publication = "TPTestSub1"
> .PublisherSecurityMode = NT_AUTHENTICATION
> 'Set up the Subscriber.
> .Subscriber = "PARENTVM\TPSVCCTR01"
> .SubscriberDatabase = "00010101"
> .SubscriberDatasourceType = SQL_SERVER
> .SubscriberSecurityMode = DB_AUTHENTICATION
> .SubscriberLogin = "V1"
> .SubscriberPassword = "pw"
> 'Set up the Subscription.
> .SubscriptionType = PULL
> .SynchronizationType = AUTOMATIC
> 'Synchronize the data.
> .Initialize
> .Run
> .Terminate
> MsgBox "Complete"
> End With
> Exit Sub
> Errhandler:
> MsgBox Error
> End Sub
> Thanks a ton for your help!
> RSH
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ecjk%23rR9FHA.1184@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks.
How do I generate the snapshot?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
> This almost always means a permissions problem. From what it looks like
> you are connecting from your subscriber to c:\Program files\... on your
> publisher, or at least it should be your publisher. Make sure when you
> configured your distributor that your snapshot location was defined as a
> share. If not you can go back and fix it by right clicking on your
> replication folder and selecting distributor properties.Select the
> Publishers node, and for your publisher select the browse button to the
> right of your publisher and in the default snapshot folder make it a
> share.
> For your second question, the reason you most frequently get this message
> is because the snapshot has not being generated for this publication.
> --
> 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
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:Olcv3AT9FHA.636@.TK2MSFTNGP10.phx.gbl...
>
|||Hillary,
I set the Snapshot folder up as a Share as you described yesterday. I am no
longer getting the error about:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber." and I see the files being setup in the share.
I am still getting the pesky error: "The subscription to publication
'TestSub1' has expired or does not exist"
I assume that as part of the setup that somewhere the subscriber is aware of
where to look for the snapshot...I did not see any properties for this when
setting up the subscriber. Also something that might be a bit odd is that I
am generating the snapshot from the Publisher side...is this correct? Or
should I somehow generate it from the subscriber (if so how?) and if that is
the case how do I make the Publisher aware of the fact that it was created?
Thanks alot for your time!
Ron
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eZBocEb9FHA.500@.TK2MSFTNGP15.phx.gbl...
> Thanks.
> How do I generate the snapshot?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:evENuiV9FHA.1188@.TK2MSFTNGP12.phx.gbl...
>
Friday, March 23, 2012
-ForceConvergenceLevel - Where dose it go?
It is (slightly) documented in BOL - see Replication
Merge Agent Utility. This is a commandline parameter that
can be added to the merge agent's job step. Is this
something that you have been told to do by PSS? I'm
curious about why you are implementing it.
Rgds,
Paul Ibison
[vbcol=seagreen]
Hi Paul.
I'm desperate, due to problems with rows not being replicated. Two
subscribers synchronizes fine without errors, but one gest all the rows from
a table and the other gets most but a few is missing. No filters is applied
on that article.
So I saw a discussion on the subject (can't remember where) and this article
http://support.microsoft.com/default...b;en-us;272210 so I thought
it was worth a try.
"Paul Ibison" wrote:
> It is (slightly) documented in BOL - see Replication
> Merge Agent Utility. This is a commandline parameter that
> can be added to the merge agent's job step. Is this
> something that you have been told to do by PSS? I'm
> curious about why you are implementing it.
> Rgds,
> Paul Ibison
>
>
Merge Agent Utility. This is a commandline parameter that
can be added to the merge agent's job step. Is this
something that you have been told to do by PSS? I'm
curious about why you are implementing it.
Rgds,
Paul Ibison
[vbcol=seagreen]
Hi Paul.
I'm desperate, due to problems with rows not being replicated. Two
subscribers synchronizes fine without errors, but one gest all the rows from
a table and the other gets most but a few is missing. No filters is applied
on that article.
So I saw a discussion on the subject (can't remember where) and this article
http://support.microsoft.com/default...b;en-us;272210 so I thought
it was worth a try.
"Paul Ibison" wrote:
> It is (slightly) documented in BOL - see Replication
> Merge Agent Utility. This is a commandline parameter that
> can be added to the merge agent's job step. Is this
> something that you have been told to do by PSS? I'm
> curious about why you are implementing it.
> Rgds,
> Paul Ibison
>
>
-ForceConvergenceLevel - Where dose it go?
Hi,
There is a undocumented Merge Agent parameter (-ForceConvergenceLevel) that
could solve some of my troubles, but where do I specify it?
It's not available in the the profile setup for the agent in the Enterprise
manager.
It's possible to specify it on the command line to replmerg.exe, but I want
it to be effektive whenever my subscribers synchronizes.
Regards
Jorgen D:
check this link out:
http://support.microsoft.com/default...b;en-us;305039
to use this expand replication monitor, expand the agents folder, expand the
merge agents folder, right click on your merge agent, select merge agent
properties, select steps, double click on Run Agent.
Click on the commands text box, press the end key, press the space key, and
enter -ForceConvergenceLevel and then the level you wish.
Then click apply, ok, apply, ok. Then right click again on your merge agent
and start it up.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JorgenD" <JorgenD@.discussions.microsoft.com> wrote in message
news:ED4B5EE0-8FFD-40D1-8C44-FA295EAB3EFB@.microsoft.com...
> Hi,
> There is a undocumented Merge Agent parameter (-ForceConvergenceLevel)
that
> could solve some of my troubles, but where do I specify it?
> It's not available in the the profile setup for the agent in the
Enterprise
> manager.
> It's possible to specify it on the command line to replmerg.exe, but I
want
> it to be effektive whenever my subscribers synchronizes.
> Regards
> Jorgen D:
>
|||Thanks for your reply Hilary,
The problem is that my subscribers are anonymous and disconnected (SQL CE on
PDAs), so first of all I have 100+ merge agents and second, properties are
not available since they are not connected. Clicking Properties returns a
"SQL server not found"-error
When the subscriber connects and starts the merge process (pull
subsctiption) somhow the agent wakes up. But where is that "trigger"/"command
line"?
"Hilary Cotter" wrote:
> check this link out:
> http://support.microsoft.com/default...b;en-us;305039
> to use this expand replication monitor, expand the agents folder, expand the
> merge agents folder, right click on your merge agent, select merge agent
> properties, select steps, double click on Run Agent.
> Click on the commands text box, press the end key, press the space key, and
> enter -ForceConvergenceLevel and then the level you wish.
> Then click apply, ok, apply, ok. Then right click again on your merge agent
> and start it up.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "JorgenD" <JorgenD@.discussions.microsoft.com> wrote in message
> news:ED4B5EE0-8FFD-40D1-8C44-FA295EAB3EFB@.microsoft.com...
> that
> Enterprise
> want
>
>
There is a undocumented Merge Agent parameter (-ForceConvergenceLevel) that
could solve some of my troubles, but where do I specify it?
It's not available in the the profile setup for the agent in the Enterprise
manager.
It's possible to specify it on the command line to replmerg.exe, but I want
it to be effektive whenever my subscribers synchronizes.
Regards
Jorgen D:
check this link out:
http://support.microsoft.com/default...b;en-us;305039
to use this expand replication monitor, expand the agents folder, expand the
merge agents folder, right click on your merge agent, select merge agent
properties, select steps, double click on Run Agent.
Click on the commands text box, press the end key, press the space key, and
enter -ForceConvergenceLevel and then the level you wish.
Then click apply, ok, apply, ok. Then right click again on your merge agent
and start it up.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JorgenD" <JorgenD@.discussions.microsoft.com> wrote in message
news:ED4B5EE0-8FFD-40D1-8C44-FA295EAB3EFB@.microsoft.com...
> Hi,
> There is a undocumented Merge Agent parameter (-ForceConvergenceLevel)
that
> could solve some of my troubles, but where do I specify it?
> It's not available in the the profile setup for the agent in the
Enterprise
> manager.
> It's possible to specify it on the command line to replmerg.exe, but I
want
> it to be effektive whenever my subscribers synchronizes.
> Regards
> Jorgen D:
>
|||Thanks for your reply Hilary,
The problem is that my subscribers are anonymous and disconnected (SQL CE on
PDAs), so first of all I have 100+ merge agents and second, properties are
not available since they are not connected. Clicking Properties returns a
"SQL server not found"-error
When the subscriber connects and starts the merge process (pull
subsctiption) somhow the agent wakes up. But where is that "trigger"/"command
line"?
"Hilary Cotter" wrote:
> check this link out:
> http://support.microsoft.com/default...b;en-us;305039
> to use this expand replication monitor, expand the agents folder, expand the
> merge agents folder, right click on your merge agent, select merge agent
> properties, select steps, double click on Run Agent.
> Click on the commands text box, press the end key, press the space key, and
> enter -ForceConvergenceLevel and then the level you wish.
> Then click apply, ok, apply, ok. Then right click again on your merge agent
> and start it up.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "JorgenD" <JorgenD@.discussions.microsoft.com> wrote in message
> news:ED4B5EE0-8FFD-40D1-8C44-FA295EAB3EFB@.microsoft.com...
> that
> Enterprise
> want
>
>
Monday, March 19, 2012
Force invalidate snapshot - how?
Hi,
In SQL 2005 SP1 - In my merge replication RMO C# script, I am getting the error that the merge snapshot is obsolete:
Source: Merge Replication Provider
Number: -2147201021
Message: You must rerun snapshot because current snapshot files are obsolete.
I am new to SMO/RMO and C#. I presume that I need to set the force-invalidate flag somewhere before generating another snapshot. I'm guessing that I can "try/catch" the error and handle it then (am I correct?) My script is running each step synchronously including synchronizing the subscription synchronously - meaning: subscription.SynchronizationAgent.Synchronize();
I presume the error occurs when I synch the subscription and there is an existing, but obsolete, snapshot file.
I would really appreciate it if someone could show me how to set the snapshot force_invalidate flag in RMO (preferably in C#). A fallback might be to delete any existing merge snapshot files, but I presume that would take much more time than setting the switch and calling for another snapshot to be generated. I wish I had some code to look at to show me an example of handling this error. If you can point me to such an example or can help me get around this error, I thank you.
I do have the O'Reilly book - Programming Sql Server 2005, but am not aware of any code examples that pertain to this problem. Let me know if you know of one.
Thanks for any assistance you might be able to provide :-)
Paul
More -
I had assumed a snapshot had been generated and it hasn't. The question of a code example of how to force invalidate a snapshot is still of interest to me though.
|||I found it:
// If the pull subscription and the job exists, mark the subscription
// for reinitialization and start the agent job.
if (subscription.LoadProperties() && subscription.AgentJobId != null)
{
subscription.Reinitialize();
subscription.SynchronizeWithJob();
}
Force invalidate snapshot - how?
Hi,
In SQL 2005 SP1 - In my merge replication RMO C# script, I am getting the error that the merge snapshot is obsolete:
Source: Merge Replication Provider
Number: -2147201021
Message: You must rerun snapshot because current snapshot files are obsolete.
I am new to SMO/RMO and C#. I presume that I need to set the force-invalidate flag somewhere before generating another snapshot. I'm guessing that I can "try/catch" the error and handle it then (am I correct?) My script is running each step synchronously including synchronizing the subscription synchronously - meaning: subscription.SynchronizationAgent.Synchronize();
I presume the error occurs when I synch the subscription and there is an existing, but obsolete, snapshot file.
I would really appreciate it if someone could show me how to set the snapshot force_invalidate flag in RMO (preferably in C#). A fallback might be to delete any existing merge snapshot files, but I presume that would take much more time than setting the switch and calling for another snapshot to be generated. I wish I had some code to look at to show me an example of handling this error. If you can point me to such an example or can help me get around this error, I thank you.
I do have the O'Reilly book - Programming Sql Server 2005, but am not aware of any code examples that pertain to this problem. Let me know if you know of one.
Thanks for any assistance you might be able to provide :-)
Paul
More -
I had assumed a snapshot had been generated and it hasn't. The question of a code example of how to force invalidate a snapshot is still of interest to me though.
|||I found it:
// If the pull subscription and the job exists, mark the subscription
// for reinitialization and start the agent job.
if (subscription.LoadProperties() && subscription.AgentJobId != null)
{
subscription.Reinitialize();
subscription.SynchronizeWithJob();
}
Force Data - Merge Replication
I thought I could force data to be sent on the next merge by doing a simple
update statement on the rows I need sent.
Ran update statement at the subscriber
Update MyTable
SET A_Column = A_Column
This doesn't seem to work for rows that don't already exist on the
publisher.
Is there a system stored procedure that I can use at the subscriber to force
some data to the publisher?
Tina,
sp_mergedummyupdate should do it for you.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||The system stored procedure doesn't seem to force the rows to be sent
either.
I wrote the following since I had several hundred rows to force. I ran
the agent afer running the script but still no rows were inserted.
DECLARE @.guid uniqueidentifier
DECLARE @.SQL nvarchar(200)
DECLARE list_procs CURSOR FOR
SELECT row_guid FROM Ticket
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.guid
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'sp_mergedummyupdate @.source_object = ''Ticket''' +
',@.rowguid=''{' + CONVERT( varchar(36), @.guid ) + '}'''
--Print @.SQL
EXEC sp_executesql @.sql
FETCH NEXT FROM list_procs INTO @.guid
END
CLOSE list_procs
DEALLOCATE list_procs
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u$GZGwisEHA.904@.TK2MSFTNGP11.phx.gbl...
> Tina,
> sp_mergedummyupdate should do it for you.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Tina,
can you check (based on rowguid) to see if the records have entered
MSmerge_contents. Also, I don't want to state the obvious, but did you run
the merge agent afterwards?
Rgds,
Paul ibison (SQL Server MVP)
[vbcol=seagreen]
|||I figured out my problem. I found that the rowguids were in my
msmerge_tombstone table. Since I was using a push, the publisher could
never delete them from the subscriber but replication wasn't letting the
subscriber send these as new rows either.
Thanks for your time.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oy$1XUFtEHA.3052@.tk2msftngp13.phx.gbl...
> Tina,
> can you check (based on rowguid) to see if the records have entered
> MSmerge_contents. Also, I don't want to state the obvious, but did you run
> the merge agent afterwards?
> Rgds,
> Paul ibison (SQL Server MVP)
>
>
update statement on the rows I need sent.
Ran update statement at the subscriber
Update MyTable
SET A_Column = A_Column
This doesn't seem to work for rows that don't already exist on the
publisher.
Is there a system stored procedure that I can use at the subscriber to force
some data to the publisher?
Tina,
sp_mergedummyupdate should do it for you.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||The system stored procedure doesn't seem to force the rows to be sent
either.
I wrote the following since I had several hundred rows to force. I ran
the agent afer running the script but still no rows were inserted.
DECLARE @.guid uniqueidentifier
DECLARE @.SQL nvarchar(200)
DECLARE list_procs CURSOR FOR
SELECT row_guid FROM Ticket
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.guid
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'sp_mergedummyupdate @.source_object = ''Ticket''' +
',@.rowguid=''{' + CONVERT( varchar(36), @.guid ) + '}'''
--Print @.SQL
EXEC sp_executesql @.sql
FETCH NEXT FROM list_procs INTO @.guid
END
CLOSE list_procs
DEALLOCATE list_procs
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u$GZGwisEHA.904@.TK2MSFTNGP11.phx.gbl...
> Tina,
> sp_mergedummyupdate should do it for you.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Tina,
can you check (based on rowguid) to see if the records have entered
MSmerge_contents. Also, I don't want to state the obvious, but did you run
the merge agent afterwards?
Rgds,
Paul ibison (SQL Server MVP)
[vbcol=seagreen]
|||I figured out my problem. I found that the rowguids were in my
msmerge_tombstone table. Since I was using a push, the publisher could
never delete them from the subscriber but replication wasn't letting the
subscriber send these as new rows either.
Thanks for your time.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oy$1XUFtEHA.3052@.tk2msftngp13.phx.gbl...
> Tina,
> can you check (based on rowguid) to see if the records have entered
> MSmerge_contents. Also, I don't want to state the obvious, but did you run
> the merge agent afterwards?
> Rgds,
> Paul ibison (SQL Server MVP)
>
>
Monday, March 12, 2012
Force a push from the subscriber
I'm not too sure I follow this 
You have continuous merge replication set up and you want
it to go much faster? Changing it to a user-initiated,
non-continuous one won't speed it up. If I have
misinterpreted the question, and you just require user-
initiated replication, then you could use windows
synchronization manager and have pull subscriptions, or
program the merge activeX control to initiate the pull.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
No, I don't want it to go faster. I want the best of both worlds; to use
continuous merge replication (which polls about every 30 secs), AND
on-demand merge replication (triggered by the user on the client workstation
app).
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1eae01c51f07$ff630a00$a501280a@.phx.gbl...
> I'm not too sure I follow this
> You have continuous merge replication set up and you want
> it to go much faster? Changing it to a user-initiated,
> non-continuous one won't speed it up. If I have
> misinterpreted the question, and you just require user-
> initiated replication, then you could use windows
> synchronization manager and have pull subscriptions, or
> program the merge activeX control to initiate the pull.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I must be missing something
If there is continuous merge replication, what effect
would initiating the merge agent have? - it would give
the error message that another merge agent is running.
There again, what effect would you want it to have - if
it is running continuously, why (logically) would you
synchronize it on demand?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I know it sounds strange, but the main user of this app comes into the
office to synchronise his data with the corporate db, and then leaves. He is
impatient, and wants to initiate a merge himself. Because the merge is
transparent to him, there's no way for him to tell when the it has
completed.
I agree that he is *too* impatient, and if it's too hard, he'll just have to
live with it, but if we can give him the ability to initiate the merge
himself, why not?
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3a4f01c51fd3$65cafed0$a601280a@.phx.gbl...
>I must be missing something
> If there is continuous merge replication, what effect
> would initiating the merge agent have? - it would give
> the error message that another merge agent is running.
> There again, what effect would you want it to have - if
> it is running continuously, why (logically) would you
> synchronize it on demand?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Graham,
this sounds fine, but iun your first post you mentioned that ' continuous
push merge' was being used. Continuous and on-demand are mutually exclusive,
which is why I was/am confused. If it is not continuous and user-initiated
synchroniozation is what you need, then windows synchronization manager or
the activex controls coded in a GUI will do the trick.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, I understand now. I wasn't aware that they were mutually exclusive. I'll
leave it in continuous mode (he'll just have to live with it).
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uhTFaOKIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Graham,
> this sounds fine, but iun your first post you mentioned that ' continuous
> push merge' was being used. Continuous and on-demand are mutually
> exclusive, which is why I was/am confused. If it is not continuous and
> user-initiated synchroniozation is what you need, then windows
> synchronization manager or the activex controls coded in a GUI will do the
> trick.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Oh, forgive me. I rudely forgot to say "thanks".
Thanks Paul.
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uhTFaOKIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Graham,
> this sounds fine, but iun your first post you mentioned that ' continuous
> push merge' was being used. Continuous and on-demand are mutually
> exclusive, which is why I was/am confused. If it is not continuous and
> user-initiated synchroniozation is what you need, then windows
> synchronization manager or the activex controls coded in a GUI will do the
> trick.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
You have continuous merge replication set up and you want
it to go much faster? Changing it to a user-initiated,
non-continuous one won't speed it up. If I have
misinterpreted the question, and you just require user-
initiated replication, then you could use windows
synchronization manager and have pull subscriptions, or
program the merge activeX control to initiate the pull.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul,
No, I don't want it to go faster. I want the best of both worlds; to use
continuous merge replication (which polls about every 30 secs), AND
on-demand merge replication (triggered by the user on the client workstation
app).
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1eae01c51f07$ff630a00$a501280a@.phx.gbl...
> I'm not too sure I follow this
> You have continuous merge replication set up and you want
> it to go much faster? Changing it to a user-initiated,
> non-continuous one won't speed it up. If I have
> misinterpreted the question, and you just require user-
> initiated replication, then you could use windows
> synchronization manager and have pull subscriptions, or
> program the merge activeX control to initiate the pull.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I must be missing something
If there is continuous merge replication, what effect
would initiating the merge agent have? - it would give
the error message that another merge agent is running.
There again, what effect would you want it to have - if
it is running continuously, why (logically) would you
synchronize it on demand?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I know it sounds strange, but the main user of this app comes into the
office to synchronise his data with the corporate db, and then leaves. He is
impatient, and wants to initiate a merge himself. Because the merge is
transparent to him, there's no way for him to tell when the it has
completed.
I agree that he is *too* impatient, and if it's too hard, he'll just have to
live with it, but if we can give him the ability to initiate the merge
himself, why not?
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3a4f01c51fd3$65cafed0$a601280a@.phx.gbl...
>I must be missing something
> If there is continuous merge replication, what effect
> would initiating the merge agent have? - it would give
> the error message that another merge agent is running.
> There again, what effect would you want it to have - if
> it is running continuously, why (logically) would you
> synchronize it on demand?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Graham,
this sounds fine, but iun your first post you mentioned that ' continuous
push merge' was being used. Continuous and on-demand are mutually exclusive,
which is why I was/am confused. If it is not continuous and user-initiated
synchroniozation is what you need, then windows synchronization manager or
the activex controls coded in a GUI will do the trick.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, I understand now. I wasn't aware that they were mutually exclusive. I'll
leave it in continuous mode (he'll just have to live with it).
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uhTFaOKIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Graham,
> this sounds fine, but iun your first post you mentioned that ' continuous
> push merge' was being used. Continuous and on-demand are mutually
> exclusive, which is why I was/am confused. If it is not continuous and
> user-initiated synchroniozation is what you need, then windows
> synchronization manager or the activex controls coded in a GUI will do the
> trick.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Oh, forgive me. I rudely forgot to say "thanks".
Thanks Paul.
- Graham
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uhTFaOKIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Graham,
> this sounds fine, but iun your first post you mentioned that ' continuous
> push merge' was being used. Continuous and on-demand are mutually
> exclusive, which is why I was/am confused. If it is not continuous and
> user-initiated synchroniozation is what you need, then windows
> synchronization manager or the activex controls coded in a GUI will do the
> trick.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Subscribe to:
Posts (Atom)