How to set-up a linked server to oracle

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to set-up a linked server to oracle

Author

Topic

neil
Starting Member

29 Posts

Posted - 2004-04-28 : 16:10:54

I'm wanting to set-up an linked server to oracle in sql 2000.

I have the oracle client installed on the sql 2000 box. When i try to view the tables through EM for the linked server. EM just hangs - no error messages.

Could someone tell me where I'm going wrong, I am using the following settings:
Product name:Oracle
Data source:<oracle instance> (which is set-up in the oracle tnsnames.ora file)
Provider string: MSDAORA

I've supplied an oracle username and password for the linked server to use.

Thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-28 : 17:19:23

Have you followed the directions in Books Online? Pay particular attention to the pieces on registry entries and Client software.

OLE DB Provider for Oracle
The Microsoft OLE DB Provider for Oracle allows distributed queries to query data in Oracle databases.

Note Oracle client software does not support distributed queries using Microsoft OLE DB Provider for Oracle when the distributed transactions are executed from an instance of SQL Server installed on Microsoft Windows® 98.

To create a linked server to access an Oracle database instance

Ensure the Oracle client software on the server running SQL Server is at the level required by the provider. The Microsoft OLE DB Provider for Oracle requires Oracle Client Software Support File version 7.3.3.4.0 or later, and SQL*Net version 2.3.3.0.4.

Create an SQL*Net alias name on the server running SQL Server that points to an Oracle database instance. For more information, see the Oracle documentation.

Execute sp_addlinkedserver to create the linked server, specifying MSDAORA as provider_name, and the SQL*Net alias name for the Oracle database instance as data_ source.
This example assumes that an SQL*Net alias name has been defined as OracleDB.

sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB'

Use sp_addlinkedsrvlogin to create login mappings from SQL Server logins to Oracle logins.
This example maps the SQL Server login Joe to the linked server defined in Step 3 using the Oracle login and password OrclUsr and OrclPwd:

sp_addlinkedsrvlogin 'OrclDB', false, 'Joe', 'OrclUsr', 'OrclPwd'

Each Oracle database instance has only one catalog with an empty name. Tables in an Oracle linked server must be referenced using a four-part name of the form OracleLinkedServerName..OwnerUserName.TableName. For example, this SELECT statement references the table SALES owned by the Oracle user MARY in the server mapped by the OrclDB linked server:

SELECT *
FROM OrclDB..MARY.SALES

Use these rules when referencing tables in an Oracle linked server:

If the table and column names were created in Oracle without quoted identifiers, use all uppercase names.

If the table and column names were created in Oracle with quoted identifiers, use the same case for all letters of the names as was used when the names were created in Oracle.

INSERT statements should supply values for all columns in a table even if certain columns in the table can be NULL or have default values.
Registry Entries
To enable the OLE DB Provider for Oracle to work with your Oracle client software, the client's registry must be modified by running a registry file from a command line. Multiple instances of the client software should not run concurrently. These files are listed in the following table and are located within the same directory structure that contains your Microsoft Data Access Component (MDAC) installation, which typically is in C:\Program Files\Common Files\System Files\OLE DB.

Oracle client Windows NT or 9x Windows 2000
7.x mtxoci7x_winnt.reg mtxoci7x_win2k.reg
8.0 mtxoci80x_winnt.reg mtxoci80x_win2k.reg
8.1 mtxoci81x_winnt.reg mtxoci81x_win2k.reg

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

neil
Starting Member

29 Posts

Posted - 2004-04-29 : 05:22:13

Yes i did follow the instructions in BOL -

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-29 : 08:37:22

can you do a SELECT LINKEDTABLE.database.owner.object then from Query analyzer?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources