April 13, 2011 at 3:51 am
Hello there,
I'm trying to access my customers Oracle database with a SQL Linked Server. The error I get is:
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server...
I did the following steps:
1. Installed Oracle client 10g with a custom installation and selected everything.
2. Created a tnsnames.ora
dev =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dijkora01)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = dev)
)
)
3. Tested this with tnsping dev, which said that the connection is OK.
4. Activated 'Allow inprocess' for my OraOLEDB.Oracle provider for the linked servers in my SQL Server Management Studio.
5. Created a new linked server with:
Linked Server: IAP
Provider: Oracle Provider for OLE DB
Product name: Oracle
Data source: dev
In the security tab I activated 'Be made using this security context'. When I now press 'OK' I get the above error message.
Can someone help me with this?
April 14, 2011 at 3:28 pm
Make sure that you have the right Oracle Client installed.
I know we had problems because the ORA client was a 32 bit version and the Oracle Instance was an x64.
try to drop the linked server and recreate it again once you have verified the client
EXEC master.dbo.sp_addlinkedserver @server = N'MyOracleServer',
@srvproduct = N'Oracle', @provider = N'OraOLEDB.Oracle',
@datasrc = N'OracleDB'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyOracleServer',
@useself = N'False', @locallogin = NULL, @rmtuser = N'MyOracleUser',
@rmtpassword = 'MyOraclePasswod'
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply