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_MSenum_merge_subscriptions(nvarchar @publisher, nvarchar @publisher_db
, nvarchar @publication
, bit @exclude_anonymous)
MetaData:
create procedure sys.sp_MSenum_merge_subscriptions ( @publisher sysname, @publisher_db sysname, @publication sysname, @exclude_anonymous bit = 0 ) as begin declare @subscriber sysname declare @subscriber_db sysname declare @subscriber_name sysname declare @type int declare @status int declare @agent_name nvarchar(100) declare @subscriber_id smallint declare @publisher_id smallint declare @start_time nvarchar(24) declare @time nvarchar(24) declare @duration int declare @comments nvarchar(255) declare @delivery_rate float declare @error_id int declare @publication_id int declare @download_inserts int declare @download_updates int declare @download_deletes int declare @download_conflicts int declare @upload_inserts int declare @upload_updates int declare @upload_deletes int declare @upload_conflicts int declare @job_id binary(16) declare @local_job bit declare @profile_id int declare @agent_id int declare @last_timestamp binary(8) declare @offload_enabled bit declare @offload_server sysname ,@subscriber_type tinyint declare @anonymous_subid uniqueidentifier set nocount on -- -- security check -- only replmonitor can execute this -- if not (is_member(N'db_owner') = 1 or isnull(is_member(N'replmonitor'),0) = 1) begin raiserror(14260, 16, -1) return (1) end select @publisher_id = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@publisher) select @publication_id = publication_id from dbo.MSpublications where publisher_id = @publisher_id and publisher_db = @publisher_db and publication = @publication and publication_type = 2 -- Merge create table #merge_subscriptions (subscriber sysname NOT NULL, status int NOT NULL, subscriber_db sysname NOT NULL, type int NOT NULL, agent_name nvarchar(100) NOT NULL, last_action nvarchar(255) NULL, action_time nvarchar(24) NULL, start_time nvarchar(24) NULL, duration int NULL, delivery_rate float NULL, download_inserts int NULL, download_updates int NULL, download_deletes int NULL, publisher_conficts int NULL, upload_inserts int NULL, upload_updates int NULL, upload_deletes int NULL, subscriber_conficts int NULL, error_id int NULL, job_id binary(16) NULL, local_job bit NULL, profile_id int NOT NULL, agent_id int NOT NULL, last_timestamp binary(8) NOT NULL, offload_enabled bit NOT NULL, offload_server sysname NULL, subscriber_type tinyint NULL) -- This is to force all queries to return rows ordered by job_id create unique clustered index ucmerge_subscriptions ON #merge_subscriptions (agent_id) declare hC CURSOR LOCAL FAST_FORWARD FOR select subscriber_id, subscriber_db, name, job_id, local_job, profile_id, id, subscriber_name, offload_enabled, offload_server, anonymous_subid from dbo.MSmerge_agents where publisher_id = @publisher_id and publisher_db = @publisher_db and publication = @publication and (@exclude_anonymous = 0 or anonymous_subid is null) for read only open hC fetch hC into @subscriber_id, @subscriber_db, @agent_name, @job_id, @local_job, @profile_id, @agent_id, @subscriber_name, @offload_enabled, @offload_server, @anonymous_subid while (@@fetch_status <> -1) begin if @anonymous_subid is not NULL begin select @subscriber = @subscriber_name select @subscriber_db = @subscriber_db + '-' + convert(nvarchar(30), @agent_id) select @type = 2 -- anonymous subscription end else begin if @subscriber_id is not NULL select @subscriber = srvname from master.dbo.sysservers where srvid=@subscriber_id else select @subscriber = @subscriber_name select @type = subscription_type from dbo.MSmerge_subscriptions where publisher_id = @publisher_id and publisher_db = @publisher_db and publication_id = @publication_id and UPPER(subscriber) = UPPER(@subscriber) and subscriber_db = @subscriber_db select @subscriber_type = type from MSsubscriber_info where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) end -- Get the status of the agent select @status = 0 select @start_time = NULL, @time = NULL, @duration = NULL, @comments = NULL, @download_inserts = NULL, @download_deletes = NULL, @download_updates = NULL, @download_conflicts = NULL, @upload_inserts = NULL, @upload_deletes = NULL, @upload_updates = NULL, @upload_conflicts = NULL, @delivery_rate = NULL, @error_id = NULL, @last_timestamp = 0x00000000 select @status = isnull(ms.runstatus,0), @start_time = sys.fn_replformatdatetime(ms.start_time), @time = sys.fn_replformatdatetime(ms.end_time), @duration = ms.duration, @comments = rh.comments, @download_inserts = ms.download_inserts, @download_deletes = ms.download_deletes, @download_updates = ms.download_updates, @download_conflicts = ms.download_conflicts, @upload_inserts = ms.upload_inserts, @upload_deletes = ms.upload_deletes, @upload_updates = ms.upload_updates, @upload_conflicts = ms.upload_conflicts, -- Note: return average rate here !!! delivery_rate column is current rate @delivery_rate = ms.delivery_rate, @error_id = rh.error_id, @last_timestamp = ms.timestamp from dbo.MSmerge_sessions ms with (READPAST), dbo.MSmerge_history rh with (READPAST) where ms.agent_id = @agent_id and ms.session_id = (select top 1 session_id from dbo.MSmerge_sessions with (READPAST) where agent_id = @agent_id order by session_id desc) and rh.agent_id = @agent_id and rh.session_id=ms.session_id -- Not currently working Build 351 timestamp = (select top 1 timestamp from dbo.MSmerge_history with (READPAST) where agent_id = @agent_id order by timestamp DESC) -- insert into #merge_subscriptions values ( @subscriber, @status, @subscriber_db, @type, @agent_name, @comments, @time, @start_time, @duration, @delivery_rate, @download_inserts, @download_updates, @download_deletes, @download_conflicts, @upload_inserts, @upload_updates, @upload_deletes, @upload_conflicts, @error_id, @job_id, @local_job, @profile_id, @agent_id, @last_timestamp, @offload_enabled, @offload_server, @subscriber_type) fetch hC into @subscriber_id, @subscriber_db, @agent_name, @job_id, @local_job, @profile_id, @agent_id, @subscriber_name, @offload_enabled, @offload_server, @anonymous_subid end select * from #merge_subscriptions order by job_id asc drop table #merge_subscriptions close hC deallocate hC end
No comments:
Post a Comment