Contents
How do I install Oracle provider for OLE DB in SQL Server?
Solution
- Install Oracle 10g Release 2 64-bit client software (available from Oracle website)
- Install Oracle 10g Release 2 64-bit ODAC software (available from Oracle website)
- Restart SQL services.
- Configure OraOLEDB. Oracle provider.
- Create linked server.
- Add remote logins for linked server.
How do I create a SQL linked server in Oracle?
Creating Oracle Linked Server in SQL Server
- Connect to SQL Server in SSMS and then expand Server Objects folder, right click on Linked Servers folder and then click “New Linked Server…”
- Now update linked server details in the wizard as shown below.
What is Oracle provider for OLE DB?
Oracle Provider for OLE DB (OraOLEDB) is an OLE DB data provider that offers high performance and efficient access to Oracle data by OLE DB consumers. With the proper connection attribute setting, an OLEDB.NET Data Provider can utilize OraOLEDB to access Oracle Database.
Can you create an instance of OLE DB provider?
Cannot create an instance of OLE DB provider “OraOLEDB.Oracle” for linked server. We have enabled Allow in Process in SQL Server for the Oracle Provider.
How to create a linked server in SQL Server?
The Allow InProcess option must be set on the specific provider before the linked server is created. You can add the linked server provider option under [SQL Server Database] -> Server Objects -> Linked Servers -> Providers -> Right-click on a provider, and select Properties.
Why do I get an error when I try to create a linked server?
You try to create or run a linked server query. The linked server query is created or run under the context of a user who is not a local administrator. In this scenario, you may receive an error message that resembles the following: This issue can occur if the security settings for the MSDAINITIALIIZE DCOM class are incorrect.
How to add an administrator to an Ole?
On the Security tab, under Access Permissions, click Customize, and then click Edit. Click the Administrators group or user account, and then click to select the following check boxes under Allow: Note Add the Administrator group or user account if it is necessary. Click OK.