Foreign in Snapshot Replication

When I initialized and started the snapshot agent, how can I move the table
schema along with all the foreign keys? The only way I can think of is add
the script for before and after the initialization.
Does that mean the Replication not support repicate the foreign keys?
If I have the foreign keys in each replicated table, is the transacational
replication smart enough to insert data into into the parent table first?
Thanks
Ed
Hi Ed,
You can replicate fks with the initial snapshot for transactional
replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
article schema option. There are, however a few things you need to watch out
for:
1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
case if you use concurrent snapshot, have circular references (including
self-referencing fk), or you have fks referencing from outside the
publication.
2) Even though transactional replication is suposed to replay what happened
at the publisher in the same order, an update on the primary key is
typically translated into deletes followed by inserts. This can lead to
temporary violation of fks referencing the pk and the only way to workaround
this is to make sure that your fks at the subcriber has the "NOT FOR
REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
a schema option to mark all replicated fks NFR when they are replicated to
the subscriber.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
> When I initialized and started the snapshot agent, how can I move the
> table
> schema along with all the foreign keys? The only way I can think of is
> add
> the script for before and after the initialization.
> Does that mean the Replication not support repicate the foreign keys?
> If I have the foreign keys in each replicated table, is the transacational
> replication smart enough to insert data into into the parent table first?
> Thanks
> Ed
|||In SQL 05, there is an option if I want to replicate the foreign keys. Do I
still have to include all table Dependencies? Replication is smart enough to
know which table/foreign key needs to be created first?
The reason why i ask the question is I have a Prod and a Dev Server.
Developers always ask me to transfer all tables only to the Dev Server. I am
thinking using snapshot / transactional replication to update tables/data for
them.
That's why I would like to make sure moving the tables along with the
foreign keys is good and the replication knows the sequence of creating the
keys.
Thanks
"Raymond Mak [MSFT]" wrote:

> Hi Ed,
> You can replicate fks with the initial snapshot for transactional
> replication from SQL2000 onwards by enabling the 0x200 (DriForiegnKeys)
> article schema option. There are, however a few things you need to watch out
> for:
> 1) Support on SQL2000 is admittedly a bit glitchy, this is especially the
> case if you use concurrent snapshot, have circular references (including
> self-referencing fk), or you have fks referencing from outside the
> publication.
> 2) Even though transactional replication is suposed to replay what happened
> at the publisher in the same order, an update on the primary key is
> typically translated into deletes followed by inserts. This can lead to
> temporary violation of fks referencing the pk and the only way to workaround
> this is to make sure that your fks at the subcriber has the "NOT FOR
> REPLICATION" (NFR) property on them. SQL2005 makes this easier by providing
> a schema option to mark all replicated fks NFR when they are replicated to
> the subscriber.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:83EE4199-8A99-4673-B999-CBF3C9151B78@.microsoft.com...
>
>
|||You don't need to include all objects referenced by fks in your publication,
and the fks should be created in the right order (they only have to be
created after the unique\primary keys that they reference). The snapshot
agent history will tell you which fks cannot be replicated because they
reference something not in the publication.
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...[vbcol=seagreen]
> In SQL 05, there is an option if I want to replicate the foreign keys. Do
> I
> still have to include all table Dependencies? Replication is smart enough
> to
> know which table/foreign key needs to be created first?
> The reason why i ask the question is I have a Prod and a Dev Server.
> Developers always ask me to transfer all tables only to the Dev Server. I
> am
> thinking using snapshot / transactional replication to update tables/data
> for
> them.
> That's why I would like to make sure moving the tables along with the
> foreign keys is good and the replication knows the sequence of creating
> the
> keys.
> Thanks
> "Raymond Mak [MSFT]" wrote:
|||Thanks.
While I am playing with the table "Customers" and "Orders" in Northwind
database in SQL 2000, when I checked on "Include declared referential
integrity", the foreign keys still not get copied. Am I missing something?
Ed
"Raymond Mak [MSFT]" wrote:

> You don't need to include all objects referenced by fks in your publication,
> and the fks should be created in the right order (they only have to be
> created after the unique\primary keys that they reference). The snapshot
> agent history will tell you which fks cannot be replicated because they
> reference something not in the publication.
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:3651674B-3142-462D-BD65-FE3D911591F7@.microsoft.com...
>
>
|||The Orders table in the Northwind database has FKs referencing the Employees
table and the Shippers table as well. In SQL2000, the snapshot agent will
simply not script any foreign key constraints from the referencing table if
*any* of the referenced tables is not part of the publication. The SQL2005
snapshot agent implements a much more granular approach of analyzing each
individual foreign key so the foreign key from Orders to Customers will be
replicated with the SQL2005 snapshot agent despite the fact that you didn't
include Employees and Shippers in your publication (you will even see in the
snapshot history messages why the FKs referencing Employees and Shippers are
not scripted). Ed, I got exactly the same question (down to the same tables)
from one of our support engineers just two days ago, did you contact
Microsoft Support for this?
-Raymond
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...[vbcol=seagreen]
> Thanks.
> While I am playing with the table "Customers" and "Orders" in Northwind
> database in SQL 2000, when I checked on "Include declared referential
> integrity", the foreign keys still not get copied. Am I missing
> something?
> Ed
> "Raymond Mak [MSFT]" wrote:
|||It was not me but I will play with SQL 05 to see the result.
Thanks again.
"Raymond Mak [MSFT]" wrote:

> The Orders table in the Northwind database has FKs referencing the Employees
> table and the Shippers table as well. In SQL2000, the snapshot agent will
> simply not script any foreign key constraints from the referencing table if
> *any* of the referenced tables is not part of the publication. The SQL2005
> snapshot agent implements a much more granular approach of analyzing each
> individual foreign key so the foreign key from Orders to Customers will be
> replicated with the SQL2005 snapshot agent despite the fact that you didn't
> include Employees and Shippers in your publication (you will even see in the
> snapshot history messages why the FKs referencing Employees and Shippers are
> not scripted). Ed, I got exactly the same question (down to the same tables)
> from one of our support engineers just two days ago, did you contact
> Microsoft Support for this?
> -Raymond
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:190B9FF0-EC95-45A1-9B7A-6D6C3713D32F@.microsoft.com...
>
>

Foregein Key data voilation in merge replication

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)

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...
>

Forcing resynchronization

I am doing testing on a database between a SQL Server and a laptop
usingmerge replication. I can see that each system is missing a record
in each system. When i run the validation process the system
identifies that the table are not in sync. Regardless of the
resynchronization method I use the two tables do not synchronize. What
am I doing wrong
This sort of thing can occur if the merge trigger hasn't fired. EG
(1) If you bulk insert the rows and choose the defaults, then FIRE_TRIGGERS
is false and consequently the rows are not added to MSmerge_contents.
(2) If you do a fast-load using the Transform Data task in DTS.
In these cases case, you need to run sp_addtabletocontents to include the
rows then resynchronise. Alternatively you can use sp_mergedummyupdate for a
single row. For the fast load case, in future if you deselect the check box
the triggers will fire.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

ForceConvergenceLevel again.

Hi.
I've asked about this before but didn't get an answer.
Due to problems in the replication with SQL CE devices I found that the
merge agen parameter "-ForceConvergenceLevel" probaly could help solving some
of those problems. But where to put it?
Normaly you would right-click the merge agent in the replication monitor,
and select agent properties, and specify the parameter under the run step.
But SQL CE devices is anonymous disconnected devices so you can't get the
agent properties, since you cant connect to the device/db.
Furthermore merge agents under the replication monitor is dynamicly created
when a anonymous subscriber is connecting so the parameters shuld be
specified as defaults under Agent Profiles (Right click- replication folder,
select Configure Publishing, Sub..., Agent Profiles, Merge Tab), but here
you can only set a subset of the parameters.
So how du I use the -ForceConvergenceLevel parameter with anonymous merge
subscribtions?
You can't configure these properties using the ActiveX controls or SQL CE.
I strongly suggest you contact Microsoft PSS on this question.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JorgenD" <JorgenD@.discussions.microsoft.com> wrote in message
news:31D8904E-D094-4420-85F6-455E7F81DACF@.microsoft.com...
> Hi.
> I've asked about this before but didn't get an answer.
> Due to problems in the replication with SQL CE devices I found that the
> merge agen parameter "-ForceConvergenceLevel" probaly could help solving
some
> of those problems. But where to put it?
> Normaly you would right-click the merge agent in the replication monitor,
> and select agent properties, and specify the parameter under the run step.
> But SQL CE devices is anonymous disconnected devices so you can't get the
> agent properties, since you cant connect to the device/db.
> Furthermore merge agents under the replication monitor is dynamicly
created
> when a anonymous subscriber is connecting so the parameters shuld be
> specified as defaults under Agent Profiles (Right click- replication
folder,
> select Configure Publishing, Sub..., Agent Profiles, Merge Tab), but
here
> you can only set a subset of the parameters.
> So how du I use the -ForceConvergenceLevel parameter with anonymous merge
> subscribtions?
>

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)
>
>

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)
>

For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
NewishHi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
--
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
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:
> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and you
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly dropped
> when there are large waves that obscure the transmitter/receiver, and there
> can't be too many more demanding replication setups than this, yet the merge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
Newish
Hi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegro ups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>
|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
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
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegro ups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>
|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:

> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and you
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly dropped
> when there are large waves that obscure the transmitter/receiver, and there
> can't be too many more demanding replication setups than this, yet the merge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

For replication gurus

Hi
I dont want to use built in replication mechanism in sql 2005.
Reason:
Two servers that need to be synchronised, 95% from A to B and 5% from B
to A, are located in two different cities and the internet connection
is not vey reliable.
Is there an alternative.
I was thinking of monitoring all changes by datestimetamp and then
prepare a huge composite sql statement for the changes and email this
text file to the other server where it would be executed. This would
be done once everyday.
If someone has a better solution i will really appreciate to know the
details.
Thanks in advance.
NewishHi
Well , have you looked into linked servers in the BOL?
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||This will work for updates and inserts, but what about your deletes?
If you are trying to merge data from two servers where the data is in the
same tables you will need to do some sort of auditing based on PK info. IE
an audit table for each table you are merging which contains the pk of the
row modified an indicator as to whether it is an update, insert or delete
and then use triggers to write to these audit tables. Then you need to be
able to handle the condition where a row is updated on one side and deleted
on the other, as well as PK collisions.
It gets complex very quickly. Merge replication is designed for scenarios
like this.
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
"Newish" <ahussain3@.gmail.com> wrote in message
news:1163517504.861790.68990@.m73g2000cwd.googlegroups.com...
> Hi
> I dont want to use built in replication mechanism in sql 2005.
> Reason:
> Two servers that need to be synchronised, 95% from A to B and 5% from B
> to A, are located in two different cities and the internet connection
> is not vey reliable.
> Is there an alternative.
> I was thinking of monitoring all changes by datestimetamp and then
> prepare a huge composite sql statement for the changes and email this
> text file to the other server where it would be executed. This would
> be done once everyday.
> If someone has a better solution i will really appreciate to know the
> details.
> Thanks in advance.
> Newish
>|||I agree with Hilary.
BTW merge replication is resilient to the connection being dropped, and you
can optimise the profile for this occasion.
I have replicated to ships where the satellite comms are repeatedly dropped
when there are large waves that obscure the transmitter/receiver, and there
can't be too many more demanding replication setups than this, yet the merge
synchronization proved robust.
Alternative solutions eg DataCompare are available, but you'd have to
program much of the functionality that comes for free with merge
replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks for your responses.
Exactly what happens if the line drops. Is everything rolled back. Is
the merge transaction based.
Regards
Newish
Paul Ibison wrote:

> I agree with Hilary.
> BTW merge replication is resilient to the connection being dropped, and yo
u
> can optimise the profile for this occasion.
> I have replicated to ships where the satellite comms are repeatedly droppe
d
> when there are large waves that obscure the transmitter/receiver, and ther
e
> can't be too many more demanding replication setups than this, yet the mer
ge
> synchronization proved robust.
> Alternative solutions eg DataCompare are available, but you'd have to
> program much of the functionality that comes for free with merge
> replication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||The transaction for processing the current row will get rolled back, but the
unprocessed changes in the batch will get reapplied when the merge agent
next runs.
This'll change to encompass >1 row if you are using logical records.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .