Stored procedure to set up merge replication

Last Post 13 Jul 2004 11:42 AM by rjdegraff. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rjdegraff
New Member
New Member

--
13 Jul 2004 11:22 AM
I need a stored procedure to create a merge replication. What I have so far
is

--
-- This procedure should be run on ONLY the primary SQL server
--
-- Give it the name of a database on that server and it will create a
Publisher for replicating that database to the backup server. Once you have
run this
-- procedure you should also connect to the backup SQL server and run
up_CreateSub with the same database name to create the pull subscription.
--
-- 2004-07-08 jdeg original code
--

CREATE PROCEDURE up_CreatePub @name varchar(100) AS

if (@@servername = 'WPG-APPS-87') begin

declare @desc as varchar(100) set @desc = 'Merge publication of ' + @name
+ ' database from publisher ' + @@servername
declare @snap as varchar(100) set @snap = @@servername + '-' + @name + '-'
+ @name + '-90'

exec sp_replicationdboption @dbname = @name, @optname = N'merge publish',
@value = N'true'

-- Adding the merge publication

exec sp_addmergepublication @publication = @name,@description = @desc,
@retention = 14, @sync_mode = N'native', @allow_push = N'true', @allow_pull
= N'true', @allow_anonymous = N'false', @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 = @name,@frequency_type = 8,
@frequency_interval = 64, @frequency_relative_interval = 0,
@frequency_recurrence_factor = 1, @frequency_subday = 1,
@frequency_subday_interval = 0, @active_start_date = 0, @active_end_date =
0, @active_start_time_of_day = 0, @active_end_time_of_day = 0,
@snapshot_job_name = @snap

exec sp_grant_publication_access @publication = @name, @login =
N'BUILTIN\Administrators'
exec sp_grant_publication_access @publication = @name, @login = N'sa'

-- Adding the merge articles

exec sp_addmergearticle @publication = @name, @article =
N'Analog00', @source_owner = N'dbo', @source_object = N'Analog00', @type =
N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd
= N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@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
exec sp_addmergearticle @publication = @name, @article =
N'Analog05', @source_owner = N'dbo', @source_object = N'Analog05', @type =
N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd
= N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1,
@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

end else begin

RAISERROR ('you can only run this on WPG-APPS-87',16,1)

end
GO

---------------------------

when I run it in the Query analyzer I get

Creating table sysmergepublications
Creating table MSmerge_errorlineage
Creating table MSrepl_identity_range
Creating table sysmergearticles
Creating table sysmergeschemaarticles
Creating table sysmergesubscriptions
Creating table MSmerge_replinfo
Creating table MSmerge_tombstone
Creati
rjdegraff
New Member
New Member

--
13 Jul 2004 11:42 AM
I realize that. That's how I generated the original code. The problem is that I need to be able to run the script from a stored procedure and I can't put the "use database" into the stored procedure so it always tries to run against the default database (EMSData) where the stored procedure is stored.
You are not authorized to post a reply.

Acceptable Use Policy