Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_refreshsubscriptions

  No additional text.


Syntax


-- Name: sp_MSrepl_refreshsubscriptions

-- Description: Internal SP to Add article to existing subscriptions

-- Parameter: (see proc below)

-- Returns:

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

create procedure sys.sp_MSrepl_refreshsubscriptions
(
	@publication	sysname,
	@publisher		sysname,
	@publisher_type	sysname
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @article  sysname
                    ,@subscriber sysname
                    ,@dest_db sysname
                    ,@retcode int
                    ,@pubid int
                    ,@immediate_sync bit
                    ,@no_sync tinyint
                    ,@subscription_type_id int
                    ,@subscription_type nvarchar(4)
                    ,@virtual smallint
                    ,@srvid smallint
                    ,@sync_typeid int
                    ,@nosync_type int
                    ,@sync_type nvarchar(100)
                    ,@update_mode_id tinyint
                    ,@update_mode nvarchar(30)

    SELECT @no_sync = 2
                ,@virtual = -1

    /*
    ** Security Check
    */
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Check to see if the database has been activated for publication.
    */
    IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @publication.
    ** Make sure that the publication exists
    */

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

    
    -- Verify publication exists
    
	SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)

	IF (@pubid IS NULL)
	BEGIN
		RAISERROR (20026, 11, -1, @publication)
		RETURN (1)
	END

    /* Add real subscription to the new articles  */
    /* Open a cursor on all the pending subscriptions, that is */
    /* All the subscriptions on the publication that */
    /* are not on an article in the publication. */
    /* not including virtual subscriptions */

    DECLARE hCrefreshsubscriptions CURSOR LOCAL FAST_FORWARD FOR
        SELECT DISTINCT art1.name, subs1.dest_db, subs1.srvid, subs1.srvname
            FROM syssubscriptions subs1, sysextendedarticlesview art1
            WHERE art1.pubid = @pubid AND
                  subs1.srvid <> @virtual AND
                  EXISTS (SELECT * FROM syssubscriptions subs2, sysextendedarticlesview art2
                    WHERE subs2.srvid = subs1.srvid AND
                          subs2.dest_db = subs1.dest_db AND
                          subs2.artid = art2.artid AND
                          art2.pubid = @pubid) AND
                  NOT EXISTS ( SELECT * FROM syssubscriptions subs3
                    WHERE  subs3.artid = art1.artid AND
                           subs3.srvid = subs1.srvid AND
                           subs3.dest_db = subs1.dest_db)
    FOR READ ONLY
    OPEN hCrefreshsubscriptions
    FETCH hCrefreshsubscriptions INTO @article,  @dest_db, @srvid, @subscriber


    WHILE (@@fetch_status <> -1)
    BEGIN

        /*
        ** Get subscription meta data common to this publication
        */
        SELECT @subscription_type_id = subs.subscription_type
            ,@sync_typeid = subs.sync_type
            ,@nosync_type = subs.nosync_type
            ,@update_mode_id = subs.update_mode
         from
            sysextendedarticlesview art, syssubscriptions subs where
            art.pubid = @pubid AND
            subs.srvid = @srvid AND
            subs.dest_db = @dest_db AND
            subs.artid = art.artid

        /*
        ** only do it 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
            subs.dest_db = @dest_db AND
            subs.artid = art.artid AND
            (subscription_type <> @subscription_type_id OR
            sync_type <> @sync_typeid OR
            update_mode != @update_mode_id))
        BEGIN
            SELECT @subscription_type = case when (@subscription_type_id = 0) then 'push' else 'pull' end
                ,@sync_type = case
                		when (@sync_typeid = 1) then 'automatic'
						when (@sync_typeid = 2 and @nosync_type in (1, 2, 3)) then 'replication support only'
						else 'none' end
                ,@update_mode = case
                        when (@update_mode_id = 0) then N'read only'
                        when (@update_mode_id = 1) then N'sync tran'
                        when (@update_mode_id in (2,4)) then N'queued tran'
                        when (@update_mode_id in (3,5)) then N'failover'
                        when (@update_mode_id in (6,7)) then N'queued failover'
                        else N'bad update mode' end

			
            EXECUTE @retcode =	sys.sp_addsubscription
								@publication		= @publication,
								@article			= @article,
								@subscriber			= @subscriber,
								@destination_db		= @dest_db,
								@sync_type			= @sync_type,
								@status				= NULL,
								@subscription_type	= @subscription_type,
								@update_mode        = @update_mode,
								@reserved			= 'internal',
								@publisher			= @publisher

            IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
                CLOSE hCrefreshsubscriptions
                DEALLOCATE hCrefreshsubscriptions
                RETURN (1)
            END
        END
        FETCH hCrefreshsubscriptions INTO @article, @dest_db, @srvid, @subscriber
    END

    CLOSE hCrefreshsubscriptions
    DEALLOCATE hCrefreshsubscriptions
END

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_refreshsubscriptions (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