Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_helpsubscription

  No additional text.


Syntax


-- Name:
--          sp_MSrepl_helpsubscription

-- Description:
--          Lists subscription information associated with a particular publication, article,
--			Subscriber, or set of subscriptions. This stored procedure is executed at a Publisher
--			on the publication database.

-- Security:
--          Internal
-- Requires Certificate signature for catalog access

-- Returns:
--          Result set of subscription properties

-- Owner:
--          

create procedure sys.sp_MSrepl_helpsubscription
(
	@publication	sysname,
	@article		sysname,
	@subscriber		sysname,
	@destination_db	sysname,
	@found			int OUTPUT,
	@publisher		sysname,
	@publisher_type	sysname
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode				int
    DECLARE @subscriber_bit			smallint
    DECLARE @no_row					bit
    DECLARE @srvid					smallint
    DECLARE @pubid					int
    DECLARE @artid					int
    DECLARE @immediate_sync			bit
    DECLARE @subscription_type_id	int
    DECLARE @sync_typeid 			int
    DECLARE @publish_bit			int
    DECLARE @orig_publication		sysname
    DECLARE @full_subscription		bit
    DECLARE @distributor    		sysname
    DECLARE @distributiondb 		sysname
    DECLARE @distproc       		NVARCHAR(255)
    DECLARE @dbname         		sysname

	DECLARE @publication_ids TABLE
	(
		pubid	int
	)
	
	SELECT @publish_bit = 1
    SELECT @distributor = NULL
    SELECT @distributiondb = NULL
    SELECT @distproc = NULL
    SELECT @dbname = NULL
    SELECT @orig_publication = @publication
    SELECT @publisher = ISNULL(@publisher, publishingservername())

	SET @retcode = 0

    /* Security check. To public. */
    /* NOTE: Security check is part of the output after the cursor has been run */

    /*
    ** Check if the database is published.
    */
    IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Initializations of @now_row.
    */
    IF @found = 23456
    BEGIN
        SELECT @no_row=0
    END
    ELSE
    BEGIN
        SELECT @no_row=1
    END

    /*
    ** Initializations.
    */
    SELECT @subscriber_bit = 4

    /*
    ** Parameter Check:  @subscriber.
    */
    IF @subscriber IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@subscriber', 'sp_MSrepl_helpsubscription')
		RETURN (1)
	END

    /*
    ** Parameter Check:  @subscriber.
    ** Check if remote server is defined as a subscription server, and
    ** that the name conforms to the rules for identifiers.
    */

    IF @subscriber <> '%'
	BEGIN

		EXECUTE @retcode = sys.sp_validname @subscriber
		select @subscriber = UPPER(@subscriber)

		IF @retcode <> 0
			RETURN (1)

	END

    /*
    ** Parameter Check:  @publication.
    ** If the publication name is specified, check to make sure that it
    ** conforms to the rules for identifiers and that the publication
    ** actually exists.  Disallow NULL.
    */

    IF @publication IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_helpsubscription')
		RETURN (1)
	END

	-- Get list of matching publication id's for
	-- this publisher/type combo
	INSERT INTO @publication_ids
	SELECT pubid
	FROM   sys.fn_IHgetpubid(@publication, @publisher, @publisher_type)
	
    IF @publication <> '%'
	BEGIN
		EXECUTE @retcode = sys.sp_validname @publication

		IF @retcode <> 0
		BEGIN
			RETURN (1)
		END

		IF NOT EXISTS
		(
			SELECT	*
			FROM	@publication_ids
		)
		BEGIN
		   RAISERROR (20026, 11, -1, @publication)
		   RETURN (1)
		END
	END

    /*
    ** Parameter Check:  @article.
    ** If the article name is specified, check to make sure that it
    ** conforms to the rules for identifiers and that the article
    ** actually exists.  Disallow NULL.
    **
    ** If @article is 'all', only return one entry for the whole publication
    ** for full subscriptions (subscriptions inlcluding all the articles in a
    ** publication).
    **
    */

    IF @article IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_helpsubscription')
		RETURN (1)
	END

    IF LOWER(@article) <> 'all'
    BEGIN
        IF @article <> '%'
		BEGIN
			IF NOT EXISTS
			(
				SELECT	*
				FROM	sysextendedarticlesview sv,
						@publication_ids pi
				WHERE	sv.name  = @article
				  AND	sv.pubid = pi.pubid
			)
			BEGIN
				RAISERROR (20027, 11, -1, @article)
				RETURN (1)
			END
		END


        IF EXISTS
        (
        	SELECT	*
              FROM	syssubscriptions sub,
					syspublications pub,
					sysextendedarticlesview art,
					@publication_ids pi
             WHERE	((@subscriber = N'%') OR (sub.srvname = UPPER(@subscriber) ))
				and (sub.srvname is not null and len(sub.srvname)> 0)
				AND	((@publication = N'%') or (pub.name = @publication))
               AND	 ((@article = N'%') or (art.name = @article))
               AND	art.pubid = pub.pubid
               AND	sub.artid = art.artid
               AND	((@destination_db = N'%') OR (sub.dest_db = @destination_db))
               AND	(sub.login_name = suser_sname(suser_sid()) OR
                    is_srvrolemember('sysadmin') = 1 OR
                    is_member ('db_owner') = 1)
               AND	pub.pubid = pi.pubid
		)
        BEGIN
            SELECT @found = 1
            IF @no_row <> 0 RETURN (0)
        END
        ELSE
        BEGIN
            SELECT @found = 0
            RETURN(0)
        END
    END

    CREATE TABLE #helpsubscription
    (
		subscriber			sysname collate database_default not null,
		publication			sysname collate database_default not null,
		article				sysname collate database_default not null,
		destination_db		sysname collate database_default not null,
		status				tinyint NOT NULL,
		sync_type			tinyint NOT NULL,
		subscription_type	int NOT NULL,
		full_subscription	bit NOT NULL,
		distribution_jobid	binary(16) NULL,
		subscription_name	nvarchar(386) collate database_default not null,
		-- SyncTran
		update_mode			int NOT NULL,
		loopback_detection	bit not null
    )

    /* Open a CURSOR LOCAL FOR subscriber/destination_db and publication pair
    **
    ** Get subscriptions
    ** sa or dbo can see every subscriptions while
    ** others only see their own.
    */

    /*
    ** Performance Optimization: Eliminate the 'LIKE' clause for publication name.
    **                           Empirical evidence shows almost 50% speed improvement when
    **                           opening the cursor if publication name is provided.
    */
    IF (@publication <> '%')
        DECLARE hChelpsubscription_pub CURSOR LOCAL FAST_FORWARD FOR
            SELECT	DISTINCT sub.srvname,
                   	pub.name,
                   	sub.dest_db,
                   	pub.pubid,
                   	sub.srvid,
                   	pub.immediate_sync
              FROM	syssubscriptions sub,
					syspublications pub,
					sysextendedarticlesview art,
					@publication_ids pi
             WHERE	((@subscriber = N'%') OR (sub.srvname = UPPER(@subscriber) ))
				and (sub.srvname is not null and len(sub.srvname)> 0)
               AND	pub.name = @publication collate database_default
               AND	art.pubid = pub.pubid
               AND	sub.artid = art.artid
               AND	((@destination_db = N'%') OR (sub.dest_db = @destination_db))
               AND	(sub.login_name = suser_sname(suser_sid()) OR
                        is_srvrolemember('sysadmin') = 1 OR
                        is_member ('db_owner') = 1)
		       AND	pub.pubid = pi.pubid
               FOR READ ONLY
    ELSE
        DECLARE hChelpsubscription_pub CURSOR LOCAL FAST_FORWARD FOR
            SELECT	DISTINCT sub.srvname,
					pub.name,
					sub.dest_db,
					pub.pubid,
					sub.srvid,
					pub.immediate_sync
              FROM	syssubscriptions sub,
					syspublications pub,
					sysextendedarticlesview art,
					@publication_ids pi
             WHERE	((@subscriber = N'%') OR (sub.srvname = UPPER(@subscriber) ))
				and (sub.srvname is not null and len(sub.srvname)> 0)
               AND	art.pubid = pub.pubid
               AND	sub.artid = art.artid
               AND	((@destination_db = N'%') OR (sub.dest_db = @destination_db))
               AND	(sub.login_name = suser_sname(suser_sid()) OR
                        is_srvrolemember('sysadmin') = 1 OR
                        is_member ('db_owner') = 1)
		       AND	pub.pubid = pi.pubid
               FOR READ ONLY

    OPEN	hChelpsubscription_pub
    FETCH	hChelpsubscription_pub
    INTO	@subscriber,
    		@publication,
			@destination_db,
			@pubid,
			@srvid,
			@immediate_sync

    WHILE (@@fetch_status <> -1)
    BEGIN

        /*
        ** Is it a full subscription ? i.e. Does it include all the articles?
        **
        */
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	sysextendedarticlesview art
        	WHERE	art.pubid = @pubid
        	  AND	NOT EXISTS
        	  		(
        	  			SELECT	*
        	  			FROM	syssubscriptions sub
        	  			WHERE	sub.artid = art.artid
        	  			  AND	sub.srvid = @srvid
        	  			  AND	((@destination_db = N'%') OR (sub.dest_db = @destination_db))
        	  		)
        )
        BEGIN
            /* Do all the subscriptions on the publication have same
            ** sync_type and subscription_type ?
            */

            /*
            ** Get subscription type on the publication
            */
            SELECT	@subscription_type_id = subs.subscription_type,
					@sync_typeid = subs.sync_type
			FROM	sysextendedarticlesview art,
					syssubscriptions subs
			WHERE	art.pubid = @pubid
			  AND	subs.srvid = @srvid
			  AND	((@destination_db = N'%') OR (subs.dest_db = @destination_db))
			  AND	subs.artid = art.artid


            /*
            ** if the subscription all have the same subscription type
            ** and sync_type
            */
            IF NOT EXISTS
            (
            	SELECT	*
            	FROM	sysextendedarticlesview art,
            			syssubscriptions subs
            	WHERE	art.pubid = @pubid
            	  AND	subs.srvid = @srvid
            	  AND	((@destination_db = N'%') OR (subs.dest_db = @destination_db))
            	  AND	subs.artid = art.artid
            	  AND	(subscription_type <> @subscription_type_id
            	  			OR sync_type <> @sync_typeid)
            )
            BEGIN
            	SELECT @full_subscription = 1
            END
            ELSE
            BEGIN
                SELECT @full_subscription = 0
			END
        END
        ELSE
        BEGIN
            SELECT @full_subscription = 0
        END

        /*
        ** If it is a full subscription and the @article is 'all',
        ** only return one entry for the whole publication.
        ** Always return one row per publication if @article is 'ALL'
        */
        IF LOWER(@article) = 'all'
        BEGIN
            INSERT INTO #helpsubscription
			SELECT	TOP 1
					@subscriber,
					@publication,
					@article,
					@destination_db,
					sub.status,
					case sub.sync_type
                        when 2 then
                            case sub.nosync_type
                                when 3 then 5
                                when 2 then 4
                                when 1 then 3
                                else 2
                            end
                        else sub.sync_type
                    end,
					sub.subscription_type,
					@full_subscription,
					sub.distribution_jobid,
					@subscriber + ':' + @destination_db  ,
					-- NOTE: For Queued case: we will always the following
					-- enumeration for update mode as the initial failover
					-- state is contained within the value of the update mode
					
					-- For update mode = 2,4 return 2 (Queued only)
					-- For update mode = 3,5 return 3 (Immediate with Queued as failover)
					-- For update mode = 6,7 return 4 (Queued with Immediate as failover)
					
					case
						when sub.update_mode = 4 then 2
						when sub.update_mode = 5 then 3
						when sub.update_mode in (6,7) then 4
						else sub.update_mode
					end,
					sub.loopback_detection
					-- end SyncTran
			FROM	syssubscriptions sub, sysextendedarticlesview art
			WHERE	sub.srvid = @srvid
			  AND	((@destination_db = N'%') OR (sub.dest_db = @destination_db))
			  AND	sub.artid = art.artid
			  AND	art.pubid = @pubid
        END
        ELSE
        BEGIN
            /*
            ** Get subscriptions
            ** sa or dbo can see every subscriptions while
            ** others only see their own.
            */

            INSERT INTO #helpsubscription
			SELECT	@subscriber,
					@publication,
					art.name,
					@destination_db,
					sub.status,	
					case sub.sync_type
                        when 2 then
                            case sub.nosync_type
                                when 3 then 5
                                when 2 then 4
                                when 1 then 3
                                else 2
                            end
                        else sub.sync_type
                    end,
					sub.subscription_type,
					@full_subscription,
					sub.distribution_jobid,
					@subscriber + ':' + @destination_db + ':' + art.name,
					-- NOTE: For Queued case: we will always the following
					-- enumeration for update mode as the initial failover
					-- state is contained within the value of the update mode
					
					-- For update mode = 2,4 return 2 (Queued only)
					-- For update mode = 3,5 return 3 (Immediate with Queued as failover)
					-- For update mode = 6,7 return 4 (Queued with Immediate as failover)
					
					case
						when sub.update_mode = 4 then 2
						when sub.update_mode = 5 then 3
						when sub.update_mode in (6,7) then 4
						else sub.update_mode
					end,
					sub.loopback_detection
					-- end SyncTran
			FROM	syssubscriptions sub,
					sysextendedarticlesview art
			WHERE	sub.srvid = @srvid
			  AND	((@destination_db = N'%') OR (sub.dest_db = @destination_db))
			  AND	art.pubid = @pubid
			  AND	 ((@article = N'%') or (art.name = @article))
			  AND	sub.artid = art.artid
			  AND	(sub.login_name = suser_sname(suser_sid())
			  			OR is_srvrolemember('sysadmin') = 1
			  			OR is_member ('db_owner') = 1)
        END
        FETCH	hChelpsubscription_pub
        INTO	@subscriber,
        		@publication,
				@destination_db,
				@pubid,
				@srvid,
				@immediate_sync
    END

    CLOSE hChelpsubscription_pub
    DEALLOCATE hChelpsubscription_pub

	-- Include 9.0 subscriber types in the table
	CREATE TABLE #dist_agent_properties
	(
		job_id                  	VARBINARY(16) NULL,
		offload_enabled         	bit NULL,
		offload_server          	sysname collate database_default null,
		dts_package_name        	sysname collate database_default null,
		dts_package_location    	int NULL,
		status                  	int NULL,
		subscriber_security_mode	smallint NULL,
		subscriber_login			sysname NULL,
		job_login					sysname NULL,
		distrib_agent_name          nvarchar(100) NULL,
		subscriber_type				tinyint NULL,
		subscriber_provider			sysname NULL,
		subscriber_datasource		nvarchar(4000) NULL,
		subscriber_providerstring	nvarchar(4000) NULL,
		subscriber_location			nvarchar(4000) NULL,
		subscriber_catalog			sysname NULL
	)

    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher	= @publisher,
														@rpcsrvname	= @distributor OUTPUT,
														@distribdb	= @distributiondb OUTPUT
    IF @retcode <> 0
        RETURN @retcode

    SELECT @distributor = RTRIM(@distributor)

    -- Get distribution agent properties
    SELECT @distproc =	QUOTENAME(@distributor) + '.' +
    					QUOTENAME(@distributiondb) +
						'.dbo.sp_MSenumdistributionagentproperties'

    SELECT @dbname = db_name()

	INSERT INTO #dist_agent_properties
	EXEC @retcode = @distproc	@publisher		= @publisher,
								@publisher_db	= @dbname,
								@publication	= @orig_publication,
								@show_security	= 1
	
    /*
    ** Get subscriptions
    */
	SELECT	hs.subscriber as [subscriber],
			hs.publication as [publication],
			hs.article as [article],
			hs.destination_db as [destination database],
			-- distributionstatus	= 0 means that the subscription has been deactivated.
			case
				when hs.status = 2 and ap.status = 0 then 0
				else hs.status
			end as [subscription status],
			hs.sync_type as [synchronization type],
			hs.subscription_type as [subscription type],
			hs.full_subscription as [full subscription],
			hs.subscription_name as [subscription name],
			-- SyncTran
			hs.update_mode as [update mode],
			ap.job_id as [distribution job id],
			hs.loopback_detection as [loopback_detection],
			ap.offload_enabled as [offload_enabled],
			ap.offload_server as [offload_server],
			ap.dts_package_name as [dts_package_name],
			ap.dts_package_location as [dts_package_location],
			ap.subscriber_security_mode as [subscriber_security_mode],
			ap.subscriber_login as [subscriber_login],
			'**********' as [subscriber_password],
			ap.job_login as [job_login],
			'**********' as [job_password],
			ap.distrib_agent_name,
			ap.subscriber_type,
			ap.subscriber_provider,
			ap.subscriber_datasource,
			ap.subscriber_providerstring,
			ap.subscriber_location,
			ap.subscriber_catalog
	FROM	#helpsubscription hs
  		LEFT OUTER JOIN #dist_agent_properties ap
    		ON	hs.distribution_jobid = ap.job_id
	ORDER BY subscriber, publication, article
	
	DROP TABLE #dist_agent_properties
END

 
Last revision 2008RTM
See also

  sp_helpsubscription (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