Wednesday, March 7, 2012

Create merge publication programically?

Hi all,
Can any one give me a sample code for creating merge publication?
Thank you very much!
TonyHere 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

No comments:

Post a Comment