I have two MSDE servers on different machines and am
trying to set up merge replication between the two. When
the merge agent runs on the publisher, it indicates that
the "process could not connect to the Subscriber" (SQL
Server does not exist or access denied).
I'm not sure if matters, but I'm running in a peer-to-
peer network. I have my SQL Agent service on the
publisher running under a local machine account, and that
account has access to the publisher, however I wasn't
sure how to grant access to that account on the
Subscriber, or if that was even necessary.
Any help would be appreciated.
- Jim T.
Jim
I am not positive, but I think that we ran into this problem as well. I
beleive that the problem is that the local machine account on the publisher
(or subscriber) can't authenticate with the other machine which means that
the SQL Server Agent can't connect to the other machine to make replication
happen. If I remember correctly, we had to have a non-local (domain)
account that each domain trusts and each machine allows access to the
database on. I'm not sure, but I think it may also be possible to have
local accounts as long as both the username and password are the same on
both machines. I'm pretty sure that there is a KB article titled somthing
like "How to run replication accross non-trusted domains" that may help. If
you need to change the SQL Agent's service acount, take a look at KB article
283811. Sorry to be so vague, but I hope it points you in the right
direction.
HTH
Ron L
"Jim Toth" <anonymous@.discussions.microsoft.com> wrote in message
news:14b301c4b3e6$5d41a650$a401280a@.phx.gbl...
>I have two MSDE servers on different machines and am
> trying to set up merge replication between the two. When
> the merge agent runs on the publisher, it indicates that
> the "process could not connect to the Subscriber" (SQL
> Server does not exist or access denied).
> I'm not sure if matters, but I'm running in a peer-to-
> peer network. I have my SQL Agent service on the
> publisher running under a local machine account, and that
> account has access to the publisher, however I wasn't
> sure how to grant access to that account on the
> Subscriber, or if that was even necessary.
> Any help would be appreciated.
> - Jim T.
Showing posts with label runs. Show all posts
Showing posts with label runs. Show all posts
Wednesday, March 28, 2012
merge replication with large amounts of updates at publisher
I have merge repl setup and it has been running with no problem... until a
scheduled task that runs once every night and updates a very large amount of
records was introduced. Merge repl is scheduled to run every hour. Now the
merge attempt directly following the nightly task that updates a large
amount of udates fails, and continues to fail every hour. It eventually does
succeed sometime in the evening. The scheduled task and initial repl failure
occur around 4am eastern time. I would think that would be a somewhat low
traffic time on the net... maybe not. It usually succeeds sometime between 6
and 8 pm eastern time.
1) when a large amount of updates occur, is every record in its entirety
sent to the subscriber? or does the merge repl system automatically package
things into more effecient jobs of some sort?
2) when repl does finally succeed it reports taking over 1 hour. Since my
schedule is every hour could that be part of, or THE problem? meaning a
merge job is scheduled to start but the last one is still running? could
that be why its failing?
3) could any concurrency/locking type issues occur at the subscriber while
the merge agent is operating? especially since it is continually failing and
retrying and failing and retrying?
4) is 50,000 records just way to many to expect to be able to merge? (over
the internet)
any info is greatly appreciated. Thanks.
1) replication metadata is compared to determine which row(s) need
updating/deleting/inserting. Basically the way it works is that you have a
rowguid which identifies each row. If a row is modified triggers write the
rowguid and some tracking information to the msmerge_contents table.
The merge agent will read all rows in the msmerge_contents table have been
added since the last time it ran. It will then compare the version
(generation) of this row with the version of the row on the
publisher/subscriber. The publisher/subscriber with the latest generation is
determined to be the winner, and then the source table on the publisher or
subscriber is consulted and the data values are extracted, and a
insert/update/delete statement is fired on the subscriber/publisher.
So initially its just the rowguid and version information which travels
across the wire, and then it could be the row itself.
2) I think you would benefit by setting QueryTimeOut to something large, it
600. You can set this by right clicking on your merge agent, selecting agent
properties, clicking steps, click run agent, and then in the commands
section, click edit, and look for -QueryTimeOut and set it to 600. It its
not there add it. The merge agent will run for a predetermined time and then
may fail. Sometimes it will take 3 or 4 times before it completely processes
all transactions. Many DBA wrap their merge agents in infinite loops. The
job scheduler for SQL Server will not run two simultaneous instances of the
same merge agent.
3) Its possible. Normally you will get a deadlock message in your merge
agent or msrepl_errors. Normally you run into locking/concurrency problems
when you have a large number of merge agents running simultaneously. There
is a setting to limit the number of concurrent merge agents. Right click on
your publication, select publication properties, and then click the
subscriber tab. Many DBAs will stagger their merge agents so they don't run
simultaneously. For instance they will schedule them to run every 17 minutes
or so. There is nothing magic about 17, its just a larger prime number.
4) Not at all, but this depends on hardware of the publisher and subscriber
and the bandwidth available. You might benefit from the slow link profile if
you have low bandwidth.
"djc" <noone@.nowhere.com> wrote in message
news:OV931hVrEHA.332@.TK2MSFTNGP14.phx.gbl...
>I have merge repl setup and it has been running with no problem... until a
> scheduled task that runs once every night and updates a very large amount
> of
> records was introduced. Merge repl is scheduled to run every hour. Now the
> merge attempt directly following the nightly task that updates a large
> amount of udates fails, and continues to fail every hour. It eventually
> does
> succeed sometime in the evening. The scheduled task and initial repl
> failure
> occur around 4am eastern time. I would think that would be a somewhat low
> traffic time on the net... maybe not. It usually succeeds sometime between
> 6
> and 8 pm eastern time.
> 1) when a large amount of updates occur, is every record in its entirety
> sent to the subscriber? or does the merge repl system automatically
> package
> things into more effecient jobs of some sort?
> 2) when repl does finally succeed it reports taking over 1 hour. Since my
> schedule is every hour could that be part of, or THE problem? meaning a
> merge job is scheduled to start but the last one is still running? could
> that be why its failing?
> 3) could any concurrency/locking type issues occur at the subscriber while
> the merge agent is operating? especially since it is continually failing
> and
> retrying and failing and retrying?
> 4) is 50,000 records just way to many to expect to be able to merge? (over
> the internet)
> any info is greatly appreciated. Thanks.
>
|||Thanks Hilary.
1) You suggested adding the QueryTimeOut setting directly to the command in
the agent. I just want to verify that the dialog box returned from right
clicking on the merge agent (from the replication monitor) and choosing
Agent Profile allows you to do the same thing. The dialog has several
template profiles like the Slow Link one you mentioned and others. I have
played with the QueryTimeOut value from there. Just want to make sure thats
ok as well since you suggested adding this option to the command itself..
any difference?
2) "The merge agent will run for a predetermined time and then may fail.
Sometimes it will take 3 or 4 times before it completely processes all
transactions. Many DBA wrap their merge agents in infinite loops. The job
scheduler for SQL Server will not run two simultaneous instances of the same
merge agent."
Well, wrapping the agents in an infinite loop is beyond my current skill
level although if it comes to that then I will have to learn. When you said
that the agent will run for a predetermined time and then fail. Sometimes 3
or 4 times before it completely processes all transactions... after failing
does it pick up where it left off? or does it start all over again? If it
picks up where it left off does that mean that its possible that out of a
50,000 record merge job that takes 6 times to complete that the records
updated during each of the failed attempts are committed before the final
completion of the whole 50,000 job?
3) In Books Online I read about the MaxUploadChanges and MaxDownloadChanges.
It said you can set a limit on the number of records updated per merge agent
session. But it didn't say what happens if you hit that limit. Lets say you
set a limit to 2500 records but you have 30,000 records that need to be
merged. After is stops at 2500 will the remaining changes still be processed
2500 at a time during subsequesnt merge agent runs? If so this may allow me
to split up my 1 time large update into smaller chunks since it only happens
once over night?
thanks again for all your help. It is very much appreciated.
-djc
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Ov2WLwXrEHA.1204@.TK2MSFTNGP12.phx.gbl...
> 1) replication metadata is compared to determine which row(s) need
> updating/deleting/inserting. Basically the way it works is that you have
a
> rowguid which identifies each row. If a row is modified triggers write the
> rowguid and some tracking information to the msmerge_contents table.
> The merge agent will read all rows in the msmerge_contents table have been
> added since the last time it ran. It will then compare the version
> (generation) of this row with the version of the row on the
> publisher/subscriber. The publisher/subscriber with the latest generation
is
> determined to be the winner, and then the source table on the publisher or
> subscriber is consulted and the data values are extracted, and a
> insert/update/delete statement is fired on the subscriber/publisher.
> So initially its just the rowguid and version information which travels
> across the wire, and then it could be the row itself.
> 2) I think you would benefit by setting QueryTimeOut to something large,
it
> 600. You can set this by right clicking on your merge agent, selecting
agent
> properties, clicking steps, click run agent, and then in the commands
> section, click edit, and look for -QueryTimeOut and set it to 600. It its
> not there add it. The merge agent will run for a predetermined time and
then
> may fail. Sometimes it will take 3 or 4 times before it completely
processes
> all transactions. Many DBA wrap their merge agents in infinite loops. The
> job scheduler for SQL Server will not run two simultaneous instances of
the
> same merge agent.
> 3) Its possible. Normally you will get a deadlock message in your merge
> agent or msrepl_errors. Normally you run into locking/concurrency problems
> when you have a large number of merge agents running simultaneously. There
> is a setting to limit the number of concurrent merge agents. Right click
on
> your publication, select publication properties, and then click the
> subscriber tab. Many DBAs will stagger their merge agents so they don't
run
> simultaneously. For instance they will schedule them to run every 17
minutes
> or so. There is nothing magic about 17, its just a larger prime number.
> 4) Not at all, but this depends on hardware of the publisher and
subscriber
> and the bandwidth available. You might benefit from the slow link profile
if[vbcol=seagreen]
> you have low bandwidth.
>
> "djc" <noone@.nowhere.com> wrote in message
> news:OV931hVrEHA.332@.TK2MSFTNGP14.phx.gbl...
a[vbcol=seagreen]
amount[vbcol=seagreen]
the[vbcol=seagreen]
low[vbcol=seagreen]
between[vbcol=seagreen]
my[vbcol=seagreen]
while[vbcol=seagreen]
(over
>
sql
scheduled task that runs once every night and updates a very large amount of
records was introduced. Merge repl is scheduled to run every hour. Now the
merge attempt directly following the nightly task that updates a large
amount of udates fails, and continues to fail every hour. It eventually does
succeed sometime in the evening. The scheduled task and initial repl failure
occur around 4am eastern time. I would think that would be a somewhat low
traffic time on the net... maybe not. It usually succeeds sometime between 6
and 8 pm eastern time.
1) when a large amount of updates occur, is every record in its entirety
sent to the subscriber? or does the merge repl system automatically package
things into more effecient jobs of some sort?
2) when repl does finally succeed it reports taking over 1 hour. Since my
schedule is every hour could that be part of, or THE problem? meaning a
merge job is scheduled to start but the last one is still running? could
that be why its failing?
3) could any concurrency/locking type issues occur at the subscriber while
the merge agent is operating? especially since it is continually failing and
retrying and failing and retrying?
4) is 50,000 records just way to many to expect to be able to merge? (over
the internet)
any info is greatly appreciated. Thanks.
1) replication metadata is compared to determine which row(s) need
updating/deleting/inserting. Basically the way it works is that you have a
rowguid which identifies each row. If a row is modified triggers write the
rowguid and some tracking information to the msmerge_contents table.
The merge agent will read all rows in the msmerge_contents table have been
added since the last time it ran. It will then compare the version
(generation) of this row with the version of the row on the
publisher/subscriber. The publisher/subscriber with the latest generation is
determined to be the winner, and then the source table on the publisher or
subscriber is consulted and the data values are extracted, and a
insert/update/delete statement is fired on the subscriber/publisher.
So initially its just the rowguid and version information which travels
across the wire, and then it could be the row itself.
2) I think you would benefit by setting QueryTimeOut to something large, it
600. You can set this by right clicking on your merge agent, selecting agent
properties, clicking steps, click run agent, and then in the commands
section, click edit, and look for -QueryTimeOut and set it to 600. It its
not there add it. The merge agent will run for a predetermined time and then
may fail. Sometimes it will take 3 or 4 times before it completely processes
all transactions. Many DBA wrap their merge agents in infinite loops. The
job scheduler for SQL Server will not run two simultaneous instances of the
same merge agent.
3) Its possible. Normally you will get a deadlock message in your merge
agent or msrepl_errors. Normally you run into locking/concurrency problems
when you have a large number of merge agents running simultaneously. There
is a setting to limit the number of concurrent merge agents. Right click on
your publication, select publication properties, and then click the
subscriber tab. Many DBAs will stagger their merge agents so they don't run
simultaneously. For instance they will schedule them to run every 17 minutes
or so. There is nothing magic about 17, its just a larger prime number.
4) Not at all, but this depends on hardware of the publisher and subscriber
and the bandwidth available. You might benefit from the slow link profile if
you have low bandwidth.
"djc" <noone@.nowhere.com> wrote in message
news:OV931hVrEHA.332@.TK2MSFTNGP14.phx.gbl...
>I have merge repl setup and it has been running with no problem... until a
> scheduled task that runs once every night and updates a very large amount
> of
> records was introduced. Merge repl is scheduled to run every hour. Now the
> merge attempt directly following the nightly task that updates a large
> amount of udates fails, and continues to fail every hour. It eventually
> does
> succeed sometime in the evening. The scheduled task and initial repl
> failure
> occur around 4am eastern time. I would think that would be a somewhat low
> traffic time on the net... maybe not. It usually succeeds sometime between
> 6
> and 8 pm eastern time.
> 1) when a large amount of updates occur, is every record in its entirety
> sent to the subscriber? or does the merge repl system automatically
> package
> things into more effecient jobs of some sort?
> 2) when repl does finally succeed it reports taking over 1 hour. Since my
> schedule is every hour could that be part of, or THE problem? meaning a
> merge job is scheduled to start but the last one is still running? could
> that be why its failing?
> 3) could any concurrency/locking type issues occur at the subscriber while
> the merge agent is operating? especially since it is continually failing
> and
> retrying and failing and retrying?
> 4) is 50,000 records just way to many to expect to be able to merge? (over
> the internet)
> any info is greatly appreciated. Thanks.
>
|||Thanks Hilary.
1) You suggested adding the QueryTimeOut setting directly to the command in
the agent. I just want to verify that the dialog box returned from right
clicking on the merge agent (from the replication monitor) and choosing
Agent Profile allows you to do the same thing. The dialog has several
template profiles like the Slow Link one you mentioned and others. I have
played with the QueryTimeOut value from there. Just want to make sure thats
ok as well since you suggested adding this option to the command itself..
any difference?
2) "The merge agent will run for a predetermined time and then may fail.
Sometimes it will take 3 or 4 times before it completely processes all
transactions. Many DBA wrap their merge agents in infinite loops. The job
scheduler for SQL Server will not run two simultaneous instances of the same
merge agent."
Well, wrapping the agents in an infinite loop is beyond my current skill
level although if it comes to that then I will have to learn. When you said
that the agent will run for a predetermined time and then fail. Sometimes 3
or 4 times before it completely processes all transactions... after failing
does it pick up where it left off? or does it start all over again? If it
picks up where it left off does that mean that its possible that out of a
50,000 record merge job that takes 6 times to complete that the records
updated during each of the failed attempts are committed before the final
completion of the whole 50,000 job?
3) In Books Online I read about the MaxUploadChanges and MaxDownloadChanges.
It said you can set a limit on the number of records updated per merge agent
session. But it didn't say what happens if you hit that limit. Lets say you
set a limit to 2500 records but you have 30,000 records that need to be
merged. After is stops at 2500 will the remaining changes still be processed
2500 at a time during subsequesnt merge agent runs? If so this may allow me
to split up my 1 time large update into smaller chunks since it only happens
once over night?
thanks again for all your help. It is very much appreciated.
-djc
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Ov2WLwXrEHA.1204@.TK2MSFTNGP12.phx.gbl...
> 1) replication metadata is compared to determine which row(s) need
> updating/deleting/inserting. Basically the way it works is that you have
a
> rowguid which identifies each row. If a row is modified triggers write the
> rowguid and some tracking information to the msmerge_contents table.
> The merge agent will read all rows in the msmerge_contents table have been
> added since the last time it ran. It will then compare the version
> (generation) of this row with the version of the row on the
> publisher/subscriber. The publisher/subscriber with the latest generation
is
> determined to be the winner, and then the source table on the publisher or
> subscriber is consulted and the data values are extracted, and a
> insert/update/delete statement is fired on the subscriber/publisher.
> So initially its just the rowguid and version information which travels
> across the wire, and then it could be the row itself.
> 2) I think you would benefit by setting QueryTimeOut to something large,
it
> 600. You can set this by right clicking on your merge agent, selecting
agent
> properties, clicking steps, click run agent, and then in the commands
> section, click edit, and look for -QueryTimeOut and set it to 600. It its
> not there add it. The merge agent will run for a predetermined time and
then
> may fail. Sometimes it will take 3 or 4 times before it completely
processes
> all transactions. Many DBA wrap their merge agents in infinite loops. The
> job scheduler for SQL Server will not run two simultaneous instances of
the
> same merge agent.
> 3) Its possible. Normally you will get a deadlock message in your merge
> agent or msrepl_errors. Normally you run into locking/concurrency problems
> when you have a large number of merge agents running simultaneously. There
> is a setting to limit the number of concurrent merge agents. Right click
on
> your publication, select publication properties, and then click the
> subscriber tab. Many DBAs will stagger their merge agents so they don't
run
> simultaneously. For instance they will schedule them to run every 17
minutes
> or so. There is nothing magic about 17, its just a larger prime number.
> 4) Not at all, but this depends on hardware of the publisher and
subscriber
> and the bandwidth available. You might benefit from the slow link profile
if[vbcol=seagreen]
> you have low bandwidth.
>
> "djc" <noone@.nowhere.com> wrote in message
> news:OV931hVrEHA.332@.TK2MSFTNGP14.phx.gbl...
a[vbcol=seagreen]
amount[vbcol=seagreen]
the[vbcol=seagreen]
low[vbcol=seagreen]
between[vbcol=seagreen]
my[vbcol=seagreen]
while[vbcol=seagreen]
(over
>
sql
Monday, March 26, 2012
Merge Replication using SQL-DMO
I am in a process of learning Replication in MSDE, especially Merge Replication
Server runs on MS-XP Professional
------------
I have a sample Access project 'ReplTest' which has only table with only 2 columns.
DatabaseName : ReplTestDB
Table Name : TestTable
MSDE Instance Name : SVR\MYINSTANCE
Now I would like to know how I can configure this database for merge replication
using SQL-DMO
Laptop runs on MS-XP Professional
------------
I have another access project which is running on computer 2 and connected to the
ReplicaTest database.
MSDE Instance Name : LPT\MYINSTANCE
My task is, when I am disconnected from server I would like to have a local copy of
the database to work with and then, when reconnected, need synchronization with the
server database and continue working from server database.
How to write this replication process from scratch using SQL-DMO objects in both Server computer
and Laptop computer.
I dont have enterprise manager in both computers since they use only MSDE
Can anyone help me?
Thanks in advance
JPCheck out "Publishers, Distributors, and Subscribers" from BOL (Ihope you have BOL).|||The problem with BOL is it is so extensive and the scenario of my situation is not described. I could do replication in the same machine as distributor, publisher and suscriber, but what if the subscriber is another machine as in my case ?|||I found this within these forums:
The sample code you were after is for creating heterogenous publication. It does not work with SQL publications. Try to model after the following sample.
Sub CreateMergePub() Dim osvr As New SQLDMO.SQLServer Dim szLastErrorText As String
Dim lLastErrorNumber As Long
Dim szServerName As String
Dim oReplicationDatabase As SQLDMO.ReplicationDatabase
Dim oDistPublisher As New DistributionPublisher
Dim oMergePublication As New MergePublication
Dim oMergeArticle1 As New SQLDMO.MergeArticle
On Error GoTo ErrorHandler
szServerName = "your servername"
' To use NT integrated security ' osvr.LoginSecure = True
osvr.Connect szServerName
' Enable pubs database for publishing ' Set oReplicationDatabase =
osvr.Replication.ReplicationDatabases("pubs")
'oReplicationDatabase.EnableTransPublishing = True
' Set MergePublication properties, name, snapshot method, trans '
oMergePublication.Name = "Pub1" oMergePublication.SnapshotMethod =
SQLDMOInitSync_BCPNative
' Set non-default properties, i.e. if you do not set the following properties, '
default value will be used ' 'oMergePublication.PublicationAttributes =
SQLDMOPubAttrib_AllowPull Or SQLDMOPubAttrib_AllowAnonymous
'oMergePublication.RetentionPeriod = 64
'oMergePublication.SnapshotSchedule.FrequencyType = SQLDMOFreq_Daily
'oMergePublication.SnapshotSchedule.FrequencyInter val = 1
'oMergePublication.SnapshotSchedule.ActiveStartTim eOfDay = 233000
'oMergePublication.SnapshotSchedule.ActiveEndTimeO fDay = 235959
'oMergePublication.SnapshotSchedule.ActiveStartDat e = 0
'oMergePublication.SnapshotSchedule.ActiveEndDate = 19981205
oReplicationDatabase.MergePublications.Add oMergePublication
' Add article ' oMergeArticle1.Name = "Article1" oMergeArticle1.SourceObjectName
= "jobs" oMergeArticle1.SourceObjectOwner = "dbo"
'Add the article to the publication ' oMergePublication.MergeArticles.Add
oMergeArticle1
' Disconnect from SQL Server szServerName ' osvr.DisConnect Set osvr =
Nothing End
ErrorHandler:
szLastErrorText = Err.Description lLastErrorNumber = Err.Number
MsgBox szLastErrorText, vbOKOnly, "Error " + Trim$(Str$(Err.Number))
End Sub
--
Pung Xu, Microsoft This posting is provided "AS IS" with no warranties, and confers
no rights. Please do not send email directly to this alias. This alias is for
newsgroup purposes only.
"matt" <matt_e_davis@.hotmail.com> wrote in message
news:d0db2ad5.0205221240.7c427ed7@.posting.google.c om...
> I am having trouble creating a merge publication with SQL-DMO. I modeled my
> solution after the following code:
> --================================================ Sub CreatePublication() '
> Connect to SQL Server Distributor Dim oSqlServer As New SQLDMO.SQLServer
> oSqlServer.Connect "", "sa", "" On Error GoTo ErrorHandler
>
> Dim oSamplePublisher As DistributionPublisher Set oSamplePublisher =
>
oSqlServer.Replication.Distributor.DistributionPub lishers("SAMPLEPUBLISHER")
>
> ' Create Sample Publication Dim oSamplePublication As New
> DistributionPublication oSamplePublication.Name = "SamplePublication"
> oSamplePublication.PublicationDB = "SampleDatabase"
> oSamplePublication.PublicationType = SQLDMOPublication_Transactional
> oSamplePublication.VendorName = "Sample Vendor"
> oSamplePublication.LogReaderAgent = "SampleLogReaderAgent"
> oSamplePublication.SnapshotAgent = "SampleSnapShotAgent"
> oSamplePublication.Description = "Sample Publication Definition"
> oSamplePublication.PublicationAttributes = SQLDMOPubAttrib_AllowPush
>
> ' Add the Publication oSamplePublisher.DistributionPublications.Add
> oSamplePublication
>
> ' Create Sample Articles Dim oSampleArticle1 As New DistributionArticle
> oSampleArticle1.Name = "SampleArticle1" oSampleArticle1.Description = "Sampe
> Article1 Definition" oSampleArticle1.SourceObjectName = "SampleTable1"
>
> Dim oSampleArticle2 As New DistributionArticle oSampleArticle2.Name =
> "SampleArticle2" oSampleArticle2.Description = "Sample Article2 Definition"
> oSampleArticle2.SourceObjectName = "SampleTable2"
>
> ' Add the Articles to the Publication
> oSamplePublication.DistributionArticles.Add oSampleArticle2
> oSamplePublication.DistributionArticles.Add oSampleArticle1
>
> oSqlServer.Close Exit Sub ErrorHandler: PrintErrors oSqlServer Exit Sub End Sub
>
> --================================================ This does create a publication
> but when you right click (via Enterprise Mgr.) Replication Monitor > PublisherName
> > SamplePublication and select Properties an error occurs. The error states that
> the publication is not in the TransPublication collection. The same error occurs
> when you create a merge publication. Except the error is that the publication does
> not exist in the MergePublications collection.
>
> How can I add the publication to the MergePublications collection?? Does anyone
> have any sample code for this?
>
> I am new at SQL-DMO and VB so I am struggling.
>
> Thanks in advance for any help you can give!!
>
> Matt|||Hi
Thanks for the reply.
I solved my problem...
I think the following posting will be helphul to someone ...
The text is too long , so posted in 3 parts
Scenario.. PART 1 SERVER COMPUTER
Desktop - Installed Access 2003 & MSDE 2000
Machine Name - MYSVR
MSDE instance Name - 'MYSVR\MYINSTANCE'
User id - 'sa' Password - 'password'
1. Create an Access Project 'TestProj.adp'
2. Create a SQL Database 'TestDB'
3. Create Table Name 'TestTable'
Columns - TestID INT IDENTITY(1,1) PRIMARY KEY
- TestName NVARCHAR(50)
4. Add a record to the table
5. Create a text TestRep01.txt and paste the following
Note that machinename should be replaced with your machine name
/*Script to be copied */
use master
GO
exec sp_adddistributor @.distributor = @.@.servername, @.password = N''
GO
-- Updating the agent profile defaults
sp_MSupdate_agenttype_default @.profile_id = 1
GO
sp_MSupdate_agenttype_default @.profile_id = 2
GO
sp_MSupdate_agenttype_default @.profile_id = 4
GO
sp_MSupdate_agenttype_default @.profile_id = 6
GO
sp_MSupdate_agenttype_default @.profile_id = 11
GO
-- Adding the distribution database
exec sp_adddistributiondb @.database = N'distribution',
@.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.data_file = N'distribution.MDF',
@.data_file_size = 3,
@.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.log_file = N'distribution.LDF',
@.log_file_size = 3,
@.min_distretention = 0,
@.max_distretention = 72,
@.history_retention = 48,
@.security_mode = 1
GO
-- Adding the distribution publisher
exec sp_adddistpublisher @.publisher = @.@.servername,
@.distribution_db = N'distribution',
@.security_mode = 1,
@.working_directory = N'\\MYSVR\C$\Program Files\Microsoft SQL Server\MSSQL$MYSINSTANCE\ReplData',
@.trusted = N'false',
@.thirdparty_flag = 0
GO
/*script ends*/
5. Create a folder 'ReplData' under 'C:\Program Files\Microsoft SQL
Server\MSSQL$MYINSTANCE' if it is not there.
6. Make sure SQLServer agent is running, if not, start that now.
7. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep01.txt
-o ResultTestRep01.txt -b
8. If so far so good , copy the follwoing script to another text file TestRep02.txt
/* Script starts*/
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'TestDB',
@.optname = N'merge publish',
@.value = N'true'
GO
use [TestDB]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'TestPub',
@.description = N'Merge publ of TestDB.',
@.retention = 14,
@.sync_mode = N'character',
@.allow_push = N'true',
@.allow_pull = N'true',
@.allow_anonymous = N'true',
@.enabled_for_internet = N'false',
@.centralized_conflicts = N'true',
@.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false',
@.ftp_port = 21,
@.ftp_login = N'anonymous',
@.conflict_retention = 14,
@.keep_partition_changes = N'false',
@.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false',
@.add_to_active_directory = N'false',
@.max_concurrent_merge = 0,
@.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = 'TestPub',
@.frequency_type = 4,
@.frequency_interval = 1,
@.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 1,
@.frequency_subday_interval = 5,
@.active_start_date = 0,
@.active_end_date = 0,
@.active_start_time_of_day = 500,
@.active_end_time_of_day = 235959,
GO
exec sp_grant_publication_access @.publication = N'TestPub',
@.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'TestPub',
@.article = N'TestTable',
@.source_owner = N'dbo',
@.source_object = N'TestTable',
@.type = N'table',
@.description = null,
@.column_tracking = N'true',
@.pre_creation_cmd = N'drop',
@.creation_script = null,
@.schema_option = 0x000000000000FFF1,
@.article_resolver = null,
@.subset_filterclause = null,
@.vertical_partition = N'false',
@.destination_owner = N'dbo',
@.auto_identity_range = N'false',
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
/*Script Ends */
9. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep02.txt
-o ResultTestRep02.txt -b
10. If this works fine (check the ResultTestRep02.txt for errors) the proceed
Any doubts and corrections are welcome
Cheers
Jos|||Part 2 of hte previous positing
In server computer , TestProj application
11. Create a form 'TestReplication'
13. Create reference to objects SQLMerge, SQLDSnapshot
and SQLReplError
14. Add
-- Commnad buttons 'cmdSnapShot', 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'
Copy the Code and paste in the VBA
Option Explicit
Private WithEvents SQLMerge As SQLMerge
Private WithEvents SQLMrgSnapshot As SQLSnapshot
Private Sub Form_Load()
Set SQLMerge = New SQLMerge
Set SQLMrgSnapshot = New SQLSnapshot
SQLMerge.Publisher = "MYSVR\MYINSTANCE"
SQLMerge.PublisherSecurityMode = NT_AUTHENTICATION
SQLMerge.PublisherDatabase = "TestDB"
SQLMerge.Publication = "TestPub"
SQLMrgSnapshot.Publisher = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.PublisherDatabase = "TestDB"
SQLMrgSnapshot.Distributor = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.Publication = "TestPub"
SQLMrgSnapshot.DistributorSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.PublisherSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.ReplicationType = MERGE
Exit Sub
End Sub
Private Sub cmdMergePub_Click()
On Error GoTo Failure
Dim replerr As SQLReplError
ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Replication."
DoEvents
'Initialize
SQLMerge.Initialize
'Run
SQLMerge.Run
'Terminate
SQLMerge.Terminate
'Reset objects
ProgressBar.Value = 0
Exit Sub
Failure:
For Each replerr In SQLMerge.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr
ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub
Private Sub cmdSnapshot_Click()
On Error GoTo Failure
Dim replerr As SQLReplError
ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Snapshot Generation."
DoEvents
'Initialize
SQLMrgSnapshot.Initialize
'Run
SQLMrgSnapshot.Run
'Terminate
SQLMrgSnapshot.Terminate
'Reset objects
ProgressBar.Value = 0
Exit Sub
Failure:
For Each replerr In SQLMrgSnapshot.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr
ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub
Private Sub PrintErrors(c As Object)
If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, "SQL Replication Sample Failure"
End If
End Sub
Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
SQLMerge_Status = SUCCESS
End Function
Private Function SQLMrgSnapshot_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
'Setting the return code to CANCEL will cause the control to cancel operation
SQLMrgSnapshot_Status = SUCCESS
End Function
15. Now Run only Snapshot by clicking cmdSnapshot button
Continues|||//////////////////////////////////////////////////////////////////////////////////
CLIENT COMPUTER
/////////////////////////////////////////////////////////////////////////////////
16. If success go to your next connected computer
Installed MS-Access 2003 and MSDE 2000
Computer name 'MYLAPTOP'
MSDE instance Name 'MYLAPTOP\MYINSTANCE'
user id 'sa'
passwor 'password'
17. Create an access project TestProjClient
18. Create a SQL Database 'TestDBClient'
Here you do not need to create tables
19. Add a Form TestReplicationClient
20. Create reference to objects SQLMerge and SQLReplError
21. Add
-- Commnad buttons 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'
22. Copy paste the following in the VBA code
'**********************************************
Option Explicit
Private WithEvents SQLMrgSnapshot As SQLSnapshot
Private Sub Form_Load()
Set SQLMerge = New SQLMerge
End Sub
Private Sub cmdMergePub_Click()
with SQLMerge
'------------------
'Set the publisher properties
.Publisher = "MYSVR\MYINSTANCE"
.PublisherSecurityMode = DB_AUTHENTICATION
.PublisherLogin = "sa"
.PublisherPassword = "password"
.PublisherDatabase = "TestDB"
.Publication = "TestPub"
'------------------
.PublisherAddress = "MYSVR"
.PublisherNetwork = DEFAULT_NETWORK
'------------------
'Set the distributor properties
' No need to set Distribution Server when both
' Publisher and subscriber are same Server
'------------------
'Set your local subscriber properties
.Subscriber = "MYLAPTOP\MYINSTANCE"
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberDatasourceType = SQL_SERVER
.SubscriberLogin = "sa"
.SubscriberPassword = "password"
.SubscriberDatabase = "TestDBReplica"
.SubscriptionType = ANONYMOUS
'------------------
'------------------
.Initialize
.Run
.Terminate
'------------------
End With
exit sub
ErrH:
Set SQLMerge = Nothing
MsgBox "Unexpected error occured during Merge Process" & _
vbCrLf & Err.Description, vbCritical, "Replication"
End Sub
Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
SQLMerge_Status = SUCCESS
End Function
'**********************************************
23. Click cmdMerge
SUCCESS??????
if yes
24. Open the table and add one more record
25. now click cmdMerge again
26. Go to the 1st computer (server computer) and see whether newly added record in the laptop is in the TestProj
DONE
Pls reply if any doubts
Cheers
Jos|||Attached txtx file for scenario on which i solved the replication problem
All comments are corrections are appreciated
cheers
Jos
Server runs on MS-XP Professional
------------
I have a sample Access project 'ReplTest' which has only table with only 2 columns.
DatabaseName : ReplTestDB
Table Name : TestTable
MSDE Instance Name : SVR\MYINSTANCE
Now I would like to know how I can configure this database for merge replication
using SQL-DMO
Laptop runs on MS-XP Professional
------------
I have another access project which is running on computer 2 and connected to the
ReplicaTest database.
MSDE Instance Name : LPT\MYINSTANCE
My task is, when I am disconnected from server I would like to have a local copy of
the database to work with and then, when reconnected, need synchronization with the
server database and continue working from server database.
How to write this replication process from scratch using SQL-DMO objects in both Server computer
and Laptop computer.
I dont have enterprise manager in both computers since they use only MSDE
Can anyone help me?
Thanks in advance
JPCheck out "Publishers, Distributors, and Subscribers" from BOL (Ihope you have BOL).|||The problem with BOL is it is so extensive and the scenario of my situation is not described. I could do replication in the same machine as distributor, publisher and suscriber, but what if the subscriber is another machine as in my case ?|||I found this within these forums:
The sample code you were after is for creating heterogenous publication. It does not work with SQL publications. Try to model after the following sample.
Sub CreateMergePub() Dim osvr As New SQLDMO.SQLServer Dim szLastErrorText As String
Dim lLastErrorNumber As Long
Dim szServerName As String
Dim oReplicationDatabase As SQLDMO.ReplicationDatabase
Dim oDistPublisher As New DistributionPublisher
Dim oMergePublication As New MergePublication
Dim oMergeArticle1 As New SQLDMO.MergeArticle
On Error GoTo ErrorHandler
szServerName = "your servername"
' To use NT integrated security ' osvr.LoginSecure = True
osvr.Connect szServerName
' Enable pubs database for publishing ' Set oReplicationDatabase =
osvr.Replication.ReplicationDatabases("pubs")
'oReplicationDatabase.EnableTransPublishing = True
' Set MergePublication properties, name, snapshot method, trans '
oMergePublication.Name = "Pub1" oMergePublication.SnapshotMethod =
SQLDMOInitSync_BCPNative
' Set non-default properties, i.e. if you do not set the following properties, '
default value will be used ' 'oMergePublication.PublicationAttributes =
SQLDMOPubAttrib_AllowPull Or SQLDMOPubAttrib_AllowAnonymous
'oMergePublication.RetentionPeriod = 64
'oMergePublication.SnapshotSchedule.FrequencyType = SQLDMOFreq_Daily
'oMergePublication.SnapshotSchedule.FrequencyInter val = 1
'oMergePublication.SnapshotSchedule.ActiveStartTim eOfDay = 233000
'oMergePublication.SnapshotSchedule.ActiveEndTimeO fDay = 235959
'oMergePublication.SnapshotSchedule.ActiveStartDat e = 0
'oMergePublication.SnapshotSchedule.ActiveEndDate = 19981205
oReplicationDatabase.MergePublications.Add oMergePublication
' Add article ' oMergeArticle1.Name = "Article1" oMergeArticle1.SourceObjectName
= "jobs" oMergeArticle1.SourceObjectOwner = "dbo"
'Add the article to the publication ' oMergePublication.MergeArticles.Add
oMergeArticle1
' Disconnect from SQL Server szServerName ' osvr.DisConnect Set osvr =
Nothing End
ErrorHandler:
szLastErrorText = Err.Description lLastErrorNumber = Err.Number
MsgBox szLastErrorText, vbOKOnly, "Error " + Trim$(Str$(Err.Number))
End Sub
--
Pung Xu, Microsoft This posting is provided "AS IS" with no warranties, and confers
no rights. Please do not send email directly to this alias. This alias is for
newsgroup purposes only.
"matt" <matt_e_davis@.hotmail.com> wrote in message
news:d0db2ad5.0205221240.7c427ed7@.posting.google.c om...
> I am having trouble creating a merge publication with SQL-DMO. I modeled my
> solution after the following code:
> --================================================ Sub CreatePublication() '
> Connect to SQL Server Distributor Dim oSqlServer As New SQLDMO.SQLServer
> oSqlServer.Connect "", "sa", "" On Error GoTo ErrorHandler
>
> Dim oSamplePublisher As DistributionPublisher Set oSamplePublisher =
>
oSqlServer.Replication.Distributor.DistributionPub lishers("SAMPLEPUBLISHER")
>
> ' Create Sample Publication Dim oSamplePublication As New
> DistributionPublication oSamplePublication.Name = "SamplePublication"
> oSamplePublication.PublicationDB = "SampleDatabase"
> oSamplePublication.PublicationType = SQLDMOPublication_Transactional
> oSamplePublication.VendorName = "Sample Vendor"
> oSamplePublication.LogReaderAgent = "SampleLogReaderAgent"
> oSamplePublication.SnapshotAgent = "SampleSnapShotAgent"
> oSamplePublication.Description = "Sample Publication Definition"
> oSamplePublication.PublicationAttributes = SQLDMOPubAttrib_AllowPush
>
> ' Add the Publication oSamplePublisher.DistributionPublications.Add
> oSamplePublication
>
> ' Create Sample Articles Dim oSampleArticle1 As New DistributionArticle
> oSampleArticle1.Name = "SampleArticle1" oSampleArticle1.Description = "Sampe
> Article1 Definition" oSampleArticle1.SourceObjectName = "SampleTable1"
>
> Dim oSampleArticle2 As New DistributionArticle oSampleArticle2.Name =
> "SampleArticle2" oSampleArticle2.Description = "Sample Article2 Definition"
> oSampleArticle2.SourceObjectName = "SampleTable2"
>
> ' Add the Articles to the Publication
> oSamplePublication.DistributionArticles.Add oSampleArticle2
> oSamplePublication.DistributionArticles.Add oSampleArticle1
>
> oSqlServer.Close Exit Sub ErrorHandler: PrintErrors oSqlServer Exit Sub End Sub
>
> --================================================ This does create a publication
> but when you right click (via Enterprise Mgr.) Replication Monitor > PublisherName
> > SamplePublication and select Properties an error occurs. The error states that
> the publication is not in the TransPublication collection. The same error occurs
> when you create a merge publication. Except the error is that the publication does
> not exist in the MergePublications collection.
>
> How can I add the publication to the MergePublications collection?? Does anyone
> have any sample code for this?
>
> I am new at SQL-DMO and VB so I am struggling.
>
> Thanks in advance for any help you can give!!
>
> Matt|||Hi
Thanks for the reply.
I solved my problem...
I think the following posting will be helphul to someone ...
The text is too long , so posted in 3 parts
Scenario.. PART 1 SERVER COMPUTER
Desktop - Installed Access 2003 & MSDE 2000
Machine Name - MYSVR
MSDE instance Name - 'MYSVR\MYINSTANCE'
User id - 'sa' Password - 'password'
1. Create an Access Project 'TestProj.adp'
2. Create a SQL Database 'TestDB'
3. Create Table Name 'TestTable'
Columns - TestID INT IDENTITY(1,1) PRIMARY KEY
- TestName NVARCHAR(50)
4. Add a record to the table
5. Create a text TestRep01.txt and paste the following
Note that machinename should be replaced with your machine name
/*Script to be copied */
use master
GO
exec sp_adddistributor @.distributor = @.@.servername, @.password = N''
GO
-- Updating the agent profile defaults
sp_MSupdate_agenttype_default @.profile_id = 1
GO
sp_MSupdate_agenttype_default @.profile_id = 2
GO
sp_MSupdate_agenttype_default @.profile_id = 4
GO
sp_MSupdate_agenttype_default @.profile_id = 6
GO
sp_MSupdate_agenttype_default @.profile_id = 11
GO
-- Adding the distribution database
exec sp_adddistributiondb @.database = N'distribution',
@.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.data_file = N'distribution.MDF',
@.data_file_size = 3,
@.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.log_file = N'distribution.LDF',
@.log_file_size = 3,
@.min_distretention = 0,
@.max_distretention = 72,
@.history_retention = 48,
@.security_mode = 1
GO
-- Adding the distribution publisher
exec sp_adddistpublisher @.publisher = @.@.servername,
@.distribution_db = N'distribution',
@.security_mode = 1,
@.working_directory = N'\\MYSVR\C$\Program Files\Microsoft SQL Server\MSSQL$MYSINSTANCE\ReplData',
@.trusted = N'false',
@.thirdparty_flag = 0
GO
/*script ends*/
5. Create a folder 'ReplData' under 'C:\Program Files\Microsoft SQL
Server\MSSQL$MYINSTANCE' if it is not there.
6. Make sure SQLServer agent is running, if not, start that now.
7. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep01.txt
-o ResultTestRep01.txt -b
8. If so far so good , copy the follwoing script to another text file TestRep02.txt
/* Script starts*/
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'TestDB',
@.optname = N'merge publish',
@.value = N'true'
GO
use [TestDB]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'TestPub',
@.description = N'Merge publ of TestDB.',
@.retention = 14,
@.sync_mode = N'character',
@.allow_push = N'true',
@.allow_pull = N'true',
@.allow_anonymous = N'true',
@.enabled_for_internet = N'false',
@.centralized_conflicts = N'true',
@.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false',
@.ftp_port = 21,
@.ftp_login = N'anonymous',
@.conflict_retention = 14,
@.keep_partition_changes = N'false',
@.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false',
@.add_to_active_directory = N'false',
@.max_concurrent_merge = 0,
@.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = 'TestPub',
@.frequency_type = 4,
@.frequency_interval = 1,
@.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 1,
@.frequency_subday_interval = 5,
@.active_start_date = 0,
@.active_end_date = 0,
@.active_start_time_of_day = 500,
@.active_end_time_of_day = 235959,
GO
exec sp_grant_publication_access @.publication = N'TestPub',
@.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'TestPub',
@.article = N'TestTable',
@.source_owner = N'dbo',
@.source_object = N'TestTable',
@.type = N'table',
@.description = null,
@.column_tracking = N'true',
@.pre_creation_cmd = N'drop',
@.creation_script = null,
@.schema_option = 0x000000000000FFF1,
@.article_resolver = null,
@.subset_filterclause = null,
@.vertical_partition = N'false',
@.destination_owner = N'dbo',
@.auto_identity_range = N'false',
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
/*Script Ends */
9. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep02.txt
-o ResultTestRep02.txt -b
10. If this works fine (check the ResultTestRep02.txt for errors) the proceed
Any doubts and corrections are welcome
Cheers
Jos|||Part 2 of hte previous positing
In server computer , TestProj application
11. Create a form 'TestReplication'
13. Create reference to objects SQLMerge, SQLDSnapshot
and SQLReplError
14. Add
-- Commnad buttons 'cmdSnapShot', 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'
Copy the Code and paste in the VBA
Option Explicit
Private WithEvents SQLMerge As SQLMerge
Private WithEvents SQLMrgSnapshot As SQLSnapshot
Private Sub Form_Load()
Set SQLMerge = New SQLMerge
Set SQLMrgSnapshot = New SQLSnapshot
SQLMerge.Publisher = "MYSVR\MYINSTANCE"
SQLMerge.PublisherSecurityMode = NT_AUTHENTICATION
SQLMerge.PublisherDatabase = "TestDB"
SQLMerge.Publication = "TestPub"
SQLMrgSnapshot.Publisher = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.PublisherDatabase = "TestDB"
SQLMrgSnapshot.Distributor = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.Publication = "TestPub"
SQLMrgSnapshot.DistributorSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.PublisherSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.ReplicationType = MERGE
Exit Sub
End Sub
Private Sub cmdMergePub_Click()
On Error GoTo Failure
Dim replerr As SQLReplError
ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Replication."
DoEvents
'Initialize
SQLMerge.Initialize
'Run
SQLMerge.Run
'Terminate
SQLMerge.Terminate
'Reset objects
ProgressBar.Value = 0
Exit Sub
Failure:
For Each replerr In SQLMerge.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr
ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub
Private Sub cmdSnapshot_Click()
On Error GoTo Failure
Dim replerr As SQLReplError
ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Snapshot Generation."
DoEvents
'Initialize
SQLMrgSnapshot.Initialize
'Run
SQLMrgSnapshot.Run
'Terminate
SQLMrgSnapshot.Terminate
'Reset objects
ProgressBar.Value = 0
Exit Sub
Failure:
For Each replerr In SQLMrgSnapshot.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr
ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub
Private Sub PrintErrors(c As Object)
If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, "SQL Replication Sample Failure"
End If
End Sub
Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
SQLMerge_Status = SUCCESS
End Function
Private Function SQLMrgSnapshot_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
'Setting the return code to CANCEL will cause the control to cancel operation
SQLMrgSnapshot_Status = SUCCESS
End Function
15. Now Run only Snapshot by clicking cmdSnapshot button
Continues|||//////////////////////////////////////////////////////////////////////////////////
CLIENT COMPUTER
/////////////////////////////////////////////////////////////////////////////////
16. If success go to your next connected computer
Installed MS-Access 2003 and MSDE 2000
Computer name 'MYLAPTOP'
MSDE instance Name 'MYLAPTOP\MYINSTANCE'
user id 'sa'
passwor 'password'
17. Create an access project TestProjClient
18. Create a SQL Database 'TestDBClient'
Here you do not need to create tables
19. Add a Form TestReplicationClient
20. Create reference to objects SQLMerge and SQLReplError
21. Add
-- Commnad buttons 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'
22. Copy paste the following in the VBA code
'**********************************************
Option Explicit
Private WithEvents SQLMrgSnapshot As SQLSnapshot
Private Sub Form_Load()
Set SQLMerge = New SQLMerge
End Sub
Private Sub cmdMergePub_Click()
with SQLMerge
'------------------
'Set the publisher properties
.Publisher = "MYSVR\MYINSTANCE"
.PublisherSecurityMode = DB_AUTHENTICATION
.PublisherLogin = "sa"
.PublisherPassword = "password"
.PublisherDatabase = "TestDB"
.Publication = "TestPub"
'------------------
.PublisherAddress = "MYSVR"
.PublisherNetwork = DEFAULT_NETWORK
'------------------
'Set the distributor properties
' No need to set Distribution Server when both
' Publisher and subscriber are same Server
'------------------
'Set your local subscriber properties
.Subscriber = "MYLAPTOP\MYINSTANCE"
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberDatasourceType = SQL_SERVER
.SubscriberLogin = "sa"
.SubscriberPassword = "password"
.SubscriberDatabase = "TestDBReplica"
.SubscriptionType = ANONYMOUS
'------------------
'------------------
.Initialize
.Run
.Terminate
'------------------
End With
exit sub
ErrH:
Set SQLMerge = Nothing
MsgBox "Unexpected error occured during Merge Process" & _
vbCrLf & Err.Description, vbCritical, "Replication"
End Sub
Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
SQLMerge_Status = SUCCESS
End Function
'**********************************************
23. Click cmdMerge
SUCCESS??????
if yes
24. Open the table and add one more record
25. now click cmdMerge again
26. Go to the 1st computer (server computer) and see whether newly added record in the laptop is in the TestProj
DONE
Pls reply if any doubts
Cheers
Jos|||Attached txtx file for scenario on which i solved the replication problem
All comments are corrections are appreciated
cheers
Jos
Monday, March 19, 2012
Merge Replication Overwritting Data
Hi,
I have a problem with merge replication overwriting data without any
reason. Here is my setup:
Publisher/Distributor runs on Windows 2003 Server
Subscribers running on Windows 2000/XP using SQL desktop engine.
Subscribers update/modify data via an application and sync back to the
publisher.
Users also log straight into the server via an application to update/
modify data.
Data is been overwritten by an older version of that record. To give
you an example of the process a user enters in a record it then gets
reviewed by a manger who approves a "status" change, the data is then
entered into a secondary system then the record is closed/completed.
What is happening is that record for some reason is reverting back to
a previous version with an old status and is coming up on managers
reports again, causing work flow issues.
This is happening inconsistently and very rarely and we cant seems to
reproduce the scenario. Can anyone give me incite into what is
happening here why replication is reverting data back.
Regards,
Ben
Are you seeing any conflicts?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Setting the flag is an option. If this is the issue, I'd also like to know
exactly why this occurs though - ie why can't the change be applied at the
replica. Are the constraints different perhaps? Is the change being applied
to the PK etc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Mar 7, 8:51 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Setting the flag is an option. If this is the issue, I'd also like to know
> exactly why this occurs though - ie why can't the change be applied at the
> replica. Are the constraints different perhaps? Is the change being applied
> to the PK etc.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
That is a good question, my biggest problem is trying to reproduce the
problem to isolate where it is occurring. Unfortunately I haven't been
able to do this. The only thing I know thus far is it happens
intermittently and it's affecting records at random, both of which are
no help. I do know however that this problem only began when
replication started.
Would any of the following 2 scenarios be related to this problem:
1. A subscriber (Salesperson) sync's their data but fails in the
attempt,
the user then continues to modify data on their local machine and then
sync's
again only this time successfully, causing previous changes to be
overwritten.
2. A subscriber (Salesperson) sync's their data but fails in the
attempt, a
snapshot is taken of the publisher's data then the user sync's again
only
this time successfully, causing previous changes to be overwritten.
I'm not sure what else to check as I have nothing else to go on, any
other suggestions?
I have a problem with merge replication overwriting data without any
reason. Here is my setup:
Publisher/Distributor runs on Windows 2003 Server
Subscribers running on Windows 2000/XP using SQL desktop engine.
Subscribers update/modify data via an application and sync back to the
publisher.
Users also log straight into the server via an application to update/
modify data.
Data is been overwritten by an older version of that record. To give
you an example of the process a user enters in a record it then gets
reviewed by a manger who approves a "status" change, the data is then
entered into a secondary system then the record is closed/completed.
What is happening is that record for some reason is reverting back to
a previous version with an old status and is coming up on managers
reports again, causing work flow issues.
This is happening inconsistently and very rarely and we cant seems to
reproduce the scenario. Can anyone give me incite into what is
happening here why replication is reverting data back.
Regards,
Ben
Are you seeing any conflicts?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Setting the flag is an option. If this is the issue, I'd also like to know
exactly why this occurs though - ie why can't the change be applied at the
replica. Are the constraints different perhaps? Is the change being applied
to the PK etc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Mar 7, 8:51 pm, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Setting the flag is an option. If this is the issue, I'd also like to know
> exactly why this occurs though - ie why can't the change be applied at the
> replica. Are the constraints different perhaps? Is the change being applied
> to the PK etc.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
That is a good question, my biggest problem is trying to reproduce the
problem to isolate where it is occurring. Unfortunately I haven't been
able to do this. The only thing I know thus far is it happens
intermittently and it's affecting records at random, both of which are
no help. I do know however that this problem only began when
replication started.
Would any of the following 2 scenarios be related to this problem:
1. A subscriber (Salesperson) sync's their data but fails in the
attempt,
the user then continues to modify data on their local machine and then
sync's
again only this time successfully, causing previous changes to be
overwritten.
2. A subscriber (Salesperson) sync's their data but fails in the
attempt, a
snapshot is taken of the publisher's data then the user sync's again
only
this time successfully, causing previous changes to be overwritten.
I'm not sure what else to check as I have nothing else to go on, any
other suggestions?
Saturday, February 25, 2012
Merge Replication between sites - Keeping hot backu servers ready
Dear all
I have set a merge replication between servers say SERVER A (Publisher) and
SERVER B (Subscriber to A)
The agent runs every 2 minutes.
I have set all identity columns with increment value by 2. SERVER A has all
odd identity values and SERVER B has even.
I have 2 more servers (STAND BY) say SERVER C and SERVER D.
SERVER C is located near to SERVER A (Same office - Office A) and SERVER D
is near SERVER B (Same Office - Office B).
I want to keep SERVER C as backup replica (in case SERVER A goes down users
will connect to it and there will be minimum downtime for the users in
Office A and when SERVER A comes up it will synch with SERVER C and in turn
it will synch with SERVER B) of SERVER A (which is publisher to SERVER B) so
this can be publisher to SERVER C.
How do I configure SERVER D which will be replica (in case SERVER B goes
down users will connect to it and there will be minimum downtime for the
users in Office B and when SERVER B comes up it will synch with SERVER D and
in turn it will synch with SERVER A) of SERVER B.
Will it be subscriber or publisher to SERVER B (Which is subscriber to
SERVER A).
1- Do I need to configure SERVER B as Publisher and Subscriber?
2- Do I need to configure SERVER D as Publisher and SERVER B as subscriber.
In this case will it replicate between SERVER A and SERVER B. Do I need to
configure SERVER A's subscribers can synch with multiple PUBLISHER?
Your inputs are highly appreciated.
Hope I conveyed my need clearly.
Regards
Javed Iqbal
Javed,
you could set ServerB up as a republisher - subscriber to A and publisher to
D. However I think an easier solution would be to have B, C and D as
subscribers to A if your network speeds can cope with it. In this case, if B
goes down, users use D which is regularly synchronized with A. When it comes
back on line, A and B are synchronized.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
Initially I thought about same keeping A as publisher and rest all
subscribers. But the connectivity between SERVER A and SERVER B (2 MBPS) may
not be enough. There will be lot of other network traffic between office and
can hamper the replication process.
Thank you once again for your prompt help.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O1$4JwMyFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Javed,
> you could set ServerB up as a republisher - subscriber to A and publisher
to
> D. However I think an easier solution would be to have B, C and D as
> subscribers to A if your network speeds can cope with it. In this case, if
B
> goes down, users use D which is regularly synchronized with A. When it
comes
> back on line, A and B are synchronized.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Using merge replication for disaster recovery is a bad choice for the
following reasons
1) you add a rowguid column to each table you are replicating
2) increased latency of transactions occurring on the publisher/subscriber
due to the triggers
3) long sync times
4) no automatic recovery
5) somewhat limited abilities to modify schema
6) possibility of data loss
For automatic failover you need to use clustering. Otherwise a better
solution is probably log shipping.
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
"Javed" <javediqbal98@.hotmail.com> wrote in message
news:uRHPgnMyFHA.788@.tk2msftngp13.phx.gbl...
> Dear all
> I have set a merge replication between servers say SERVER A (Publisher)
and
> SERVER B (Subscriber to A)
> The agent runs every 2 minutes.
> I have set all identity columns with increment value by 2. SERVER A has
all
> odd identity values and SERVER B has even.
> I have 2 more servers (STAND BY) say SERVER C and SERVER D.
> SERVER C is located near to SERVER A (Same office - Office A) and SERVER D
> is near SERVER B (Same Office - Office B).
> I want to keep SERVER C as backup replica (in case SERVER A goes down
users
> will connect to it and there will be minimum downtime for the users in
> Office A and when SERVER A comes up it will synch with SERVER C and in
turn
> it will synch with SERVER B) of SERVER A (which is publisher to SERVER B)
so
> this can be publisher to SERVER C.
> How do I configure SERVER D which will be replica (in case SERVER B goes
> down users will connect to it and there will be minimum downtime for the
> users in Office B and when SERVER B comes up it will synch with SERVER D
and
> in turn it will synch with SERVER A) of SERVER B.
> Will it be subscriber or publisher to SERVER B (Which is subscriber to
> SERVER A).
> 1- Do I need to configure SERVER B as Publisher and Subscriber?
> 2- Do I need to configure SERVER D as Publisher and SERVER B as
subscriber.
> In this case will it replicate between SERVER A and SERVER B. Do I need to
> configure SERVER A's subscribers can synch with multiple PUBLISHER?
> Your inputs are highly appreciated.
> Hope I conveyed my need clearly.
> Regards
> Javed Iqbal
>
I have set a merge replication between servers say SERVER A (Publisher) and
SERVER B (Subscriber to A)
The agent runs every 2 minutes.
I have set all identity columns with increment value by 2. SERVER A has all
odd identity values and SERVER B has even.
I have 2 more servers (STAND BY) say SERVER C and SERVER D.
SERVER C is located near to SERVER A (Same office - Office A) and SERVER D
is near SERVER B (Same Office - Office B).
I want to keep SERVER C as backup replica (in case SERVER A goes down users
will connect to it and there will be minimum downtime for the users in
Office A and when SERVER A comes up it will synch with SERVER C and in turn
it will synch with SERVER B) of SERVER A (which is publisher to SERVER B) so
this can be publisher to SERVER C.
How do I configure SERVER D which will be replica (in case SERVER B goes
down users will connect to it and there will be minimum downtime for the
users in Office B and when SERVER B comes up it will synch with SERVER D and
in turn it will synch with SERVER A) of SERVER B.
Will it be subscriber or publisher to SERVER B (Which is subscriber to
SERVER A).
1- Do I need to configure SERVER B as Publisher and Subscriber?
2- Do I need to configure SERVER D as Publisher and SERVER B as subscriber.
In this case will it replicate between SERVER A and SERVER B. Do I need to
configure SERVER A's subscribers can synch with multiple PUBLISHER?
Your inputs are highly appreciated.
Hope I conveyed my need clearly.
Regards
Javed Iqbal
Javed,
you could set ServerB up as a republisher - subscriber to A and publisher to
D. However I think an easier solution would be to have B, C and D as
subscribers to A if your network speeds can cope with it. In this case, if B
goes down, users use D which is regularly synchronized with A. When it comes
back on line, A and B are synchronized.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
Initially I thought about same keeping A as publisher and rest all
subscribers. But the connectivity between SERVER A and SERVER B (2 MBPS) may
not be enough. There will be lot of other network traffic between office and
can hamper the replication process.
Thank you once again for your prompt help.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O1$4JwMyFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Javed,
> you could set ServerB up as a republisher - subscriber to A and publisher
to
> D. However I think an easier solution would be to have B, C and D as
> subscribers to A if your network speeds can cope with it. In this case, if
B
> goes down, users use D which is regularly synchronized with A. When it
comes
> back on line, A and B are synchronized.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Using merge replication for disaster recovery is a bad choice for the
following reasons
1) you add a rowguid column to each table you are replicating
2) increased latency of transactions occurring on the publisher/subscriber
due to the triggers
3) long sync times
4) no automatic recovery
5) somewhat limited abilities to modify schema
6) possibility of data loss
For automatic failover you need to use clustering. Otherwise a better
solution is probably log shipping.
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
"Javed" <javediqbal98@.hotmail.com> wrote in message
news:uRHPgnMyFHA.788@.tk2msftngp13.phx.gbl...
> Dear all
> I have set a merge replication between servers say SERVER A (Publisher)
and
> SERVER B (Subscriber to A)
> The agent runs every 2 minutes.
> I have set all identity columns with increment value by 2. SERVER A has
all
> odd identity values and SERVER B has even.
> I have 2 more servers (STAND BY) say SERVER C and SERVER D.
> SERVER C is located near to SERVER A (Same office - Office A) and SERVER D
> is near SERVER B (Same Office - Office B).
> I want to keep SERVER C as backup replica (in case SERVER A goes down
users
> will connect to it and there will be minimum downtime for the users in
> Office A and when SERVER A comes up it will synch with SERVER C and in
turn
> it will synch with SERVER B) of SERVER A (which is publisher to SERVER B)
so
> this can be publisher to SERVER C.
> How do I configure SERVER D which will be replica (in case SERVER B goes
> down users will connect to it and there will be minimum downtime for the
> users in Office B and when SERVER B comes up it will synch with SERVER D
and
> in turn it will synch with SERVER A) of SERVER B.
> Will it be subscriber or publisher to SERVER B (Which is subscriber to
> SERVER A).
> 1- Do I need to configure SERVER B as Publisher and Subscriber?
> 2- Do I need to configure SERVER D as Publisher and SERVER B as
subscriber.
> In this case will it replicate between SERVER A and SERVER B. Do I need to
> configure SERVER A's subscribers can synch with multiple PUBLISHER?
> Your inputs are highly appreciated.
> Hope I conveyed my need clearly.
> Regards
> Javed Iqbal
>
Subscribe to:
Posts (Atom)