Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_set_oracle_provider_inproc_on64bit

  No additional text.


Syntax

-- Name: sp_MSrepl_set_oracle_provider_inproc_on64bit

-- Descriptions: Set oracle oledb provider to work inproc on 64 bit machine due to a bug in oracle outproc provider.

-- Parameters: as defined in create statement

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

CREATE procedure sys.sp_MSrepl_set_oracle_provider_inproc_on64bit
AS
	SET NOCOUNT ON
	DECLARE @64bit	 int			
	DECLARE @retcode  int
	select  @64bit = 0

	-- Get version info
	declare @tmp_table TABLE (idx smallint,name	sysname,internal_value varbinary(8), character_value nvarchar(120))

	-- Get 64 bit indicator	
	INSERT @tmp_table EXEC master.dbo.xp_msver 'Platform'
	SELECT	@64bit = COUNT(*) FROM @tmp_table WHERE idx = 4  AND character_value like '%64%'		
	
	-- Not a 64 bit machine ? - nothng to do
	IF(@64bit =  0)
		RETURN (0);
	
	declare @regkey nvarchar(4000)
	declare @InstanceName sysname;
	declare @InstanceId sysname;					
	declare @AllowInProcess int
	declare @AllowInProcessRegName  sysname								

	-- get the instance name
	SELECT @InstanceName =  CONVERT(sysname, SERVERPROPERTY('InstanceName'))
	IF @InstanceName IS NULL
		SELECT @InstanceName  =  'MSSQLSERVER'


	-- map instance name to instance id
	SELECT @regkey = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
	DECLARE @tblValueExists table ([value] nvarchar(260), [data] nvarchar(260))	
	INSERT INTO @tblValueExists EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
		@regkey,
		@InstanceName

	IF((SELECT COUNT(*) FROM @tblValueExists) = 0)
	BEGIN
		RAISERROR(21628, 16, -1, @@SERVERNAME)
		RETURN(1)
	END
	
	SELECT @InstanceId = (SELECT [data] FROM @tblValueExists)
	DELETE @tblValueExists		

	-- set instance id registry path
	SELECT @regkey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' +  @InstanceId + '\Providers\OraOLEDB.ORACLE'

	-- read the AllowInProcess value
	SELECT @AllowInProcessRegName = 'AllowInProcess'	
	SELECT @AllowInProcess = null
	INSERT INTO @tblValueExists EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
		@regkey,
		@AllowInProcessRegName					

	-- The registry key was already set - so bail out.
	IF((SELECT COUNT(*) FROM @tblValueExists) <> 0)
	BEGIN
		RETURN(0)
	END

	-- allow inproc access to the oracle provider if the AllowInProcess value was not found
	EXECUTE @retcode =  master.dbo.xp_regwrite 'HKEY_LOCAL_MACHINE',
					   @regkey,
					   @AllowInProcessRegName,
					   'REG_DWORD',
						1

	IF(@retcode <> 0)
	BEGIN
		RAISERROR(21628, 16, -1, @@SERVERNAME)
		RETURN(1)
	END



 
Last revision 2008RTM
See also

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