Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_unlink_publication_internal

  No additional text.


Syntax
create procedure sys.sp_unlink_publication_internal
(
    @publisher sysname         -- publishing server name
    ,@publisher_db sysname    -- publishing database name
    ,@publication sysname      -- publication name
    ,@reserved nvarchar(10)=null -- 'drop all for all subscriptions'
    ,@publisherlinkusertodrop sysname output -- name of the publisher link user to drop after this procedure completes (null if there is no need to drop any publisher link user)
)
as
begin
    set nocount on
    declare @retcode int
            ,@publisherlink sysname
            ,@publisherlinkuser sysname
            ,@rpcproc nvarchar(4000)
            ,@security_mode int
            ,@islocalpub bit
            ,@modedropall nvarchar(10)
            ,@certname sysname

    
    -- initialize
    
    select @publisher = upper(@publisher)
        ,@reserved = lower(@reserved)
        ,@islocalpub = case when (upper(@publisher) = upper(publishingservername())) then 1 else 0 end
        ,@modedropall = N'drop_all'
    
    -- check if we need to unlink
    
    if object_id('MSsubscription_properties') is not null
    begin
        if (@reserved = @modedropall)
        begin
            
            -- all subscriptions
            -- unlink each subscription recursively
            
            declare #hcsubprop cursor local fast_forward for
                select distinct publisher, publisher_db, publication
                from dbo.MSsubscription_properties
            open #hcsubprop
            fetch #hcsubprop into @publisher, @publisher_db, @publication
            while (@@fetch_status != -1)
            begin
                exec @retcode = sys.sp_unlink_publication @publisher, @publisher_db, @publication
                if (@retcode != 0 or @@error != 0)
                    return (1)
                fetch #hcsubprop into @publisher, @publisher_db, @publication
            end
            close #hcsubprop
            deallocate #hcsubprop
        end -- @reserved = @modedropall
        else
        begin -- @reserved != @modedropall
            
            -- specific subscription
            -- Check if we need to do any processing
            
            if object_id('MSsubscription_properties') is null
                return 0
            if not exists (select * from sys.columns where
                                object_id = object_id('MSsubscription_properties')
                                and name in ('publisherlink', 'publisherlinkuser'))
                return 0
            
            -- check for publisher link related information
            
            select @publisherlink = case when (publisher_security_mode in (0,1))
                            then sys.fn_MSrepllinkname(N'REPLLINK', upper(@publisher), @publisher_db, @publication, db_name())
                            else publisherlink end
                ,@publisherlinkuser = case when (publisher_security_mode in (0,1) and publisherlinkuser = N'xxxx')
                            then N'repllinkproxy' else publisherlinkuser end
                ,@security_mode = publisher_security_mode
            from dbo.MSsubscription_properties
            where upper(publisher) = @publisher
                and    publisher_db = @publisher_db
                and    publication = @publication

            if (@publisherlink is not null)
            begin
                
                -- skip this processing for cases
                -- where MSreplication_objects does not exist
                -- or we have an older version of MSreplication_objects
                
                if object_id('MSreplication_objects') is null
                    return 0
                if not exists (select * from sys.columns where
                                    object_id = object_id('MSreplication_objects')
                                    and name = 'publication')
                    return 0
                
                -- we have link information
                -- check if we need to remove certificates
                
                select distinct @certname = c.name
                from sys.crypt_properties cp join sys.certificates c
                    on cp.thumbprint = c.thumbprint
                where object_name(cp.major_id) in
                    (select object_name
                        from dbo.MSreplication_objects
                        where upper(publisher) = @publisher
                            and publisher_db = @publisher_db
                            and publication = @publication
                            and object_type = 'T')
                if (@certname is not null)
                begin
                    
                    -- remove cert grant from target db
                    
                    if (@islocalpub = 0)
                    begin
                        select @rpcproc = N'master.sys.sp_MSrepltrigcertgrant'
                        exec @rpcproc @mode = 2
                                            ,@islocalpub = @islocalpub
                                            ,@certname = @certname
                                            ,@targetdb = N'master'
                    end
                    else
                    begin
                        select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSrepltrigcertgrant'
                        exec @rpcproc @mode = 2
                                            ,@islocalpub = @islocalpub
                                            ,@certname = @certname
                                            ,@targetdb = @publisher_db
                    end
                    
                    -- remove cert from subscriber db
                    
                    exec sys.sp_MSrepltrigpreparecert @mode = 2
                                                ,@certname = @certname
                                                ,@publisher = @publisher
                                                ,@publisher_db = @publisher_db
                                                ,@publication = @publication
                end

                if (@islocalpub = 0 and @security_mode in (0,1))
                begin
                    
                    -- remote publisher - drop unique link
                    
                    exec @retcode = sys.sp_MSprocesspublisherlink @mode = 2
                        ,@islocalpub = @islocalpub, @securitymode = @security_mode
                        ,@linkname = @publisherlink, @remserver = @publisher
                    if @@error <> 0 or @retcode <> 0
                        return (1)
                end
                if (@security_mode = 0 and @publisherlinkuser is not null )
                begin
                    
                    -- remove context user if no other subscriptions are using it
                    
                    if not exists (select *
                        from dbo.MSsubscription_properties
                        where publisherlinkuser = case when (@publisherlinkuser = N'repllinkproxy') then N'xxxx' else @publisherlinkuser end
                            and (upper(publisher) != @publisher
                                or publisher_db != @publisher_db
                                or publication != @publication))
                    begin
                        if exists (select * from sys.database_principals where name = @publisherlinkuser)
                        begin
                            set @publisherlinkusertodrop = @publisherlinkuser
                        end
                    end
                end
                
                -- update this entry in MSsubscription_properties
                
                update MSsubscription_properties
      set publisherlink = null
                    ,publisherlinkuser = null
                where upper(publisher) = @publisher
                    and publisher_db =  @publisher_db
                    and publication = @publication
                if (@@error <> 0)
                    return (1)
            end -- if (@publisherlink is not null)
        end -- @reserved != @modedropall
    end -- if object_id('MSsubscription_properties') is not null
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_droppullsubscription (Procedure)
sp_link_publication (Procedure)
sp_subscription_cleanup (Procedure)
sp_unlink_publication (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