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
