Good morning,
Recently I had to connect my Microsoft SQL Server 2008 R2
64Bit installed on Microsoft Windows Server 2008 64bit, with an external Oracle
connection using CLR Stored Procedure.
I became crazy how to configure the OLEDB Provider,
provided by Oracle (OraOLEDB.Oracle). Indeed Microsoft does not provide a
64-bit OLEDB Provider for Oracle. Thus, if you try to link an external Oracle database
with the default installation, forget it.
Below I want to give you the steps that must be followed for running a CLR stored procedure that is linked to a remote Oracle database.
- Download both 32 and 64 bit version from Oracle
web site. It is mandatory to create a login and password. The links and files
are these:
URL: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
Application name: Oracle
Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64)
File name: win64_11gR2_client.zip
URL: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
Application name: Oracle
Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit)
File name: win32_11gR2_client.zip
-
Create a new folder in the server like “C:\Oracle”
-
Launch the 32bit setup.exe.
-
Select “Custom installation”, otherwise you will
consume 600MB for one DLL…
-
Set as default folder installation the new
folder created previously (C:\Oracle)
-
Choose the following option to install:
-
Do the same with 64bit Setup.exe
- Restart the server (it is not required, but I had issues try to connect the provider the first time)
- If everything went well, you
should see now the provider in the linker servers list.
-
If you need to link the server (this was not my
case), you must execute the following transact SQL script.
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'DynamicParameters', 1
GO
Fantastic, now we can try to execute
the CLR stored procedure.
Two couple of things to remember:
1) Remember to use OLEDBConnection objects in the code
Imports System.Data.OleDb
Dim sqlRemoteConnection As OleDbConnection = New OleDbConnection
Dim sqlRemoteCommand As OleDbCommand = New OleDbCommand
2) Setup the connection string
properly. After several error messages coming out during the attempt connection, I
finally used:
Provider=OraOLEDB.Oracle; Data Source=94.84.XXX.43;User ID=USERTEST; Password=PASSWORDTTEST
If you try to use the voice “Initial Catalog” you get an error (I read it is not supported in oracle).
Now executing the CLR stored
Procedure, no errors come out, data are transferred correctly between two
different data source system.
EXEC spIntDBNewTicket
@IDIntegration = 'ST',
@IDFromTo = 'COVER-ST',
@ReturnMessage = null
That’s all.
Fabio
