Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_vupgrade_express_edition

  No additional text.


Syntax


-- Name: sp_vupgrade_express_edition

-- Descriptions:
--	For Express Edition, this proc drops all the merge publications

-- Returns: 0 - success
--          1 - Otherwise

-- Security:
-- Requires Certificate signature for catalog access

create procedure sys.sp_vupgrade_express_edition
as
begin
    set nocount on

    declare @quoteddbname nvarchar(270),
            @has_dbaccess bit,
            @proccmd nvarchar(4000),
            @unquoteddbname sysname,
            @category int

    raiserror(20731, 10, -1) --Dropping all merge publications

    -- Drop publication for all databases except system and distribution db's
    DECLARE #current_db CURSOR LOCAL FAST_FORWARD
    FOR
    SELECT  distinct name, N'[' + replace(name, N']', N']]') + N']',
            has_dbaccess(name),category
    FROM    master.dbo.sysdatabases
    WHERE   name COLLATE DATABASE_DEFAULT NOT IN
                (
                    N'master' ,
                    N'tempdb' ,
                    N'msdb'   ,
                    N'model'
                )
            and category & 16 = 0
            and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
    FOR READ ONLY

    
    -- Process each database in the cursor
    
    open #current_db
    fetch #current_db into @unquoteddbname, @quoteddbname, @has_dbaccess,@category
    while ( @@fetch_status <> -1 )
    begin
        if ( @has_dbaccess = 1 )
        begin
            
            -- Database is accessible for cleanup
            
            if (@category & 4 = 4 or @category & 1 = 1)
            begin
                
                -- Database is enabled for replication publishing
                -- Drop all publications and disable the db for publishing.
                -- This is done under exception block.
                
                if object_id(@quoteddbname + N'.dbo.sysmergepublications', 'U') is not null
                begin
                    select @proccmd = @quoteddbname + N'.sys.sp_dropmergepublication'
                    begin try
                        exec @proccmd @publication = N'all', @ignore_distributor = 1
                        exec sys.sp_replicationdboption @dbname = @unquoteddbname, @optname = N'merge publish', @value=N'false', @ignore_distributor=1
                    end try
                    begin catch
                        
                        -- An exception was raised in try block.
                        -- Check if there are aborted transactions
                        -- At this point we should not have any open transactions
                        -- If there any open transactions - roll them back completely
                        
                        if (@@trancount > 0)
                            rollback transaction
                        
                        -- if there subscriptions - the publication will not be dropped
                        -- exception will cleanup publication metadata
                        
                        if object_id(@quoteddbname + N'.dbo.sysmergepublications', 'U') is not null
                        begin
                            select @proccmd = N'delete from ' + @quoteddbname + N'.dbo.sysmergepublications '
                                    + N'where UPPER(publisher)=UPPER(publishingservername())'
                                    + N'and publisher_db = N''' + replace(@unquoteddbname, '''', '''''') + N''' '
                            exec (@proccmd)
                        end
                    end catch
                end -- dropping merge publications
                if object_id(@quoteddbname + N'.dbo.syspublications', 'U') is not null
                begin
                    select @proccmd = @quoteddbname + N'.sys.sp_droppublication'
                    begin try
                        exec @proccmd @publication = N'all', @ignore_distributor = 1
                        exec sys.sp_replicationdboption @dbname = @unquoteddbname, @optname = N'publish', @value=N'false', @ignore_distributor=1
                    end try
                    begin catch
                        
                        -- An exception was raised in try block.
                        -- Check if there are aborted transactions
                        -- At this point we should not have any open transactions
                        -- If there any open transactions - roll them back completely
                        
                        if (@@trancount > 0)
                            rollback transaction
                        
                        -- if there subscriptions - the publication will not be dropped
                        -- exception will cleanup publication metadata
                        
                        if object_id(@quoteddbname + N'.dbo.syspublications', 'U') is not null
                        begin
                            select @proccmd = N'delete from ' + @quoteddbname + N'.dbo.syspublications '
                            exec (@proccmd)
                        end
                    end catch
                end -- dropping transactional publications
            end -- if (@category & 4 = 4 or @category & 1 = 1)
            else
            begin
                -- Database is not enabled for replication publishing
                -- Register all pull subscription in the sync manager sync all jobs are gone in msde->express upgrade
                -- I don't see much point testing for error after each step(we would like to continue any way).
                select @proccmd = @quoteddbname + N'.sys.sp_MSregister_all_trans_pull_subscriptions'
                exec @proccmd

                select @proccmd = @quoteddbname + N'.sys.sp_MSregister_all_merge_pull_subscriptions'
                exec @proccmd
            end
        end -- if ( @has_dbaccess = 1 )
        else
        begin
            -- The database is not inaccessible
            raiserror( 21376, 10, 1, @quoteddbname)
        end

        fetch next from #current_db into @unquoteddbname, @quoteddbname, @has_dbaccess,@category
    end
    close #current_db
    deallocate #current_db

    return 0
end

 
Last revision 2008RTM
See also

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