Merge Wizard

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

CREATE PROCEDURE sp_MergeWizard 

@PublisherNameVarChar(100)= @@ServerName-- The Current Server
 ,@PublisherDBNameVarChar(100)= ''-- The Current Database
,@SubscriberNameVarChar(100)= @@ServerName-- Not sure if or how to automate this
,@SubscriberDBNameVarChar(100)= ''-- Not sure if or how to automate this
,@UseTablesBit = 1-- USE All Tables (SCHEMA AND DATA) and automitcaly handle the ranges for Identities
,@UseViewsBit = 1-- USE All Views (SCHEMA ONLY)
,@UseProcsBit = 1-- USE All Procedures (SCHEMA ONLY)
,@DropSubBit = 1-- DROP The Selected Subscriptions
,@DropPubBit = 1-- DROP The Selected Publications
,@CreateSubBit = 1-- CREATE The Selected Subscriptions
,@CreatePubBit = 1-- CREATE The Selected Publications
,@pub_identity_range INT= 10000000-- IDENTITY BLOCK FOR SUBSCRIBERS
,@identity_rangeINT= 10000000-- IDENTITY BLOCK FOR PUBLISHER
,@thresholdINT= 80-- IDENTITY VALUES USED BEFORE ASSIGNING NEW BLOCK
AS
-- ============================================================================================================================
-- ============================================================================================================================
--
-- CREATED BY STEVE LEDRIDGE
-- LAST MODIFIED09/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 @TypeVarChar(25)
DECLARE @TypeNameVarChar(25)
DECLARE @SQLStringVarChar(5000)
DECLARE @ManageIdentsVarChar(500)
DECLARE @schemaOptionVarChar(100)
DECLARE @ColumnTrackingVarChar(10)
DECLARE @publicationVarChar(5000)
DECLARE @DistributorNameVarChar(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@PublicationsTABLE
(Type Char(1)primary key
,ManageIdentsVarChar(500)
,SchemaOptionVarChar(500)
,ColumnTrackingVarChar(500)
,TypeNameVarChar(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


DeclarePublicationCursorCursor 
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
  SELECTName
  FROMsysobjects
  WHEREtype = @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