Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSpublishdb

  No additional text.


Syntax
create  procedure sys.sp_MSpublishdb(
      @value     sysname,
      @ignore_distributor bit = 0,
      @from_backup        bit = 0
    ) AS

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    declare @quoted_db      sysname
    declare @command        nvarchar(255)
    declare @description    nvarchar(500)
    declare @category_name  nvarchar(100)
    DECLARE @agentname      nvarchar(300)
    DECLARE @dbname         sysname
    DECLARE @db_mergebit    int
    DECLARE @retcode        int
    DECLARE @distributor    sysname
    DECLARE @distribdb      sysname
    DECLARE @distproc       nvarchar (255)
    				,@publisher sysname
    /*
    ** Initialization
    */

    SELECT @dbname = DB_NAME()
				,@db_mergebit = 4
				,@publisher = publishingservername()

    /*
    ** Parameter check
    ** @value
    */
    IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true','false')
    BEGIN
      RAISERROR(14137,16,-1)
      RETURN(1)
    END

    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
    begin
        /*
        ** Test to see if the distributor is installed and online.
        */
        EXECUTE @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,
           @distribdb   = @distribdb OUTPUT

        IF @@ERROR <> 0 or @retcode <> 0 or @distributor IS NULL or @distribdb IS NULL
        BEGIN
            IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
                RAISERROR (20028, 16, -1)
            ELSE
                RAISERROR (20029, 16, -1)
            RETURN (1)
        END
    end

    /*
    ** Enable the database for publishing.
    */
    IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN

        /*
        ** Drop and then create central publish tables
        */

        /*
        ** Drop first if exists
        */

        EXEC @retcode = sys.sp_MSdrop_pub_tables
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)
        END

        /*
        ** Create central publish tables
        */

        EXEC @retcode = sys.sp_MScreate_pub_tables
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)
        END
	
       /*
	    ** Create central publish tables only if cmplevel IS_MEMBER over 80
    	*/
	    if not exists (select * from master.dbo.sysdatabases
							where dbid = db_id() and cmptlevel >= 80)
		begin
			raiserror(21809, 10, 1)
		end	
    END

    ELSE    /* Disable the database for publishing. */
    BEGIN
        /*
	    ** Drop first if exists, to be consistant with pub tables
    	*/
        /*
        ** Remove all subscriptions in the database.
        ** WARNING : must owner qualify proc calls for these to run inside server on restore/attach
        */
        EXEC @retcode = sys.sp_dropsubscription @publication = 'all',
            @article = 'all', @subscriber = 'all',
            @ignore_distributor = @ignore_distributor
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)
        END

        -- Used for attach and restored db.
        -- sysservers table in master db might be changed so that
        -- sp_dropsubscription won't work. Delete the table directly.

        delete syssubscriptions where srvid >= 0
        IF @@ERROR <> 0
        BEGIN
            return (1)
        END

        /*
        ** Remove all publications and articles in the database.
        ** sp_droppublication will also forcefully unmark repl bits in sys.objects
        ** and call sp_repldone when dropping the last
        ** publication.
        */
        EXEC @retcode = sys.sp_droppublication @publication = 'all',
            @ignore_distributor = @ignore_distributor,
            @from_backup = @from_backup
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)
        END
        /*
        ** Drop central publish tables
        */
        EXEC @retcode = sys.sp_MSdrop_pub_tables
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
            return (1)
        END

	

        
        -- drop the database wide pal role
        
        if exists (select * from sys.database_principals where name='MStran_PAL_role' and type = 'R')
        begin
            
            -- drop the members for MStran_PAL_role role.
            
           exec @retcode = sys.sp_MSrepl_drop_all_role_members @rolename = 'MStran_PAL_role'
		   if @@ERROR <> 0 or @retcode <> 0
                    return (1)

            exec @retcode = sys.sp_droprole @rolename = 'MStran_PAL_role'
            if @@ERROR <> 0 or @retcode <> 0
                return (1)
        end
        /*
        ** Drop publisher database level agents in new security mode
        */
		IF @ignore_distributor = 0
		BEGIN
			-- drop logreader agent
			SELECT @distproc = QUOTENAME(@distributor) + '.' + QUOTENAME(@distribdb) + '.dbo.sp_MSdrop_logreader_agent'
			EXECUTE @retcode = @distproc @publisher = @publisher,
								@publisher_db = @dbname,
								@publication = 'ALL'
			IF @@ERROR <> 0 or @retcode <> 0
				RETURN 1
				
			-- drop qreader agent
		END

        -- At this point we should have dropped the logreader agent so we check to see
        --  if CDC is enabled, if so we add the CDC job
        IF [sys].[fn_cdc_is_db_enabled]() = 1
        BEGIN
            exec @retcode = [sys].[sp_cdc_add_job] @job_type = N'capture'
            if @@error <> 0 or @retcode <> 0
                return 1
        END
	END
	
    return (0)

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSremovedbreplication (Procedure)
sp_MSrestoredbreplication (Procedure)
sp_replicationdboption (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