Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAcheckpublisher

  No additional text.


Syntax


-- Name:
--		sp_ORAcheckpublisher

-- Description:
--		Determine if an ad-hoc Oracle datasource
--		is currently published by checking the synonym.

-- Returns:
--		Result set:
--		Distributor name that has published this Oracle db
--		NULL if not published

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

-- Notes:
--		Used by the UI


CREATE PROCEDURE sys.sp_ORAcheckpublisher
(
	@publisher			sysname,
	@security_mode		bit,
	@login				varchar(30),
	@password			varchar(30),
	@connect_timeout	int
)
AS
BEGIN
	DECLARE @retcode		int
	DECLARE @timeout		nvarchar(50)
	DECLARE	@conn			nvarchar(max)
	DECLARE	@conn2			nvarchar(max)
	DECLARE @command        nvarchar(max)
	DECLARE @is_published	bit
	DECLARE @provider		sysname

	SELECT @timeout = CONVERT(nvarchar(50), @connect_timeout)

        -- If the login is entered double quoted, we will retain the value as entered keeping
        -- the double quotes.  Otherwise, we will upper case the entered value to mirror
        -- PL/SQL behavior.
        IF NOT ((substring(@login, 1, 1) = '"') AND (substring(@login, LEN(@login), 1) = '"'))
        BEGIN
	    SET @login = UPPER(@login)
        END

	-- Verify connection
	EXEC @retcode = sys.sp_MSrepl_testconnection
					@publisher_type		= N'ORACLE',
					@publisher			= @publisher,
					@security_mode		= @security_mode,
					@login				= @login,
					@password			= @password,
					@connect_timeout	= @connect_timeout
					
	IF @@ERROR<>0 OR @retcode<>0
	BEGIN
		RETURN (1)
	END

	-- Get provider
	EXEC @retcode = sp_IHgetprovider N'ORACLE', @provider OUTPUT
	
	IF @@ERROR<>0 OR @retcode<>0
	BEGIN
		RETURN (1)
	END

	EXEC @retcode = sys.sp_CheckOracleAdminPrivileges
		    @publisher,
		    @login,
		    @password,
		    @provider
	IF @@ERROR<>0 OR @retcode<>0
	BEGIN
		RETURN (1)
	END

	-- Define connection string for OPENDATASOURCE
	SET @conn = N'Data Source=' + @publisher + N';' +
				CASE WHEN @security_mode = 0 THEN
					N'User ID=''' + sys.fn_replreplacesinglequote(@login) +
					N''';Password=''' + sys.fn_replreplacesinglequote(@password) + N''';'				
				ELSE
					N'Integrated Security=true;'
				END + N'Timeout=' + @timeout + N';'


	-- Define connection string for OPENROWSET
	SET @conn2 = QUOTENAME(@publisher,'''') +
				CASE WHEN @security_mode = 0 THEN
				  + N';' + QUOTENAME(@login,'''')
				  + N';' + QUOTENAME(@password,'''')
				ELSE
				  + N''			
				END
	SET @conn2 = sys.fn_replreplacesinglequote(@conn2)

	SET @command = N'declare @table_owner sysname, @table_name sysname, @cmd2 nvarchar(max) ' +
                      N'SELECT @table_owner = QUOTENAME(TABLE_OWNER,''"''), @table_name = TABLE_NAME ' +
                      N'FROM OPENDATASOURCE(''' + @provider + ''', ' + QUOTENAME(@conn, N'''') +
                      N')..SYS.ALL_SYNONYMS WHERE TABLE_NAME=''HREPL_DISTRIBUTOR'' AND SYNONYM_NAME=''MSSQLSERVERDISTRIBUTOR'' ' +
                      N'SELECT @table_owner = sys.fn_replreplacesinglequote(@table_owner) ' +
                      N'IF @table_owner IS NOT NULL AND @table_name IS NOT NULL BEGIN ' +					
                      N' SELECT @cmd2 = ''SELECT * FROM OPENROWSET( ''''' + @provider + ''''', ' +  @conn2 + N', ' +
                      N' ''''select DISTRIBUTOR_DISTRIBUTORNAME AS Distributor, DISTRIBUTOR_PUBLISHERNAME AS Publisher FROM '' + @table_owner + ''.HREPL_DISTRIBUTOR'''') '' ' +
                      N' EXEC (@cmd2) END ' +					 				
                      N'ELSE SELECT NULL AS Distributor, NULL AS Publisher '

	--- taking care of the case that HREPL_DISTRIBUTOR table does not exists but has SYNONYMS.
	BEGIN TRY
		EXEC(@command)
	END TRY
	BEGIN CATCH
		--On any error here we return null Distributor and Publisher
		SELECT NULL AS Distributor, NULL AS Publisher
	END CATCH

	RETURN (0)
END

 
Last revision 2008RTM
See also

  sp_CheckOracleAdminPrivileges (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSrepl_check_publisher (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