Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MShelp_publication

  No additional text.


Syntax

CREATE  PROCEDURE sys.sp_MShelp_publication (
@publisher sysname,
@publisher_db sysname = '%',
@publication sysname = '%')
as
BEGIN
    set nocount on
    declare	@queued_agent sysname
    declare @retcode int
    declare @pubid int

    SELECT @retcode = 0

    /*
    ** Create a temp table of pubids identifying publications that the current user has access to
    */
    CREATE TABLE #accessiblepubs (pubid int)

    DECLARE hC  CURSOR LOCAL FAST_FORWARD FOR
        SELECT publication_id FROM MSpublications WHERE ((@publication = N'%') or (publication = @publication))
    OPEN hC
    FETCH hC INTO @pubid
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF is_member(N'db_owner') <> 1 AND isnull(is_member(N'replmonitor'),0) <> 1
        BEGIN
            exec @retcode = sys.sp_MScheck_pull_access
                @publication_id = @pubid,
                @raise_fatal_error = 0
	END			
        IF (is_member(N'db_owner') = 1) OR
           (isnull(is_member(N'replmonitor'),0) = 1) OR
           (@retcode = 0 AND @@error = 0)
           INSERT INTO #accessiblepubs values(@pubid)

        FETCH hC INTO @pubid
    END
    CLOSE hC
    DEALLOCATE hC

    if @publisher_db != '%' and @publication != '%' and not exists (select * from MSpublications where
        publisher_id = (select srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@publisher)) and
        ((@publisher_db = N'%') or (publisher_db = @publisher_db)) and
        publication = @publication)
    begin
        raiserror(20026, 16, -1, @publication)
        return(1)
    end

	select @queued_agent = name from MSqreader_agents

	-- need to add new columns:
	-- num_subscriptions, num_now_syncing, num_errors, num_warning
	-- highest_perf_threshold, lowest_perf_threshold, 	
	
	    declare @currentdate datetime
	    set @currentdate=getdate()

	declare @dymanic_info table(
		publication_id int primary key,
		num_subscriptions int,
		num_now_syncing int,
		num_errors int,
		num_warning int,
		merge_RunFastDurationThreshold int,
		merge_SyncExpireTimeThreshold int,
		WorstWarningDuration int,
		WorstWarningExpiration int
		);

		-- first we select all the values returned from the functions calls
		-- because other wise they will be called for every row maching the main query.
		insert into @dymanic_info
		select p.publication_id,
		sys.fn_replgetNumMergesubscriptionsOnPublication(p.publisher_id, p.publisher_db, p.publication, p.publication_id) as num_subscriptions,
        sys.fn_replgetNumMergesubscriptionsNowSyncingOnPublication(p.publisher_id, p.publisher_db, p.publication, p.publication_id) as num_now_syncing,
        sys.fn_replgetNumOfErrorsMergesubscriptionsOnPublication(p.publisher_id, p.publisher_db, p.publication, p.publication_id) as num_errors,
        sys.fn_replgetNumOfWarningsMergesubscriptionsOnPublication(
        	p.publisher_id,
        	p.publisher_db,
        	p.publication,
        	p.publication_id,
        	p.retention,
            convert(int, sys.fn_replgetpublicationthreshold(p.publication_id, 4)),
        	convert(int, sys.fn_replgetpublicationthreshold(p.publication_id, 5)),
        	@currentdate
        ) as num_warning,
        convert(int, sys.fn_replgetpublicationthreshold(p.publication_id, 5))  as merge_RunFastDurationThreshold,
         sys.fn_replgetWorstWarningDurationMergesubscriptionsOnPublication(
        	p.publisher_id,
        	p.publisher_db,
        	p.publication,
        	p.publication_id) as WorstWarningDuration,
        convert(int, sys.fn_replgetpublicationthreshold(p.publication_id, 4)) as merge_SyncExpireTimeThreshold,
        sys.fn_replgetWorstWarningExpirationMergesubscriptionsOnPublication(
        	p.publisher_id,
        	p.publisher_db,
        	p.publication,
        	p.publication_id,
        	@currentdate) as WorstWarningExpiration    	
			from MSpublications p	
		where
        p.publisher_id = (select srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@publisher))
		and ((@publisher_db = N'%') or (p.publisher_db = @publisher_db)) and   ((@publication = N'%') or (p.publication = @publication))

	


       select p.publisher_db as publisher_db, p.publication as publication, p.publication_id as publication_id,
       p.publication_type as publication_type,
        --thirdparty_flag,
        independent_agent as independent_agent,
        immediate_sync as immediate_sync, allow_push as allow_push,
        allow_pull as allow_pull, allow_anonymous as allow_anonymous, 'snapshot_agent' = s.name,
        'logreader_agent' = l.name, p.description as description, vendor_name as vendor_name
        -- queue reader agent only if we have valid queued subscription
        ,'queuereader agent' = case
        	when (exists (select * from MSsubscriptions sub where sub.publisher_id = p.publisher_id and
						sub.publisher_db = p.publisher_db and sub.publication_id = p.publication_id and
						(sub.update_mode in (2,3,4,5,6,7))))
			then @queued_agent else NULL end
		,'thirdparty_options' = thirdparty_options
		,'snapshot_jobid' = j.job_id
        -- dynamic columns for repl monitor
        ,d.num_subscriptions as num_subscriptions
        ,d.num_now_syncing as num_now_syncing
        ,d.num_errors as num_errors
        ,d.num_warning as num_warning
        ,d.merge_RunFastDurationThreshold  as merge_RunFastDurationThreshold
        ,d.WorstWarningDuration as WorstWarningDuration
        ,d.merge_SyncExpireTimeThreshold as merge_SyncExpireTimeThreshold
        ,p.retention as retention
        ,d.WorstWarningExpiration as WorstWarningExpiration
        from #accessiblepubs a
		JOIN @dymanic_info d
		ON
		d.publication_id = a.pubid

        LEFT OUTER JOIN MSpublications p
        ON
        p.publication_id = a.pubid
        LEFT OUTER JOIN MSsnapshot_agents s
        ON
        s.publisher_id = p.publisher_id and
        s.publisher_db = p.publisher_db and
        s.publication = p.publication
        LEFT OUTER JOIN MSlogreader_agents l
        ON
        (l.publisher_id = p.publisher_id and
        l.publisher_db = p.publisher_db and
        p.publication_type = 0 and              -- Only Transactional Publication has a  Log Reader
        (l.publication = p.publication OR
         thirdparty_flag = 0))
		LEFT OUTER JOIN msdb.dbo.sysjobs_view j
		ON
		s.job_id = j.job_id
      where
        p.publisher_id = (select srvid from master.dbo.sysservers where UPPER(srvname collate database_default) = UPPER(@publisher)) and
        ((@publisher_db = N'%') or (p.publisher_db = @publisher_db)) and
       ((@publication = N'%') or (p.publication = @publication))
        order by p.publication

END

 
Last revision 2008RTM
See also

  sp_instdist (Procedure)
sp_MSdrop_repltran (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash