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
