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