Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    1

    Lightbulb Unanswered: Create merge publication programically?

    Hi all,

    Can any one give me a sample code for creating merge publication?
    Thank you very much!

    Tony

  2. #2
    Join Date
    May 2002
    Posts
    3

    Thumbs up RE: Create merge publication programically?

    Here is a snippet of code that I got after creating my publication in SQL Server Enterprise Manager, then had it generate the scripts for me. This is for a merge replication with dynamic filters (table filtered based on their username) and anonymous pull subscriptions allowed. Note: This does NOT create the first snapshot, as it doesn't create the code for this...but I'm sure it exists.

    The best way to get this code is to do all you want in EM first, then export it and use it in your code. Good luck...replication is a pain.





    -- Enabling the replication database
    use master
    GO

    exec sp_replicationdboption N'MyDatabase', N'merge publish', true
    GO

    use [MyDatabase]
    GO

    -- Adding the merge publication
    exec sp_addmergepublication @publication = N'MyDatabase-MyPublication', @description = N'Merge publication of MyDatabase database from Publisher MyServer.', @retention = 60, @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'true'
    exec sp_addpublication_snapshot @publication = N'MyDatabase-MyPublication',@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'MyDatabase-MyPublication', @login = N'sa'
    GO
    exec sp_grant_publication_access @publication = N'MyDatabase-MyPublication', @login = N'kellera'
    GO

    -- Adding the merge articles
    exec sp_addmergearticle @publication = N'MyDatabase-Master_Table', @article = N'Master_People_Table', @source_owner = N'dbo', @source_object = N'Master_People_Table', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x00000000000000F1, @article_resolver = null, @subset_filterclause = N'vchUID=USER_NAME()'
    GO

    exec sp_addmergearticle @publication = N'MyDatabase-MyPublication', @article = N'Master_Table', @source_owner = N'dbo', @source_object = N'Master_Table', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x00000000000000F1, @article_resolver = null, @subset_filterclause = N'dtExpiration >= GETDATE()'
    GO

    -- Adding the article subset filter
    exec sp_addmergefilter @publication = N'MyDatabase-MyPublication', @article = N'Master_Table', @filtername = N'Master_Table_Master_People_Table', @join_articlename = N'Master_People_Table', @join_filterclause = N'Master_Table.Installer=Master_People_Table.iPers onID AND Master_People_Table.vchCategory LIKE ''Installer''', @join_unique_key = 0
    GO

Posting Permissions

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