Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    8

    Unanswered: 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

    JP

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check out "Publishers, Distributors, and Subscribers" from BOL (Ihope you have BOL).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2005
    Posts
    8
    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 ?

  4. #4
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63
    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

  5. #5
    Join Date
    Jan 2005
    Posts
    8

    I'm sure someone is after the Merge Replication in MSDE

    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

  6. #6
    Join Date
    Jan 2005
    Posts
    8

    Part 2

    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

  7. #7
    Join Date
    Jan 2005
    Posts
    8

    part 3

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

  8. #8
    Join Date
    Jan 2005
    Posts
    8

    Ooops, if u can access txt file here it goes

    Attached txtx file for scenario on which i solved the replication problem

    All comments are corrections are appreciated

    cheers
    Jos
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •