Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_expired_subscription_cleanup

  No additional text.


Syntax

CREATE PROCEDURE sys.sp_expired_subscription_cleanup
(
	@publisher sysname = NULL
)
AS
BEGIN
    declare	@retcode         int,
    		@publisher_db    sysname,
    		@publisher_type  sysname,
			@category        int,
			@proc_name       nvarchar(200),
			@distributor     sysname,
			@distribdb       sysname,
			@distproc        nvarchar(300)

    -- Security Check is done in called procedures

    -- Get distribution server information for remote RPC call.
    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher      = @publisher,
    												 @rpcsrvname     = @distributor OUTPUT,
    												 @distribdb      = @distribdb OUTPUT,
    												 @publisher_type = @publisher_type OUTPUT

    IF (@@ERROR != 0 OR @retcode != 0)
    BEGIN
        RETURN (@retcode)
    END

	IF (@distributor IS NULL OR @distribdb IS NULL)
    BEGIN
        IF EXISTS (SELECT name FROM sys.databases WHERE sys.fn_MSrepl_ispublished(name) = 1)
        BEGIN
            RAISERROR (20036, 16, -1)
            return (1)
        END
        ELSE
        BEGIN
            return (0)
        END
    END

	IF (@publisher_type != N'MSSQLSERVER')
	BEGIN
		-- Heterogeneous publisher subscription cleanup
		SELECT @proc_name = @distribdb + N'.sys.sp_MSdrop_expired_subscription'
		EXEC @retcode = @proc_name	@publisher = @publisher
	END
	ELSE
    BEGIN
    	-- SQL publisher - sweep all the published databases
	    DECLARE DC CURSOR LOCAL FAST_FORWARD for
	    SELECT DISTINCT name, category
	    FROM   master.dbo.sysdatabases
	    WHERE ((category & 4) = 4
	       OR (category & 1 = 1))
			and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
	       and has_dbaccess(name) = 1
	    FOR READ ONLY
	
	    OPEN DC
	    FETCH DC INTO @publisher_db, @category
	
	    WHILE (@@fetch_status != -1)
	    BEGIN
	    	-- Merge publication db
	        IF (@category & 4 = 4)
            BEGIN
                SELECT @proc_name = @publisher_db + '.sys.sp_MSdrop_expired_mergesubscription'
                EXEC @retcode = @proc_name

                IF (@retcode != 0 OR @@ERROR != 0)
                BEGIN
                    GOTO DONE
                END
            END

			-- Tran publication db
	        IF (@category & 1 = 1)
            BEGIN
                SELECT @proc_name = @publisher_db + '.sys.sp_MSdrop_expired_subscription'
                EXEC @retcode = @proc_name

                IF (@retcode != 0 OR @@ERROR != 0)
                BEGIN
                    goto DONE
                END
            END

		    FETCH DC INTO @publisher_db, @category
	    END

	    
	    -- sp_MScleanup_agent_entry in distribution database is called to periodically remove obselete
	    -- entries in MSmerge_agents, which may be caused by the following reasons:
	    --  1. publishing database is externally removed; therefore cleanup agent can not do its job
	    --  2. Entries for anonymous merge subscriptions
	    
	    SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '..sp_MScleanup_agent_entry'
	    EXEC @retcode = @distproc
	
	    IF (@@ERROR != 0 OR @retcode != 0)
	    BEGIN
	    	goto DONE
	    END

DONE:
		-- Cursor cleanup
	    CLOSE DC
	    DEALLOCATE DC
	END

	RETURN @retcode
END

 
Last revision 2008RTM
See also

  sp_MSrepl_add_expired_sub_cleanup_job (Procedure)
sp_vupgrade_publisher (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