Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdrop_expired_subscription

  No additional text.


Syntax

CREATE PROCEDURE sys.sp_MSdrop_expired_subscription
(
    @publisher sysname = NULL
)
AS
BEGIN
    set nocount on
	DECLARE	@independent_agent  bit,
			@article            sysname,
			@publication        sysname,
			@pubid              int,
			@artid              int,
			@loc_publisher      sysname,
			@subscriber         sysname,
			@subscriber_id      smallint,
			@subscriber_db      sysname,
			@publisher_db       sysname,
			@out_of_date        int,
			@distributor        sysname,
			@distribdb          sysname,
			@retention          int,			-- in days
			@retcode            smallint,
			@distproc           nvarchar(255),
			@localproc          nvarchar(255),
			@msg                nvarchar(255),
			@open_cursor        nvarchar(400),
			@cutoff_date		datetime,
			@publisher_type		sysname

	-- TEMP TABLE for publication id's
	CREATE TABLE #publication_ids (pubid int)
	
	
    -- Security Check.
	
	EXEC @retcode = sys.sp_MSreplcheck_publish
    IF (@@ERROR != 0 OR @retcode != 0)
    BEGIN
        RETURN (1)
    END

    
    -- 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
        RAISERROR (20036, 16, -1)
        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(N'%', @publisher, @publisher_type)

	
	IF (@publisher_type = N'MSSQLSERVER')
	BEGIN
		SELECT	@distproc      = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + N'.',
				@loc_publisher = publishingservername(),
				@publisher_db  = db_name()
	END
	ELSE
	BEGIN
		SELECT	@distproc      = N'',
				@loc_publisher = @publisher,
				@publisher_db  = @distribdb
	END

	SELECT @distproc =	@distproc + N'sys.sp_MShelp_subscription_status '

	DECLARE PC CURSOR LOCAL FAST_FORWARD FOR
	SELECT DISTINCT name, pubid, independent_agent, retention
	FROM   syspublications p
	WHERE  p.pubid IN (SELECT pids.pubid FROM #publication_ids pids)
	
    OPEN PC
    FETCH PC INTO @publication, @pubid, @independent_agent, @retention

    WHILE (@@fetch_status != -1)
    BEGIN
        -- Don't do anything if the retention is zero, this means
        -- subscriptions to the publication will never expire
        IF (@retention != 0)
        BEGIN
	        DECLARE SC CURSOR LOCAL FAST_FORWARD FOR
	        SELECT	s.srvid,
	        		s.dest_db,
	        		a.name
	        		,s.srvname
	        FROM	syssubscriptions s,
	        		sysextendedarticlesview a
            WHERE	a.pubid  = @pubid
              AND	s.artid  = a.artid
              AND	s.srvid != -1
            FOR READ ONLY

	        OPEN SC
	        FETCH SC into @subscriber_id, @subscriber_db, @article, @subscriber
	
	        WHILE (@@fetch_status <> -1)
            BEGIN

                EXEC @retcode = @distproc	@publisher         = @loc_publisher,
											@publisher_db      = @publisher_db,
											@publication       = @publication,
											@subscriber        = @subscriber,
											@subscriber_db     = @subscriber_db,
											@retention         = @retention,
											@out_of_date       = @out_of_date OUTPUT,
											@independent_agent = @independent_agent
											
                IF (@retcode != 0 OR @@ERROR != 0)
                BEGIN
                    CLOSE SC
                    DEALLOCATE SC
                    CLOSE PC
                    DEALLOCATE PC
                    RETURN (1)
                END

                IF (@out_of_date = 1)
                begin
	                -- publisher_db.dbo.sp_dropsubscription
                    EXEC @retcode =	sys.sp_dropsubscription	@publication    = @publication,
															@article        = @article,
															@subscriber     = @subscriber,
															@destination_db = @subscriber_db,
															@reserved       = 'internal',
															@publisher		= @publisher
	                IF (@retcode != 0 OR @@ERROR != 0)
	                BEGIN
	                    CLOSE SC
	                    DEALLOCATE SC
	                    CLOSE PC
	                    DEALLOCATE PC
	                    RETURN (1)
	                END

                    RAISERROR(14157, 10, -1, @subscriber, @publication)
                END

                FETCH SC INTO @subscriber_id, @subscriber_db, @article, @subscriber
            END

	        CLOSE SC
	        DEALLOCATE SC

			IF (@publisher_type = N'MSSQLSERVER')
			BEGIN
				-- cleanup the P2P request response history meta-data based on publication retention
				SELECT @cutoff_date = DATEADD(hour, -@retention, GETDATE())
				
				EXEC @retcode = sys.sp_deletepeerrequesthistory @publication = @publication,
																@cutoff_date = @cutoff_date
	            IF (@retcode != 0 OR @@ERROR != 0)
				BEGIN
					CLOSE PC
					DEALLOCATE PC
					RETURN (1)
				END
			END
		END

        FETCH PC INTO @publication, @pubid, @independent_agent, @retention
    END

    CLOSE PC
    DEALLOCATE PC
    
    -- cleanup expired queued conflict table entries
    
	IF (@publisher_type = N'MSSQLSERVER')
	BEGIN
	    exec @retcode = sys.sp_MScleanup_conflict_table
	    if @@ERROR<>0 or @retcode<>0
	        return (1)
    END
    
    -- all done
    
    RETURN 0
END

 
Last revision 2008RTM
See also

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