The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_addmergesubscription(nvarchar @publication, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @subscription_type
, nvarchar @subscriber_type
, real @subscription_priority
, nvarchar @sync_type
, int @frequency_type
, int @frequency_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @frequency_subday
, int @frequency_subday_interval
, int @active_start_time_of_day
, int @active_end_time_of_day
, int @active_start_date
, int @active_end_date
, nvarchar @optional_command_line
, nvarchar @description
, nvarchar @enabled_for_syncmgr
, bit @offloadagent
, nvarchar @offloadserver
, nvarchar @use_interactive_resolver
, nvarchar @merge_job_name
, nvarchar @hostname)
MetaData:
create procedure sys.sp_addmergesubscription ( @publication sysname, -- Publication name -- @subscriber sysname = NULL, -- Subscriber server -- @subscriber_db sysname = NULL, -- Subscription database -- @subscription_type nvarchar(15) = 'push', -- Subscription type - push, pull -- @subscriber_type nvarchar(15) = 'local', -- Subscriber type -- @subscription_priority real = NULL, -- Subscription priority -- @sync_type nvarchar(15) = 'automatic', -- subscription sync type -- @frequency_type int = NULL, -- defaults to 4 @frequency_interval int = NULL, -- defaults to 1 @frequency_relative_interval int = NULL, -- defaults to 1 @frequency_recurrence_factor int = NULL, -- defaults to 0 @frequency_subday int = NULL, -- defaults to 8 @frequency_subday_interval int = NULL, -- defaults to 1 @active_start_time_of_day int = NULL, -- defaults to 0 @active_end_time_of_day int = NULL, -- defaults to 235959 @active_start_date int = NULL, -- defaults to 0 @active_end_date int = NULL, -- defaults to 99991231 @optional_command_line nvarchar(4000) = NULL, @description nvarchar(255) = NULL, @enabled_for_syncmgr nvarchar(5) = NULL, -- Enabled for SYNCMGR: true or false -- -- Agent offload @offloadagent bit = 0, @offloadserver sysname = NULL, @use_interactive_resolver nvarchar(5) = NULL, @merge_job_name sysname = NULL, @hostname sysname = NULL ) AS SET NOCOUNT ON -- -- Declarations. -- declare @retcode int declare @subnickname binary(6) declare @priority real declare @subid uniqueidentifier declare @pubid uniqueidentifier -- Publication id -- declare @subscriber_typeid smallint declare @subscriber_srvid int declare @merge_jobid binary(16) -- Scheduler jobid for the merge agent -- declare @subscription_type_id int declare @distproc nvarchar(300) declare @command nvarchar(255) declare @inactive tinyint declare @subscriber_bit smallint declare @global tinyint -- subscriber type is global -- declare @push tinyint -- subscription type is push -- declare @sync_typeid tinyint declare @nosync tinyint declare @automatic tinyint declare @distributor sysname declare @distribdb sysname declare @publisher sysname declare @publisher_db sysname declare @found int declare @datasource_type int DECLARE @platform_nt binary declare @use_interactive_bit bit declare @internal sysname declare @REPOLEVersion_90 int ,@publishingservername sysname declare @compatlevel int -- make sure current database is enabled for merge replication -- exec @retcode=sys.sp_MSCheckmergereplication if @@ERROR<>0 or @retcode<>0 return (1) -- -- Initializations. -- set @datasource_type = 0 -- Default SQL Server -- set @platform_nt = 0x1 SET @nosync = 2 -- Const: synchronization type 'none' -- SET @automatic = 1 -- Const: synchronization type 'automatic' -- set @inactive = 0 SET @subscriber_bit = 4 set @global = 1 set @push = 0 set @pubid = NULL set @publisher = publishingservername() set @publisher_db = DB_NAME() select @found = 1 -- Any non-NULL value is fine -- ,@publishingservername = publishingservername() set @REPOLEVersion_90 = 90 -- -- Parameter Check: @subscription_type. -- Set subscriber_typeid based on the @subscription_type specified. -- -- subscription_type subscription_type -- ================= =============== -- 0 push -- 1 pull -- if LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull') BEGIN RAISERROR (14128, 16, -1) RETURN (1) END IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push' BEGIN set @subscription_type_id = 0 END ELSE BEGIN set @subscription_type_id = 1 END -- -- Parameter Check: @offloadagent. -- IF @offloadagent IS NOT NULL AND @offloadagent != 0 BEGIN -- "Parameter '@offloadagent' is no longer supported." RAISERROR(21698, 16, -1, '@offloadagent') RETURN 1 END IF ISNULL(@offloadserver, N'') != N'' BEGIN -- "Parameter '@offloadserver' is no longer supported." RAISERROR(21698, 16, -1, '@offloadserver') RETURN 1 END -- -- Security Check. -- IF @subscription_type_id = 0 BEGIN exec @retcode = sys.sp_MSreplcheck_publish if @@ERROR <> 0 or @retcode <> 0 return(1) END ELSE BEGIN exec @retcode = sys.sp_MSreplcheck_pull @publication if @@ERROR <> 0 or @retcode <> 0 begin return(1) end END -- With the new security model a number of parameters are invalid IF sys.fn_yukonsecuritymodelrequired(NULL) = 1 BEGIN IF @frequency_type IS NOT NULL OR @frequency_interval IS NOT NULL OR @frequency_relative_interval IS NOT NULL OR @frequency_recurrence_factor IS NOT NULL OR @frequency_subday IS NOT NULL OR @frequency_subday_interval IS NOT NULL OR @active_start_time_of_day IS NOT NULL OR @active_end_time_of_day IS NOT NULL OR @active_start_date IS NOT NULL OR @active_end_date IS NOT NULL OR @optional_command_line IS NOT NULL OR @enabled_for_syncmgr IS NOT NULL OR @merge_job_name IS NOT NULL BEGIN -- "The %s parameter(s) have been deprecated from this procedure. The value(s) should now be specified when calling '%s'." RAISERROR(21838, 10, -1, 'scheduling, optional command line, sync manager and merge job name', 'sp_addmergepushsubscription_agent'' or ''sp_addmergepullsubscription_agent') SELECT @frequency_type = NULL, @frequency_interval = NULL, @frequency_relative_interval = NULL, @frequency_recurrence_factor = NULL, @frequency_subday = NULL, @frequency_subday_interval = NULL, @active_start_time_of_day = NULL, @active_end_time_of_day = NULL, @active_start_date = NULL, @active_end_date = NULL, @optional_command_line = NULL, @enabled_for_syncmgr = NULL, @merge_job_name = NULL END -- @internal = 'YUKON ADD SUB'. Passing this to sp_MSadd_mergesubscription -- signals that we are in 9.0 mode and adding the subscription only (no -- code for adding the agent job will be executed) SELECT @internal = N'YUKON ADD SUB' END ELSE BEGIN -- Set the original default values for the 8.0 model SELECT @enabled_for_syncmgr = ISNULL(@enabled_for_syncmgr, N'false'), @use_interactive_resolver = ISNULL(@use_interactive_resolver, N'false') -- set @internal = 'PRE-YUKON'. This will be passed to sp_MSadd_mergesubscription -- and signals that we are in 8.0 mode and will allow both the code for adding -- the subscription and the agent job to be executed SELECT @internal = N'PRE-YUKON' END -- -- Parameter Check: @subscriber -- Check to make sure that the subscriber is defined -- IF @subscriber IS NULL or ltrim(rtrim(@subscriber)) = N'' BEGIN RAISERROR (14043, 16, -1, '@subscriber', 'sp_addmergesubscription') RETURN (1) END IF @subscriber = 'all' BEGIN RAISERROR (14136, 16, -1) RETURN (1) END EXECUTE @retcode = sys.sp_validname @subscriber IF @@ERROR <> 0 OR @retcode <> 0 RETURN (1) -- -- Parameter Check: @subscriber_db -- IF @subscriber_db IS NULL or ltrim(rtrim(@subscriber_db)) = N'' BEGIN RAISERROR (14043, 16, -1, '@subscriber_db', 'sp_addmergesubscription') RETURN (1) END IF @subscriber_db = 'all' BEGIN RAISERROR (14136, 16, -1) RETURN (1) END -- -- @subscriber_db cannot be master -- if LOWER(@subscriber_db) = 'master' BEGIN RAISERROR (21481, 16, 1) RETURN (1) END -- -- Check to see if system tables exist. If not create them. Since under current -- design every database is qualified for subscribing. -- IF object_id('sysmergesubscriptions') is NULL BEGIN execute @retcode = sys.sp_MScreate_mergesystables @whattocreate=1 if @@ERROR <> 0 or @retcode <> 0 return (1) -- execute @retcode = sys.sp_MScreate_DDLtriggers -- if @@ERROR <> 0 or @retcode <> 0 return (1) execute @retcode= sys.sp_MSrepl_ddl_triggers @type='merge', @mode='add' if @@ERROR <> 0 or @retcode <> 0 return (1) END -- -- Parameter Check: @publication. -- Check to make sure that the publication exists and that it conforms -- to the rules for identifiers. -- if NOT EXISTS (select * FROM dbo.sysmergepublications WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()) BEGIN RAISERROR (20026, 16, -1, @publication) RETURN (1) END if @pubid IS NULL select @pubid = pubid, @compatlevel = backward_comp_level FROM dbo.sysmergepublications WHERE name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name() -- -- Parameter Check: @subscriber_type. -- Set subscriber_typeid based on the @subscriber_type specified. -- -- subscriber_type subscriber_type -- ================= =============== -- 1 global -- 2 local -- 3 anonymous -- Type 'republisher' is taken out for B3. We may want to add this back later. -- if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('local', 'global') BEGIN RAISERROR (21337, 16, -1) RETURN (1) END if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('global') set @subscriber_typeid = 1 else if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('local') set @subscriber_typeid = 2 -- -- Parameter Check: @use_interactive_resolver -- IF @use_interactive_resolver IS NOT NULL AND LOWER(@use_interactive_resolver collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@use_interactive_resolver') RETURN (1) END if LOWER(@use_interactive_resolver collate SQL_Latin1_General_CP1_CS_AS) = 'true' set @use_interactive_bit = 1 else set @use_interactive_bit = 0 -- -- Assign priority appropriately - choose 0.99 times the minimum priority -- of the global replicas. -- if (@subscription_priority >= 100.0 or @subscription_priority < 0.0) BEGIN RAISERROR (20088, 16, -1) RETURN (1) END if (@subscription_priority IS NULL) begin select @priority = 0.99 * min(priority) from dbo.sysmergesubscriptions where subscriber_type = 1 if (@priority IS NOT NULL) select @subscription_priority = @priority if (@subscription_priority IS NULL) select @subscription_priority = 0.0 end -- -- For local and anonymous subscriptions the priority is 0.0 -- if LOWER(@subscriber_type collate SQL_Latin1_General_CP1_CS_AS) IN ('local', 'anonymous') select @subscription_priority = 0.0 -- -- Validate that if Transactional subscriptions exist, that the same article is not subscribed to a -- tran publication. -- IF object_id('syssubscriptions') is not NULL begin select @subscriber_srvid = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber) collate database_default IF @subscriber_srvid IS NOT NULL BEGIN if exists (select name from dbo.sysmergeextendedarticlesview where pubid=@pubid and objid in (select objid from sysextendedarticlesview where artid in (select artid from syssubscriptions where dest_db=@subscriber_db and UPPER(srvname) = UPPER(@subscriber) collate database_default))) begin RAISERROR(21280, 16, -1, @publication, @subscriber_db) RETURN (1) end END end -- -- Parameter Check: Make sure that the subscriber,subscription_db and publisher,publication_db are different -- This proc is called on the publisher and publisher db. -- if ((UPPER(publishingservername()) = UPPER(@subscriber)) and (@subscriber_db = db_name())) begin RAISERROR (21690, 16, -1) RETURN (1) end -- -- Making it possible for a deleted subscription to come back. -- UNDONE : This disallows second pull subscription from being added unless the previous -- subscription was initial synced. -- -- ignore anonymous subscriptions. Entries for it may be present due to subscriber tracking if EXISTS (select * FROM dbo.sysmergesubscriptions WHERE db_name = @subscriber_db AND UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default AND pubid = @pubid AND status <>2 -- We can definitely add back subscriptions that were deleted. and subscription_type <> 2 and subscription_type <> 3) BEGIN RAISERROR (14058, 16, -1) RETURN (1) END -- delete if there exists an anonymous subscriptions as well select @subid = NULL select @subid = subid FROM dbo.sysmergesubscriptions WHERE db_name = @subscriber_db AND UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default AND pubid = @pubid AND (status = 2 or subscription_type = 2 or subscription_type = 3) if @subid is not NULL BEGIN -- delete supportability settings for the subscriptions that we are about to delete. delete from dbo.MSmerge_supportability_settings where subid = @subid delete from dbo.MSmerge_log_files where subid = @subid delete from dbo.sysmergesubscriptions where subid = @subid exec sys.sp_MScleanup_subscriber_history @subid=@subid delete from dbo.MSmerge_replinfo where repid = @subid -- If the only remaining subscriptions are old entries (before restore), -- we remove them now. if not exists (select * from dbo.sysmergesubscriptions where status <> 7) -- REPLICA_STATUS_BeforeRestore begin delete from dbo.sysmergesubscriptions truncate table dbo.MSmerge_supportability_settings truncate table dbo.MSmerge_log_files truncate table dbo.MSrepl_errors truncate table dbo.MSmerge_history truncate table dbo.MSmerge_articlehistory truncate table dbo.MSmerge_sessions delete from dbo.MSmerge_replinfo end END -- -- Downlevel subscribers (8.0 and lower) run merge agents which select the srvid entry of the subscriber from the Publisher -- In order to support that, we check the srvid entry and if not pre-existing, we execute the sp_MSadd_subserver proc that -- does an execute as DBO -- if (@compatlevel < @REPOLEVersion_90) begin SELECT @subscriber_srvid = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber) collate database_default if @subscriber_srvid is NULL begin EXEC @retcode = sys.sp_MSadd_subserver @subscriber = @subscriber, @type = 0 IF @@error <> 0 OR @retcode <> 0 BEGIN RAISERROR (14042, 16, -1) RETURN (1) END end end select @subid = newid() -- -- Parameter Check: @sync_type. -- Set sync_typeid based on the @sync_type specified. -- -- sync_typeid sync_type -- =========== ========= -- 1 automatic -- 2 none -- IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('automatic', 'none') BEGIN RAISERROR (14052, 16, -1) RETURN (1) END -- -- If current publication contains an article without rowguidcol, do not allow no-sync subscription -- IF LOWER(@sync_type collate SQL_Latin1_General_CP1_CS_AS) = 'automatic' BEGIN SET @sync_typeid = @automatic END ELSE BEGIN if exists (select * from dbo.sysmergearticles a where pubid=@pubid and not exists (select * from sys.columns c where c.object_id = a.objid and c.is_rowguidcol = 1)) BEGIN Raiserror(20086, 16, -1, @publication) RETURN (1) END else SET @sync_typeid = @nosync END -- -- UNDONE: Validate that the publisher is of type "republisher" -- begin tran save TRAN addmergesubscription -- Generate a guid for the Subscriber ID -- -- Look for existing nickname from any other subscription -- exec @retcode = sys.sp_MSgetreplnick @subscriber, @subscriber_db , NULL, @subnickname out if (@@error <> 0) or @retcode <> 0 GOTO FAILURE -- Generate a new replica nickname from the @subid -- if (@subnickname is null) begin EXECUTE sys.sp_MSgenreplnickname @srcguid= @subid, @replnick= @subnickname output if @@ERROR<>0 GOTO FAILURE end -- -- The subscription doesn't exist, so let's add it to dbo.sysmergesubscriptions -- INSERT dbo.sysmergesubscriptions (subscriber_server, db_name, pubid, datasource_type, subid, replnickname, replicastate, status, subscriber_type, subscription_type, sync_type, description, priority) VALUES (@subscriber, @subscriber_db, @pubid, @datasource_type, @subid, @subnickname, newid(), @inactive, @subscriber_typeid, @subscription_type_id, @sync_typeid, @description, @subscription_priority) if @@ERROR <> 0 BEGIN GOTO FAILURE END -- -- Get distribution server information for remote RPC call. -- EXECUTE @retcode = sys.sp_helpdistributor @rpcsrvname = @distributor OUTPUT, @distribdb = @distribdb OUTPUT IF @@ERROR <> 0 or @retcode <> 0 BEGIN GOTO FAILURE END declare @publisher_engine_edition int select @publisher_engine_edition = sys.fn_MSrepl_editionid() SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSadd_merge_subscription' EXEC @retcode = @distproc @publisher = @publishingservername, @publisher_db = @publisher_db, @publication = @publication, @subscriber = @subscriber, @subscriber_db = @subscriber_db, @subscription_type = @subscription_type_id, @sync_type = @sync_typeid, @status = @inactive, @frequency_type = @frequency_type, @frequency_interval = @frequency_interval, @frequency_relative_interval = @frequency_relative_interval, @frequency_recurrence_factor = @frequency_recurrence_factor, @frequency_subday = @frequency_subday, @frequency_subday_interval = @frequency_subday_interval, @active_start_time_of_day = @active_start_time_of_day, @active_end_time_of_day = @active_end_time_of_day, @active_start_date = @active_start_date, @active_end_date = @active_end_date, @optional_command_line = @optional_command_line, @merge_jobid = @merge_jobid OUTPUT, @agent_name = @merge_job_name, @hostname = @hostname, @description = @description, @subid = @subid, @internal = @internal, @publisher_engine_edition = @publisher_engine_edition IF @@ERROR <> 0 OR @retcode <> 0 begin goto FAILURE end -- -- Add row for subscription in dbo.MSmerge_replinfo. -- insert dbo.MSmerge_replinfo(repid, login_name, use_interactive_resolver, hostname, merge_jobid) values (@subid, suser_sname(suser_sid()), @use_interactive_bit, @hostname, @merge_jobid) if @@ERROR <> 0 BEGIN GOTO FAILURE END -- Conditional support for MobileSync -- if sys.fn_yukonsecuritymodelrequired(NULL) = 0 AND LOWER(@enabled_for_syncmgr collate SQL_Latin1_General_CP1_CS_AS) = 'true' BEGIN -- MobileSync Support -- declare @distributor_server sysname declare @distributor_security_mode int declare @distributor_login sysname declare @distributor_password nvarchar(524) -- -- The registry entry needs to be created only for push subscriptions - -- i.e - need not be called when a pull subscription is created at the -- subscriber and sp_addmergesubscription is being called then. -- IF @subscription_type_id = 0 BEGIN EXECUTE @retcode = sys.sp_helpdistributor @distributor = @distributor_server OUTPUT -- Distributor RPC server name -- IF @@ERROR <> 0 or @retcode <> 0 BEGIN GOTO FAILURE END -- Always use integrated security on winNT select @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = newid() exec @retcode = sys.sp_MSreplencrypt @distributor_password output IF @@ERROR <> 0 or @retcode <> 0 GOTO FAILURE -- Call sp_MSregistersubscription so that the subscription can be synchronized via Onestop etc. -- exec @retcode = sys.sp_MSregistersubscription @replication_type = 2, @publisher = @publishingservername, @publisher_db = @publisher_db, @publication = @publication, @subscriber = @subscriber, @subscriber_db = @subscriber_db, @distributor = @distributor, @distributor_security_mode = @distributor_security_mode, @distributor_login = @distributor_login, @distributor_password = @distributor_password, @subscription_id = @subid, @subscription_type = @subscription_type_id, @use_interactive_resolver = @use_interactive_bit, @hostname = @hostname IF @@error <> 0 OR @retcode <> 0 BEGIN GOTO FAILURE END END END COMMIT TRAN return (0) FAILURE: RAISERROR (14057, 16, -1) -- UNDONE : This code is specific to 6.X nested transaction semantics -- if @@TRANCOUNT > 0 begin ROLLBACK TRANSACTION addmergesubscription COMMIT TRANSACTION end RETURN (1)
No comments:
Post a Comment