OLE DB Provider for Oracle
The Microsoft OLE DB Provider for Oracle provides access to Oracle databases. Using the Microsoft OLE DB Provider for Oracle, Microsoft® SQL Server™ distributed queries can query data in Oracle databases.
To set up 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 your 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 long 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..Ow
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.
Main Topics
Browse All Topics
by: danblakePosted on 2000-10-20 at 08:12:49ID: 4828499
Configuring Linked Servers
A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:
Remote server access.
The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
Freedom from the need to address diverse data sources differently.
Linked Server Components
A linked server definition specifies an OLE DB provider and an OLE DB data source.
An OLE DB provider is a dynamic-linked library (DLL) that manages and interacts with a specific data source. An OLE DB data source is any data file accessible through OLE DB. Although data sources queried through linked server definitions are usually databases, OLE DB providers exist for a wide variety of files and file formats, including text files, spreadsheet data, and the results of full-text content searches. The following table shows examples of the most common OLE DB providers and data sources for SQL Server.
OLE DB provider OLE DB data source
Microsoft OLE DB Provider for SQL Server SQL Server database, such as pubs or Northwind
Microsoft OLE DB Provider for Jet Pathname of .mdb database file
Microsoft OLE DB Provider for ODBC ODBC data source name (pointing to a particular database)
Microsoft OLE DB Provider for Oracle SQL*Net alias that points to an Oracle database
Microsoft OLE DB Provider for Indexing Service Content files on which property searches or full-text searches can be run
--------------------------
Note This release has been tested only against the Microsoft OLE DB Provider for SQL Server, Microsoft OLE DB Provider for Jet, Microsoft OLE DB Provider for Oracle, Microsoft OLE DB Provider for Indexing Service, and the Microsoft OLE DB Provider for ODBC. However, SQL Server distributed queries are designed to work with any OLE DB provider that implements the requisite OLE DB interfaces.
--------------------------
For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as SQL Server.
Linked Server Details
This illustration shows the basics of how a linked server configuration functions.
Linked servers are typically used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server breaks down the command and sends rowset requests to OLE DB. Rowsets are the central objects that enable all OLE DB data providers to expose data in tabular form. Conceptually, a rowset is a set of rows in which each row contains columns of data.
OLE DB then queries the provider for the rowset information. The provider, in turn, knows the specifics of calling the data source, opening the file, and returning the requested information. The rowset data is returned through the provider and OLE DB to SQL Server, where it is reconstructed and returned to the client application as a result set and set of output parameters (if applicable).
Managing a Linked Server Definition
When setting up a linked server, register the connection information and data source information with SQL Server. After registration is accomplished, that data source can always be referred to with a single logical name.
You can create or delete a linked server definition with stored procedures or through SQL Server Enterprise Manager.
With stored procedures:
Create a linked server definition using sp_addlinkedserver. To view information about the linked servers defined in a given SQL Server, use sp_linkedservers. For more information, see sp_addlinkedserver and sp_linkedservers.
Delete a linked server definition using sp_dropserver. You can also use this stored procedure to remove a remote server. For more information, see sp_dropserver.
With SQL Server Enterprise Manager:
Create a linked server definition using the SQL Server Enterprise Manager console tree and the Linked Servers node (under Security). Define the name, provider properties, server options, and security options for the linked server. For information about the various ways a linked server can be set up for different OLE DB data sources and the parameter values to be used, see sp_addlinkedserver.
Delete a linked server definition by right-clicking the linked server and clicking Delete.
Executing a Distributed Query
When executing a distributed query against a linked server, include a fully qualified, four-part table name for each data source to query. This four-part name should be in the form linked_server_name.catalog
SELECT emp.EmloyeeID, ord.OrderID, ord.Discount
FROM SQLServer1.Northwind.dbo.E
OracleSvr.Catalog1.SchemaX
WHERE ord.EmployeeID = emp.EmployeeID
AND ord.Discount > 0