Saturday, June 23, 2012

SQL Server 2008 CLR Stored Procedure - Connect to remote Oracle Database

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:
Application name: Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64)
File name: win64_11gR2_client.zip
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