In our environment, we needed to create and drop replication over and over in our test environment and I got really tired of having to manually go to each table with an identity column to set its ranges. This stored procedure 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 a lot 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')
begin
drop procedure sp_MergeWizard
end;
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
begin
-- ========================================================================
-- Database Merge Wizard (sp_MergeWizard)
--
-- This will create publications and subscriptions for all objects in a
-- database.
--
-- In our environment, we needed to create and drop replication over and over in
-- our test environment and I got really tired of having to manually go to each
-- table with an identity column to set its ranges. This stored procedure 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 a lot of tweaking
-- for your needs, but it works well for us.
--
-- http://www.sqlservercentral.com/scripts/Replication/30064/
-- ========================================================================
/* EXAMPLE USAGE
---------------------------------------------------------------------------
-- Declare Parameters
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
-- Execute the Stored Procedure
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 = ''
begin
set @PublisherDBName = db_name()
end;
if @PublisherDBName is null
begin
set @PublisherDBName = db_name()
end;
if @SubscriberDBName = ''
begin
set @SubscriberDBName = db_name()
end;
if @SubscriberDBName is null
begin
set @SubscriberDBName = db_name()
end;
-- 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 into @Publications
values ('V', @ManageIdents, '0x0000000000002101', 'false', 'view schema only'); -- CHANGE FOR YOUR NEEDS
end;
if @UseProcs = 1
begin
insert into @Publications
values ('P', @ManageIdents, '0x0000000000002001', 'false', 'proc schema only'); -- CHANGE FOR YOUR NEEDS
end;
if @UseTables = 1
begin
insert into @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
begin
exec sp_dropmergesubscription @publication = @Publication, @subscriber = @SubscriberName, @subscriber_db = @SubscriberDBName, @subscription_type = N'push'
end;
-- Dropping the merge publication
if @DropPub = 1
begin
exec sp_dropmergepublication @publication = @Publication
end;
-- 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
begin
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;
end;
fetch next from PublicationCursor into
@Type,
@ManageIdents,
@SchemaOption,
@ColumnTracking,
@TypeName;
end;
close PublicationCursor;
deallocate PublicationCursor;
exec sp_msinit_replication_perfmon;
end;