Merge Wizard

,

In our environment, we needed to create and drop replication over and over in our test environment and I got realy tired of having to manualy go to each table with an identity column to set its ranges. This sproc will create or drop the publications and/or subscriptions for all or some of the objects in your database. Its nothing fancy, and could use alot of tweaking for your needs, but it works well for us.

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'sp_MergeWizard' 
	   AND 	  type = 'P')
    DROP PROCEDURE sp_MergeWizard
GO

CREATE PROCEDURE sp_MergeWizard 

	@PublisherName		VarChar(100)	= @@ServerName		-- The Current Server
 	,@PublisherDBName	VarChar(100)	= ''			-- The Current Database
	,@SubscriberName	VarChar(100)	= @@ServerName		-- Not sure if or how to automate this
	,@SubscriberDBName	VarChar(100)	= ''			-- Not sure if or how to automate this
	,@UseTables		Bit 		= 1			-- USE All Tables (SCHEMA AND DATA) and automitcaly handle the ranges for Identities
	,@UseViews		Bit 		= 1			-- USE All Views (SCHEMA ONLY)
	,@UseProcs		Bit 		= 1			-- USE All Procedures (SCHEMA ONLY)
	,@DropSub		Bit 		= 1			-- DROP The Selected Subscriptions
	,@DropPub		Bit 		= 1			-- DROP The Selected Publications
	,@CreateSub		Bit 		= 1			-- CREATE The Selected Subscriptions
	,@CreatePub		Bit 		= 1			-- CREATE The Selected Publications
	,@pub_identity_range 	INT		= 10000000		-- IDENTITY BLOCK FOR SUBSCRIBERS
	,@identity_range	INT		= 10000000		-- IDENTITY BLOCK FOR PUBLISHER
	,@threshold		INT		= 80			-- IDENTITY VALUES USED BEFORE ASSIGNING NEW BLOCK
AS
-- ============================================================================================================================
-- ============================================================================================================================
--
-- CREATED BY STEVE LEDRIDGE
-- LAST MODIFIED	09/05/01
--
-- DESCRIPTION: This will create publications and subscriptions for all objects in a database

/* EXAMPLE USE:

DECLARE @RC int
DECLARE @PublisherName varchar(100)
DECLARE @PublisherDBName varchar(100)
DECLARE @SubscriberName varchar(100)
DECLARE @SubscriberDBName varchar(100)
DECLARE @UseTables bit
DECLARE @UseViews bit
DECLARE @UseProcs bit
DECLARE @DropSub bit
DECLARE @DropPub bit
DECLARE @CreateSub bit
DECLARE @CreatePub bit
-- Set parameter values

SET @PublisherName	=	'TestDB'
SET @PublisherDBName 	=	'Wellmed315'
SET @SubscriberName	=	'TestDB'
SET @SubscriberDBName	=	'Wellmed'
SET @UseTables		=	1
SET @UseViews		=	0
SET @UseProcs		=	0
SET @DropSub		=	0
SET @DropPub		=	0
SET @CreateSub		=	0
SET @CreatePub		=	1

EXEC @RC = [sp_MergeWizard] @PublisherName, @PublisherDBName, @SubscriberName, @SubscriberDBName, @UseTables, @UseViews, @UseProcs, @DropSub, @DropPub, @CreateSub, @CreatePub

*/
-- ============================================================================================================================
-- ============================================================================================================================

DECLARE @name 			VarChar(100)
DECLARE @Type			VarChar(25)
DECLARE @TypeName		VarChar(25)
DECLARE @SQLString		VarChar(5000)
DECLARE @ManageIdents		VarChar(500)
DECLARE @schemaOption		VarChar(100)
DECLARE @ColumnTracking		VarChar(10)
DECLARE @publication		VarChar(5000)
DECLARE @DistributorName	VarChar(100)
SELECT	@DistributorName =	datasource FROM master..sysservers where srvstatus & 8 <> 0 	-- Lookup the Registered Distribution Server
SET	@ManageIdents =	'@auto_identity_range = N''false'', '  					-- This Only Changes For Tables With An Identity Field

IF	@PublisherDBName = ''
  SET	@PublisherDBName = DB_NAME()

IF	@PublisherDBName Is Null
  SET	@PublisherDBName = DB_NAME()

IF	@SubscriberDBName = ''
  SET	@SubscriberDBName = DB_NAME()

IF	@SubscriberDBName Is Null
  SET	@SubscriberDBName = DB_NAME()

-- Enabling the replication database
If (@CreateSub = 1) OR (@CreatePub = 1)
BEGIN
	SET @SQLString = 'use master exec sp_replicationdboption @dbname = N'''+@PublisherDBName+''', @optname = N''merge publish'', @value = N''true'''
	EXEC (@SQLString)
END
ELSE
BEGIN
	If (@UseTables = 1) AND (@UseViews = 1) AND (@UseProcs = 1) AND (@DropSub = 1) AND (@DropPub = 1)
	BEGIN
		SET @SQLString = 'use master exec sp_replicationdboption @dbname = N'''+@PublisherDBName+''', @optname = N''merge publish'', @value = N''false'''
		EXEC (@SQLString)
	END
END

DECLARE	@Publications		TABLE
	(Type 			Char(1)		primary key
	,ManageIdents		VarChar(500)
	,SchemaOption		VarChar(500)
	,ColumnTracking		VarChar(500)
	,TypeName		VarChar(500))

If @UseViews = 1
BEGIN
	INSERT @Publications
	   VALUES ('V', @ManageIdents, '0x0000000000002101', 'false', 'view schema only')	-- CHANGE FOR YOUR NEEDS
END

If @UseProcs = 1
BEGIN
	INSERT @Publications
	   VALUES ('P', @ManageIdents, '0x0000000000002001', 'false', 'proc schema only')	-- CHANGE FOR YOUR NEEDS
END

If @UseTables = 1
BEGIN
	INSERT @Publications
	   VALUES ('U', @ManageIdents, '0x000000000000FFD1', 'true', 'table')			-- CHANGE FOR YOUR NEEDS
END

	
Declare	PublicationCursor	Cursor 
FOR
Select * From @Publications

OPEN PublicationCursor

FETCH NEXT FROM PublicationCursor INTO @Type,@ManageIdents,@SchemaOption,@ColumnTracking,@TypeName

WHILE (@@fetch_status <> -1)
BEGIN

	IF (@@fetch_status <> -2)
	BEGIN

	SET @Publication = @PublisherName + ' ' + @PublisherDBName + ' '+ @TypeName
	
	-- Dropping the merge subscription
	If @DropSub = 1
	   exec sp_dropmergesubscription @publication = @Publication, @subscriber = @SubscriberName, @subscriber_db = @SubscriberDBName, @subscription_type = N'push'


	-- Dropping the merge publication
	If @DropPub = 1
	   exec sp_dropmergepublication @publication = @Publication

	-- Adding the merge publication
	If @CreatePub = 1
	BEGIN
		exec sp_addmergepublication @publication = @Publication, @description = @Publication, @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 = @Publication,@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, @snapshot_job_name = @Publication
		exec sp_grant_publication_access @publication = @Publication, @login = N'BUILTIN\Administrators'
		exec sp_grant_publication_access @publication = @Publication, @login = N'distributor_admin'
		exec sp_grant_publication_access @publication = @Publication, @login = N'sa'

		-- =============================================
		-- CYCLE THROUGH ALL OBJECT OF THE SELECTED TYPE
		-- =============================================
		DECLARE SysObjectsCursor CURSOR
		KEYSET
		FOR
		  SELECT	Name
		  FROM		sysobjects
		  WHERE		type = @Type 				-- CUSTOMIZE TO EXCLUDE PATTERNS
				and category & 2 = 0 
				AND Name Not Like 'SDP_%' 
				AND Name Not Like 'SWL_%' 
				AND Name Not Like 'MSSQLSA_%' 
		ORDER BY 	Name
	
		OPEN SysObjectsCursor
		
		FETCH NEXT FROM SysObjectsCursor INTO @name
	
		
		-- Adding the merge articles
		
		WHILE (@@fetch_status <> -1)
		BEGIN
		
		
		
			IF (@@fetch_status <> -2)
			BEGIN
	
				IF OBJECTPROPERTY ( object_id(@name),'TableHasIdentity') = 1
				   Begin
				   Set @ManageIdents = '@auto_identity_range = N''true'', @pub_identity_range = '+Convert(varchar,@pub_identity_range)+', @identity_range = '+Convert(varchar,@identity_range)+', @threshold = '+Convert(varchar,@threshold)+', '
				   End
				Else
				   Begin
				   SET	@ManageIdents =		'@auto_identity_range = N''false'', '
				   End   
		
				Set @SQLString = 'exec sp_addmergearticle @publication = N'''+@Publication+''', @article = N'''+@name+
					''', @source_owner = N''dbo'', @source_object = N'''+@name+''', @type = N'''+@TypeName+
					''', @description = N'''+@name+' '+@TypeName+''', @column_tracking = N'''+@ColumnTracking+''','+
					' @pre_creation_cmd = N''drop'', @creation_script = null, @schema_option = '+
					@schemaOption +', @article_resolver = null, @subset_filterclause = null, '+
					'@vertical_partition = N''false'', @destination_owner = N''dbo'', '+@ManageIdents+
					'@verify_resolver_signature = 0, @allow_interactive_resolver = N''true'', '+
					'@fast_multicol_updateproc = N''true'', @check_permissions = 7'
				exec (@SQLString)
		
			END
			FETCH NEXT FROM SysObjectsCursor INTO @name
		
		END
		
		CLOSE SysObjectsCursor
		DEALLOCATE SysObjectsCursor
	END
	
	-- Adding the merge subscription
If @CreateSub = 1
	exec sp_addmergesubscription @publication = @Publication, @subscriber = @SubscriberName, @subscriber_db = @SubscriberDBName, @subscription_type = N'push', @subscriber_type = N'local', @subscription_priority = 0.000000, @sync_type = N'automatic', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @enabled_for_syncmgr = N'false', @offloadagent = 0, @use_interactive_resolver = N'false'

	END
	FETCH NEXT FROM PublicationCursor INTO @Type,@ManageIdents,@SchemaOption,@ColumnTracking,@TypeName

END

CLOSE PublicationCursor
DEALLOCATE PublicationCursor

exec sp_msinit_replication_perfmon

Rate

5 (1)

Share

Share

Rate

5 (1)