Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAaddpublisher

  No additional text.


Syntax


-- Name:
--		sp_ORAaddpublisher

-- Description:
--		Adds Oracle publisher info on Oracle instance

-- Inputs:
--		@publisher			== publisher name (TNS name)
--		@dist_db			== distribution database
--		@remotelogin		== Oracle login to server as schema owner
--		@remotepassword		== Oracle password
--		@distributortimestamp		== publisher timestamp
--		@publisher_guid			== publisher guid

-- Returns:
--		Result set
--		Return code (0 for success, 1 for failure)

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

-- Notes:
--		This stored procedure checks first to see if the Oracle instance is already publishing to
--		SQL Server.  If not, it (re)creates the tables HREPL_Distributor and HREPL_Publisher, and
--		the public synonym MSSQLServerDistributor, and populates the table with the name of the
--		SQL Server instance that will serve as the Distributor for the Oracle publisher, the name
--		that the publisher is known as, at the Distributor, and the name of the distribution
--		database that will serve as the store and forward database for the publisher.


CREATE PROCEDURE sys.sp_ORAaddpublisher
(
	@publisher			sysname,
	@dist_db			sysname,
	@security_mode		int = 1,
	@remotelogin		sysname,
	@remotepassword		sysname,
	@connect_timeout	int = 60,
	@distributortimestamp	datetime,
	@publisher_guid		uniqueidentifier
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE	@ChkTblCmd		nvarchar(4000)
	DECLARE @TblChk			int
	DECLARE @publisherGUID	varchar(255)
	DECLARE @distributor	sysname
	DECLARE @srvid			smallint
	DECLARE @retcode		int
	DECLARE @SynonymOwner	sysname
	DECLARE @QuotedLogin	sysname

	SET @QuotedLogin = @remotelogin
	IF NOT ((SUBSTRING(@remotelogin, 1, 1) = N'"') AND (SUBSTRING(@remotelogin, LEN(@remotelogin), 1) = N'"'))
	BEGIN
		SET @QuotedLogin = QUOTENAME(@QuotedLogin, '"')
	END

	-- Define remote query support table
	create table #hquery
	(
		seq	int identity(2,1),
		cmd	nvarchar(4000)
	)

	-- Define temp table for getting synonymn owner
	CREATE TABLE #synonymOwner (owner varchar(255))
	
	-- If the public synonym exists, error indicating that this Oracle database is
	-- already a SQL publisher
	INSERT INTO #hquery(cmd) VALUES('SELECT TABLE_OWNER ')
	INSERT INTO #hquery(cmd) VALUES('FROM SYS.ALL_SYNONYMS ')
	INSERT INTO #hquery(cmd) VALUES('WHERE OWNER=''PUBLIC'' AND SYNONYM_NAME=''MSSQLSERVERDISTRIBUTOR''')
	
	EXEC @retcode = sys.sp_ORAremotequery	@Server				= @publisher,
											@SelectColumnList	= N'TABLE_OWNER',
											@InsTable			= N'#synonymOwner',
											@InsColumnList		= N'owner'

	IF (@retcode != 0) OR (@@ERROR != 0)
	BEGIN
		RETURN (1)
	END

	-- Get owner name
	SELECT	@SynonymOwner = owner
	FROM	#synonymOwner

	-- Verify publish if synonym exists
	IF (@SynonymOwner IS NOT NULL)
	BEGIN
		-- Oracle server already published.  Get info on who is publishing it
		DECLARE @info_dist_publisher	sysname
		DECLARE @info_dist_distributor	sysname
		DECLARE @info_dist_db			sysname

		CREATE TABLE #DistInfo
		(
			dist_publisher		nvarchar(128),
			dist_distributor	nvarchar(128),
			dist_db				nvarchar(128)
		)

		-- Define object check table
		create table #objectCheck (obj sysname)

		-- Verify metadata exists
		TRUNCATE TABLE #hquery
		INSERT INTO #hquery(cmd) VALUES ('SELECT TABLE_NAME FROM ALL_TABLES ')
		INSERT INTO #hquery(cmd) VALUES ('WHERE OWNER = ' + QUOTENAME(@SynonymOwner, '''') + ' ')
		INSERT INTO #hquery(cmd) VALUES ('AND TABLE_NAME = ''HREPL_DISTRIBUTOR''')

		EXEC @retcode = sys.sp_ORAremotequery	@Server				= @publisher,
												@SelectColumnList	= N'TABLE_NAME',
												@InsTable			= N'#objectCheck',
												@InsColumnList		= N'obj'

		IF (@retcode != 0) OR (@@ERROR != 0)
		BEGIN
			RETURN (1)
		END

		IF EXISTS (SELECT obj FROM #objectCheck)
		BEGIN
			TRUNCATE TABLE #hquery
			INSERT INTO #hquery(cmd) VALUES ('SELECT DISTRIBUTOR_PUBLISHERNAME, DISTRIBUTOR_DISTRIBUTORNAME, DISTRIBUTOR_DISTRIBUTIONDB ')
			INSERT INTO #hquery(cmd) VALUES ('FROM ' + QUOTENAME(@SynonymOwner, '"') + '.HREPL_DISTRIBUTOR')

			EXEC @retcode = sys.sp_ORAremotequery	@Server				= @publisher,
													@SelectColumnList	= N'DISTRIBUTOR_PUBLISHERNAME,DISTRIBUTOR_DISTRIBUTORNAME,DISTRIBUTOR_DISTRIBUTIONDB',
													@InsTable			= N'#DistInfo',
													@InsColumnList		= N'dist_publisher,dist_distributor,dist_db'

			IF (@retcode != 0) OR (@@ERROR != 0)
			BEGIN
				RETURN (1)
			END
			
			SELECT	@info_dist_publisher	= dist_publisher,
					@info_dist_distributor	= dist_distributor,
					@info_dist_db			= dist_db
			FROM	#DistInfo

			DROP TABLE #DistInfo
		END
		
		-- If this machine is the same as the distributor named and the publisher is the same,
		-- implicitly drop and re-add
		IF (@info_dist_distributor = CONVERT(sysname, ServerProperty('ServerName')) AND
			@info_dist_publisher = @publisher) OR
			NOT EXISTS (SELECT * FROM #objectCheck)
		BEGIN
			EXEC @retcode = sys.sp_ORAdroppublisher	@publisher
		END
		ELSE
		BEGIN
			RAISERROR (21646, 16, -1, @publisher, @info_dist_publisher, @info_dist_distributor, @info_dist_db, 'MSSQLSERVERDISTRIBUTOR')
			RETURN (1)
		END
		
		DROP TABLE #objectCheck
	END

	DROP TABLE #synonymOwner

	-- Check to make certain that the Oracle administrator login has sufficient privilege
	exec @retcode = sys.sp_ORACheckAdminPrivileges @publisher = @publisher
	IF @@error <> 0 OR @retcode <> 0
	BEGIN
		RETURN (1)
	END	

	-- Install HREPL support and initialize on destination server
	EXEC @retcode = sys.sp_ORAloadscript	@publisher		= @publisher,
											@script			= N'instorcltables',
											@security_mode	= @security_mode,
											@login			= @remotelogin,
											@password		= @remotepassword

	IF @retcode != 0 OR @@ERROR != 0
	BEGIN
		RAISERROR (21647, 16, -1)
		RETURN (1)
	END
	
	-- Install HREPL support and initialize on destination server
	EXEC @retcode = sys.sp_ORAloadscript	@publisher		= @publisher,
											@script			= N'instorcl',
											@security_mode	= @security_mode,
											@login			= @remotelogin,
											@password		= @remotepassword

	IF @retcode != 0 OR @@ERROR != 0
	BEGIN
		RAISERROR (21647, 16, -1)
		RETURN (1)
	END
	
	-- Drop any residual article tracking tables or triggers
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery (cmd) VALUES (N'{call HREPL.INITPUBLISHER}')
	
	EXEC @retcode = sys.sp_IHquery @publisher
	IF  (@@error <> 0 OR @retcode <> 0)
	BEGIN
		RAISERROR (21651, 16, -1, 'INITPUBLISHER', @publisher)
       	RETURN (1)
	END

	SET @publisherGUID = CONVERT(varchar(255), @publisher_guid)

	-- Get connect information for publisher
	SET @distributor = @@SERVERNAME

	-- Obtain the id of the publisher
	SELECT	@srvid = srvid from master.dbo.sysservers
	WHERE	UPPER(srvname collate database_default) = UPPER(@publisher) collate database_default

	-- Create public synonym for distributor table
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery (cmd) VALUES (N'CREATE PUBLIC SYNONYM MSSQLSERVERDISTRIBUTOR FOR HREPL_Distributor')
	
	EXEC @retcode = sys.sp_IHquery @publisher
	IF  (@@error <> 0 OR @retcode <> 0)
	BEGIN
		RAISERROR (21621, 16, -1, 'MSSQLSERVERDISTRIBUTOR')
       	RETURN (1)
	END
	
	-- Grant select privilege to public on public synonym
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery (cmd) VALUES (N'GRANT SELECT ON HREPL_DISTRIBUTOR TO PUBLIC')
	
	EXEC @retcode = sys.sp_IHquery @publisher
	IF  (@@error <> 0 OR @retcode <> 0)
	BEGIN
		RAISERROR (21622, 16, -1, 'MSSQLSERVERDISTRIBUTOR')
       	RETURN (1)
	END
	
	-- Create public synonym for SetSQLOriginator
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery (cmd) VALUES (N'CREATE PUBLIC SYNONYM MSSQLSERVERSETSQLORIGINATOR FOR ')
	INSERT INTO #hquery (cmd) VALUES (@QuotedLogin)
	INSERT INTO #hquery (cmd) VALUES (N'.HREPL')

	EXEC @retcode = sys.sp_IHquery @publisher
	IF  (@@error <> 0 OR @retcode <> 0)
	BEGIN
		RAISERROR (21621, 16, -1, 'MSSQLSERVERSETSQLORIGINATOR')
       	RETURN (1)
	END

	--  Update the entry in HREPL_Distributor, marking publisher ready.
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery (cmd) VALUES (N'INSERT INTO MSSQLSERVERDISTRIBUTOR VALUES(')
	INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@publisher, '''') + N',')
	INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@distributor, '''') + N',')
	INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@dist_db, '''') + N', NULL, 1)')
	
	EXEC @retcode = sys.sp_IHquery @publisher
	IF  (@@error <> 0 OR @retcode <> 0 )
	BEGIN
		RAISERROR (21623, 16, -1, @publisher)
		RETURN (1)
	END

	--  Create publisher entry in HREPL_Publisher
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery (cmd) VALUES (N'INSERT INTO HREPL_PUBLISHER VALUES(')
	INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@publisherGUID, '''') + N',')
	INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@publisher, '''') + N',')
	INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255),@srvid) + N', 0, 0, ''00000000000000000000'', 0, 0, 0, 0, 0, 0, 0, 1, NULL, 0,')
	INSERT INTO #hquery (cmd) VALUES (QUOTENAME(CONVERT(NVARCHAR(255), @distributortimestamp, 121), '''') + N')')
	
	EXEC @retcode = sys.sp_IHquery @publisher
	IF  (@@error <> 0 OR @retcode <> 0 )
	BEGIN
		RAISERROR (21625, 16, -1, @publisher)
		RETURN (1)
	END

	RETURN(0)
END	

 
Last revision 2008RTM
See also

  sp_MSdrop_replcom (Procedure)
sp_MSrepl_adddistpublisher (Procedure)
sp_MSrepl_refresh_heterogeneous_publisher (Procedure)
sp_ORACheckAdminPrivileges (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