Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_vupgrade_heterogeneous_publishers

  No additional text.


Syntax

create procedure sys.sp_vupgrade_heterogeneous_publishers
as
begin

    set nocount on

    -- Upgrade all defined Oracle publishers
    declare @publisher sysname
    declare @login sysname
    declare @password nvarchar(524)
    declare @security_mode int
    declare @retcode int
    declare @versionsmatch int
    declare @packageversion nvarchar(256)
    declare @sa_login sysname
    declare @state int
    declare @msg nvarchar(max)

    select @sa_login = SUSER_SNAME(0x01)

    -- Return success if msdb does not exist
    IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases
        WHERE name = 'msdb' collate database_default)
    BEGIN
        RETURN(0)
    END

    -- Return success if msdb..MSdistpublishers does not exist
    IF NOT EXISTS (SELECT * FROM msdb..sysobjects
	WHERE name = 'MSdistpublishers' collate database_default)
    BEGIN
        RETURN(0)
    END

    declare #hc_publisher CURSOR LOCAL FORWARD_ONLY for

        SELECT name, login, password, security_mode
        FROM msdb..MSdistpublishers
        WHERE publisher_type LIKE 'ORACLE%'

    open #hc_publisher
    fetch #hc_publisher into @publisher, @login, @password, @security_mode
    while (@@fetch_status != -1)
    begin

        select @publisher = upper(@publisher)

        -- The linked server should always exist.
        if not EXISTS
        (
            select *
            from master.dbo.sysservers
            where UPPER(srvname collate database_default) = @publisher
        )
        begin
            goto NEXTPUB
        end

        --Mark system link
        exec @retcode = sys.sp_serveroption @publisher, 'system', 'true'
        if @@error <> 0 OR @retcode <> 0
        begin
            goto NEXTPUB
        end

        -- Drop 'sa' linked servermapping if it exists
        exec  @retcode = sys.sp_droplinkedsrvlogin
            @rmtsrvname = @publisher,
            @locallogin = @sa_login

        -- Drop default linked server mapping if it exists
        exec  @retcode = sys.sp_droplinkedsrvlogin
            @rmtsrvname = @publisher,
            @locallogin = NULL

        -- NOTE:  There is no convenient way to get rid of other explicit mappings
        --        that may exist.  Their presence, however, should not be a problem.
        --        When using the linked server we will always execute as 'sa'
        --        and the linked server can only be accessed through the
        --        replication stored procedures in the resource database.

        exec @retcode = sys.sp_MSrepldecrypt @password output
        if @@error <> 0 OR @retcode <> 0
        begin
            goto NEXTPUB
        end

        -- Map sa to use provided remote login/password
        -- All linked server queries must be done as sa (via execute as owner)
        exec  @retcode = sys.sp_addlinkedsrvlogin
            @rmtsrvname		= @publisher,
            @useself		= N'false',
            @locallogin		= @sa_login,
            @rmtuser		= @login,
            @rmtpassword	= @password
        if @@error <> 0 OR @retcode <> 0
        begin
            goto NEXTPUB
        end

        -- Map all other users to use invalid login/password
        exec @retcode = sp_addlinkedsrvlogin
            @rmtsrvname		= @publisher,
            @useself		= 'false',
            @locallogin		= null,
            @rmtuser		= null,
            @rmtpassword	= null
        if @@error <> 0 OR @retcode <> 0
        begin
            goto NEXTPUB
        end

        -- Determine whether the Oracle publisher requires refreshing
        begin try
            exec @retcode = sys.sp_checkOraclepackageversion @publisher = @publisher
				, @versionsmatch = @versionsmatch OUTPUT
				, @packageversion = @packageversion OUTPUT
        end try
        begin catch
            select @retcode = 1
        end catch

        if @retcode <> 1
        begin
			-- If @versionsmatch = 2 the package code should be refreshed, but meta data
			-- tables should only be altered as necessary, preserving existing table entries.
			
			-- NOTE:  The variable @packageversion contains the value of @@version
			--        for SQL Server 2005 installs and SQL Server 2008 CTP versions.
			--        For SQL Server 2008 RTM and greater, @packageversion is constructed
			--        as follows:
			
			--		     SET @packageversion = N'Microsoft SQL Server ' +
			--               convert(nvarchar(100),SERVERPROPERTY('ProductLevel')) +
			--		         + N' Version:' + convert(nvarchar(100),SERVERPROPERTY('ProductVersion'))
			
			--        Extract information from @packageversion to determine specific
			--        information about the Oracle install, if this is needed to apply
			--        DDL changes to meta data tables, or if Oracle triggers need to be
			--        modified. It is important to insure, to the extent possible, that
			--        entries in the meta data tables are preserved during upgrade.
			
			if @versionsmatch = 2
			begin
				-- Refresh the Oracle package code only, leaving meta data tables
				-- and triggers intact. No DDL changes are currently required
				-- to upgrade an Oracle install.
				begin try
					EXEC @retcode = sys.sp_ORAloadscript	@publisher		= @publisher,
															@script			= N'instorcl',
															@security_mode	= @security_mode,
															@login			= @login,
															@password		= @password
		
					-- Force a recompile of the defined triggers
					EXEC @retcode = sys.sp_ORAcompiletriggers	@publisher		= @publisher

					-- Set Oracle package version to current SQL Server version
					exec @retcode = sys.sp_setOraclepackageversion @publisher = @publisher

				end try

				begin catch
					select @state = ERROR_STATE(), @msg = ERROR_MESSAGE()
					raiserror(@msg, 10, @state)

					select @retcode = 0
				end catch

			end
			else if @versionsmatch = 0
			begin
				-- Refresh all of the Oracle side meta data, ignoring errors.
				-- Failures during refresh will be logged and the refresh for specific
				-- publishers can be repeated explicitly after the upgrade process.
				-- NOTE:  A failure to complete the refresh of the Oracle package code and publication
				--        meta data will leave the Oracle package without a valid version stamp.
				begin try
					exec @retcode = sys.sp_refresh_heterogeneous_publisher @publisher = @publisher
			
					-- Force a recompile of the defined triggers
					EXEC @retcode = sys.sp_ORAcompiletriggers	@publisher		= @publisher
				
					-- Set Oracle package version to current SQL Server version
					exec @retcode = sys.sp_setOraclepackageversion @publisher = @publisher

				end try

				begin catch
					select @state = ERROR_STATE(), @msg = ERROR_MESSAGE()
					raiserror(@msg, 10, @state)

					select @retcode = 0
				end catch
			end

		end	

NEXTPUB:

        -- fetch next publisher
        fetch #hc_publisher into @publisher, @login, @password, @security_mode

    end -- while cursor

    close #hc_publisher
    deallocate #hc_publisher

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