Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_dropmergepullsubscription

  No additional text.


Syntax
create procedure sys.sp_dropmergepullsubscription(
    @publication        sysname = NULL,             /* Publication name */
    @publisher          sysname = NULL,             /* Publisher server */
    @publisher_db       sysname = NULL,             /* Publication database */
    @reserved           bit = 0
    )AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    declare @retcode                int
    declare @pubid                  uniqueidentifier
    declare @subid                  uniqueidentifier
    declare @local_db               sysname
    declare @merge_jobid            binary(16)
    declare @job_step_uid 			uniqueidentifier
    declare @cmd                    nvarchar(255)
    declare @pubidstr               nvarchar(38)
    declare @subscriber             sysname
    declare @subscriber_db          sysname
    declare @subscriber_type        int
    declare @local_job              bit
    declare @implicit_transaction   int
    declare @close_cursor_at_commit int
    declare @owner_sid              varbinary(85)
    declare @owner_name             sysname
    declare @qualified_publication_name nvarchar(512)
    declare @subscriber_type_anon   tinyint
    declare @REPLICA_STATUS_BeforeRestore tinyint

    set @subscriber_type_anon= 3
    set @REPLICA_STATUS_BeforeRestore= 7

    select @close_cursor_at_commit = 0
    select @implicit_transaction = 0

    /*
    ** Get original setting values before setting them to false for recursive calling
    */
    IF (@reserved = 0)
    BEGIN
        SELECT @implicit_transaction = @@options & 2
        SELECT @close_cursor_at_commit = @@options & 4
        SET IMPLICIT_TRANSACTIONS OFF
        SET CURSOR_CLOSE_ON_COMMIT OFF
    END

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

     /*
    ** Initializations.
    */
    set @local_db = DB_NAME()
    set @subscriber = @@SERVERNAME
    set @subscriber_db = DB_NAME()

   /*
    ** Assign parameter values appropriately
    */
    IF object_id('dbo.sysmergesubscriptions', 'U') is null
    BEGIN
        RAISERROR (14055, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check: @publisher
    ** Check to make sure that the publisher is defined
    */
    IF @publisher IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publisher', 'sp_dropmergepullsubscription')
        RETURN (1)
    END

    EXECUTE @retcode = sys.sp_validname @publisher
    IF @@ERROR <> 0 OR @retcode <> 0
       RETURN (1)

    /*
    ** Parameter Check: @publisher_db
    */
    IF @publisher_db IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publisher_db', 'sp_dropmergepullsubscription')
        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_dropmergepullsubscription')
        RETURN (1)
    END

    IF LOWER(@publication) = 'all'
    BEGIN
            declare hC1 CURSOR LOCAL FAST_FORWARD FOR
                select DISTINCT name FROM dbo.sysmergepublications
                FOR READ ONLY
        OPEN hC1
        FETCH hC1 INTO @publication
        if @@fetch_status = -1
        begin
            CLOSE hC1
            DEALLOCATE hC1
            RETURN (0) --- It's OK to have no publication when 'ALL'
        end
        WHILE (@@fetch_status <> -1)
        BEGIN
            EXECUTE sys.sp_dropmergepullsubscription @publication = @publication,
                              @publisher = @publisher,
                              @publisher_db = @publisher_db,
       @reserved = 1
            FETCH hC1 INTO @publication
        END
        CLOSE hC1
        DEALLOCATE hC1
        RETURN (0)
    END

    IF LOWER(@publisher) = 'all'
    BEGIN
        declare hC4 CURSOR LOCAL FAST_FORWARD FOR
            select DISTINCT publisher FROM dbo.sysmergepublications
            FOR READ ONLY
        OPEN hC4
        FETCH hC4 INTO @publisher
        WHILE (@@fetch_status <> -1)
        BEGIN
            EXECUTE sys.sp_dropmergepullsubscription @publication = @publication,
                                        @publisher = @publisher,
                                        @publisher_db = @publisher_db,
                                        @reserved = 1
            FETCH hC4 INTO @publisher
        END
        CLOSE hC4
        DEALLOCATE hC4
        RETURN (0)
    END

    /*
    ** Validate that the publisher is a valid server
    */
    /*
    select @publisher_srvid = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@publisher) collate database_default
    IF @publisher_srvid IS NULL
    BEGIN
        RAISERROR (14080, 16, -1, @publisher)
        RETURN (1)
    END
    */

    /* Previously the condition is set as 'AND subid<>pubid' which is fatally errorous */
    IF LOWER(@publisher_db) = 'all'
    BEGIN
        declare hC5 CURSOR LOCAL FAST_FORWARD FOR
            select DISTINCT publisher_db
            FROM dbo.sysmergepublications
            WHERE upper(publisher collate SQL_Latin1_General_CP1_CS_AS)=upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) and
                  name=@publication
            FOR READ ONLY

        OPEN hC5
        FETCH hC5 INTO @publisher_db
        WHILE (@@fetch_status <> -1)
        BEGIN
            EXECUTE sys.sp_dropmergepullsubscription @publication = @publication,
                              @publisher = @publisher,
                              @publisher_db = @publisher_db,
                              @reserved = 1
            FETCH hC5 INTO @publisher_db
        END
        CLOSE hC5
        DEALLOCATE hC5
        RETURN (0)
    END

    /*
    ** return error if only there is no 'ALL'. Same is true for the rest of error handling.
    */

    select @pubid= pubid from dbo.sysmergepublications
        where name = @publication and
              upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) and
              publisher_db = @publisher_db
    if @pubid is null
    BEGIN
        if @reserved = 0
            RAISERROR (20026, 16, -1, @publication)
        RETURN (1)
    END

    set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''
    /*
    ** Only members of the sysadmin group and the creator of the distribution
    ** agent can drop a pull subscription successfully. This behavior matches
    ** the behavior of the sysjobs_view. DBO of the subscriber database,
    ** sysadmins (owner is undefined) can drop a subscription if the owner_sid
    ** is null.
    */

    EXEC sys.sp_MSget_mergepullsubsagent_owner @publisher = @publisher,
                                               @publisher_db = @publisher_db,
                                               @publication = @publication,
                                               @owner_sid = @owner_sid OUTPUT

    IF (@owner_sid is not null AND
        (SUSER_SID() <> @owner_sid) AND
        (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0))
    BEGIN
        SELECT @owner_name = SUSER_SNAME(@owner_sid)
        SELECT @qualified_publication_name = @publisher + N':' +
                                             @publisher_db + N':' +
                                             @publication
        RAISERROR(21121,16,-1,@owner_name, @qualified_publication_name)
        RETURN (1)
    END

    /*
    ** Get subscriptions from either local replicas or global replicas
*/
    /*
    select @subid = subs1.subid, @subscriber_type = subs1.subscriber_type from
        dbo.sysmergesubscriptions   subs1,
        dbo.sysmergesubscriptions   subs2,
        dbo.sysmergepublications    pubs
        where subs1.srvid = @subscriber_srvid
            and subs1.db_name = @subscriber_db
            and subs2.srvid = @publisher_srvid
            and subs2.db_name = @publisher_db
            and subs1.pubid = subs2.subid
            and subs2.pubid = pubs.pubid
            and pubs.name = @publication
            and upper(pubs.publisher collate SQL_Latin1_General_CP1_CS_AS)=upper(@publisher collate SQL_Latin1_General_CP1_CS_AS)
            and pubs.publisher_db=@publisher_db
   */
    select @subid = subid from dbo.sysmergesubscriptions
        where pubid = @pubid and pubid <> subid and db_name = @subscriber_db and UPPER(subscriber_server) = UPPER(@subscriber)
    if @subid IS NULL
    begin
        if @reserved = 0
            raiserror (14050, 16, -1)
        RETURN (0)
    end

    exec @retcode = sys.sp_resetsnapshotdeliveryprogress @drop_table = N'true'
    if @@error <> 0 or @retcode <> 0
    begin
        return (1)
    end

    begin tran
    save TRAN dropmergepullsubscription

    /*
    ** Drop the local merge task
    */
    select @merge_jobid = merge_jobid from dbo.MSmerge_replinfo WHERE repid = @subid

    if (@merge_jobid IS NOT NULL)
    BEGIN
        IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE job_id = @merge_jobid)
        BEGIN
        	SELECT @job_step_uid = job_step_uid
		    FROM MSsubscription_properties
		    WHERE UPPER(publisher) = UPPER(@publisher)
		       AND publisher_db = @publisher_db
		       AND publication = @publication
		       AND publication_type = 2
		
            -- Checks if the job name matches one that is generated
            -- by replication
            EXEC @retcode = sys.sp_MSispullmergejobnamegenerated
                                @publisher = @publisher,
                                @publisher_db = @publisher_db,
                                @publication = @publication,
                                @job_id = @merge_jobid
            IF @@ERROR <> 0
                GOTO FAILURE

            -- Only drop the job if the name was generated
            IF @retcode = 0
            BEGIN
                EXEC @retcode = sys.sp_MSdrop_repl_job @job_id = @merge_jobid,
                										@job_step_uid = @job_step_uid
                IF @@ERROR <> 0 or @retcode <> 0
                    GOTO FAILURE
            END
        END
    END

    if @subid <> @pubid
    BEGIN
        DELETE dbo.MSmerge_replinfo WHERE repid = @subid
        IF @@ERROR <> 0
            GOTO FAILURE

		-- delete supportability settings for the subscriptions that we are about to delete.
        delete from dbo.MSmerge_supportability_settings where subid = @subid        	
        delete from dbo.MSmerge_log_files where subid = @subid

        delete from dbo.sysmergesubscriptions where subid = @subid
        if @@ERROR <> 0
            GOTO FAILURE

        exec sys.sp_MScleanup_subscriber_history @subid=@subid
		if @@ERROR <> 0
            GOTO FAILURE

        /* Call sp_MSunregistersubscription so that the reg entries get deleted */
        exec @retcode = sys.sp_MSunregistersubscription @publisher = @publisher,
                                    @publisher_db = @publisher_db,
                                    @publication = @publication,
                                    @subscriber = @@SERVERNAME,
                                    @subscriber_db = @subscriber_db
        IF @retcode<>0 or @@ERROR<>0
            GOTO FAILURE

        exec @retcode= sys.sp_MSpublicationcleanup @publisher=@publisher,
                                        @publisher_db = @publisher_db,
                                       @publication = @publication,
                                        @force_preserve_rowguidcol = 0
        IF @retcode<>0 or @@ERROR <> 0
        BEGIN
            RAISERROR (20025, 16, -1, @publication)
            GOTO FAILURE
        END

        -- If the only remaining subscriptions are old entries (before restore),
        -- we remove them now.
        if not exists (select * from dbo.sysmergesubscriptions
                            where status <> @REPLICA_STATUS_BeforeRestore)
        begin
            delete from dbo.sysmergesubscriptions
            truncate table dbo.MSmerge_supportability_settings
            truncate table dbo.MSmerge_log_files
            truncate table dbo.MSrepl_errors
            truncate table dbo.MSmerge_history
            truncate table dbo.MSmerge_articlehistory
            truncate table dbo.MSmerge_sessions
            delete from dbo.MSmerge_replinfo
        end
    END

    IF object_id('MSsubscription_properties', 'U') is not NULL
    BEGIN
        DELETE FROM MSsubscription_properties
        WHERE upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(@publisher collate SQL_Latin1_General_CP1_CS_AS)    AND
        publisher_db  = @publisher_db AND
        publication = @publication

        IF @@ERROR <> 0
            GOTO FAILURE

        IF NOT EXISTS (SELECT * FROM MSsubscription_properties)
        BEGIN
            exec @retcode = sys.sp_MSsub_cleanup_prop_table
            IF @@ERROR <> 0 or @retcode <> 0
                GOTO FAILURE
        END
    END

    COMMIT TRAN
    /*
    ** Set back original settings
    */
    IF @reserved = 0
    BEGIN
        declare @whattodrop int    -- 1=hws, 2=lws, 3=both
        set @whattodrop= 0

        if not exists (select * from dbo.sysmergesubscriptions
                            where subscription_type <> 3 and
                                  status <> @REPLICA_STATUS_BeforeRestore)
        begin
            set @whattodrop= 1
        end

        if not exists (select * from dbo.sysmergesubscriptions
                            where subscription_type = 3 and
                                  status <> @REPLICA_STATUS_BeforeRestore)
        begin
            set @whattodrop= @whattodrop + 2
        end

        /*
        ** If last subscription is dropped and the DB is not enabled for publishing,
        ** then remove the merge system tables
        */
        if 0 <> @whattodrop and
           0 = (select category & 4 FROM master.dbo.sysdatabases WHERE name = DB_NAME())
        begin
            execute @retcode= sys.sp_MSdrop_mergesystables @whattodrop=@whattodrop
            if @@ERROR <> 0 or @retcode <> 0 return (1)

            execute @retcode=sys.sp_MSrepl_ddl_triggers @type='merge', @mode='drop'
            if @@ERROR <> 0 or @retcode <> 0 goto FAILURE
        end

        IF @implicit_transaction <>0
            SET IMPLICIT_TRANSACTIONS ON
        IF @close_cursor_at_commit <>0
            SET CURSOR_CLOSE_ON_COMMIT ON
    END

    -- note that here we will ignore any errors.
    EXEC sys.sp_dropreplsymmetrickey @check_replication = 1, @throw_error = 0

    RETURN(0)

FAILURE:
    RAISERROR (14056, 16, -1)
    if @@trancount > 0
    begin
        ROLLBACK TRANSACTION dropmergepullsubscription
        COMMIT TRANSACTION
    end
    /*
    ** Set back original settings
    */
    IF @reserved = 0
    BEGIN
        IF @implicit_transaction <>0
            SET IMPLICIT_TRANSACTIONS ON
        IF @close_cursor_at_commit <>0
            SET CURSOR_CLOSE_ON_COMMIT ON
    END
    return 1

 
Last revision 2008RTM
See also

  sp_MSpublicationcleanup (Procedure)
sp_MSremovedbreplication (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