SQL Server and Oracle: Making the Connection Linked servers can bring your SQL Server and Oracle databases together. Suppose you have a Microsoft . NET- enabled Internet ordering system but your warranty system is a legacy Oracle database application. You don't want to bill your commercial customers when they place orders for warranty replacement parts, so you need realtime answers from your Oracle database. Creating a linked server lets you use SQL Server to query your Oracle database in realtime to find out who your existing customers are. When your data is distributed across several databases on multiple SQL Servers, linked servers let you run queries distributed across those servers.

When all the servers are SQL Servers, configuration is easy, and SQL Server Books Online (BOL) covers everything you need to know. However, if some of your data is on an Oracle database, for example, configuring a linked server brings special challenges. You need to understand that even if you configure an Oracle linked server in SQL Server Enterprise Manager, your SQL Server is a client of your Oracle database. Therefore, you must successfully install and configure Oracle client software on your SQL Server.

• ODAC 12c Release 1 (12.1.0.1.0) Installation Instructions, Setup, and Notes. August 2013. Download includes the following products: a) Oracle Developer Tools for.
• The Network Administrator tool kit contains 11 free tools that will make your life easier. Includes a 30 page PDF that explains how to get the best out of all these.

For this installation, you need either the DVDs or a downloaded version of the DVDs. In this tutorial, you install from the downloaded version. Microsoft Data Access Components (MDAC; also known as Windows DAC) is a framework of interrelated Microsoft technologies that allows programmers a uniform and.

Oracle provides product support only for Oracle. Oracle. 8 or later. The Oracle Net. 8 library provides the client software that SQL Server needs. In Oracle, a schema is the name for the entity SQL Server professionals know as a database.

To connect to Oracle, you need to supply the schema name, password, and host string. An Oracle schema is owned by a specific Oracle username, so the schema name is also the username of the user who owns the schema. One Oracle username owns only one schema. You can find out more about the contents of a schema by querying the Oracle data dictionary, as I describe in the sidebar . The Oracle host string is also known as a service name or System Identifier (SID).

Udit, If there’s no remote database that you can connect to and you want to practice Oracle then you will have to install Oracle RDBMS Server on your local machine/PC. The contents of relative. Installation This section describes how to install SP2. Note that to install SP2, you must log in to the TestDirector server machine with administrator privileges.

What SQL Server calls an instance, Oracle calls a database. During an Oracle server installation, Oracle Universal Installer (a graphical interface similar to SQL Server's installation program) asks for a SID to use as the name of the Oracle database. If you install your Oracle instance on a Windows machine and set your SID to Ora. Windows 2. 00. 0 service called Oracle. Service. ORA8. 17. This service is analogous to the MSSQLSERVER service for SQL Server 2.

For information about the architectural differences between the two products, see the Microsoft article . Dcs A 10C Beta 4 Patch Engineering. Oracle servers and clients use Net.

Transparent Network Substrate (TNS), an Oracle protocol adapter, and a supported network library. Net. 8 uses a protocol adapter for translations between TNS and the network library. Net. 8, a replacement for SQL*Net, uses service names to find servers. The network libraries Net.

TCP/IP, SPX, Named Pipes, Logical Unit Type 6. LU6. 2), and Bequeath. TCP/IP and SPX are the network libraries you can use to connect remote clients to Oracle servers. You use LU6. 2 for compatibility with the IBM Advanced Program- to- Program Communications (APPC) architecture. Net. 8 uses the Bequeath network protocol for making connections when an Oracle client is logged in locally to the Oracle server. For a Net. 8 client to make a successful connection to an Oracle server, the client must be able to find the service name for the server. Oracle clients can resolve service names by using a local client file called tnsnames.

Windows HOSTS file), the DNS, or an Oracle Names Server. With Oracle. 9i, Lightweight Directory Access Protocol (LDAP) becomes another service- name resolution option. However, at press time, Microsoft didn't officially support Oracle. The Java- based Oracle Universal Installer helps you install Oracle server and client software on both Windows and UNIX systems. On a Windows system, inserting the Oracle installation CD- ROM autostarts Oracle Universal Installer. I recommend selecting a default installation of the Oracle. Net. 8, the default installs the Net.

Assistant, the Net. Configuration Assistant, and SQL*Plus and adds these options to your Start menu. SQL*Plus is a client tool comparable to SQL Server's osql. Troubleshooting Using SQL Server to validate a linked- server connection to Oracle can give misleading results. Until you execute a linked- server query, you can't be certain whether you configured the link correctly.

Keep in mind that you can issue a linked- server query indirectly. In Enterprise Manager, if you select Tables or Views under your linked- server definition, you're indirectly querying the Oracle data dictionary. After I issued such a query, I received the error message in Figure 1. But when I executed Oracle's TNSPING command (similar to a TCP/IP PING, but specifically for testing connectivity to an Oracle database) from a command prompt, the results showed that Oracle client and networking components were installed, as Figure 2 shows. Using either of those network administration tools is sufficient for validating a database connection, so I knew that the problem lay elsewhere. When you're creating linked servers to non- Microsoft databases, check the PATH environment variable.

Note that Oracle incorrectly appears before SQL Server in the PATH variable that Figure 4 shows. To prevent the error message I received, edit PATH as Figure 5 shows, placing SQL Server ahead of any other vendor you're linking to. After I corrected PATH, the linked queries worked correctly. Configuring SQL Server Creating a linked Oracle server on SQL Server 2. You must manually add a key to the client machine's registry.

You can find the appropriate registry files, which begin with mtx, in the C: \program files\common files\system\ole db folder. Double- click the appropriate registry file to add the necessary key. For more information, see BOL; go to Accessing and Changing Relational Data, Distributed Queries, OLE DB Providers Tested with SQL Server, OLE DB Provider for Oracle, then look under Registry Entries. You create linked servers by executing a series of two system stored procedures. To begin, you use sp. The following example specifies Microsoft Data Access for Oracle: sp.

Although Microsoft officially supports and recommends only the Microsoft OLE DB Provider for Oracle, this provider has limitations that I show later in an INSERT example. If you experience problems when you're using Microsoft's provider, try using Oracle's provider before calling Microsoft technical support. The next step is to use sp. SQL Server is like any other Oracle client in that it must supply a username and password to connect to the Oracle server.

You can provide the username and password to sp. In the first example that follows, I had already created a SQL Server login of sys with a password of change. By default, the Oracle user sys has a password of change. Because the usernames and passwords for the two database servers match, the SQL Server credentials can impersonate the Oracle credentials. No mapping of SQL Server credentials is required in this case, so I used true as the value of the second parameter, indicating that impersonation of credentials will take place. In the second example, the Win.

K user Administrator is mapped to the Oracle user scott with a password of tiger. The last example directs all other SQL Server logins to connect as scott with a password of tiger.

The latter two examples pass specific credentials; because they don't use impersonation, the second parameter is false. You can access this dialog box from Enterprise Manager by opening Linked Servers in the Security folder.

Executing an Oracle Query from SQL Server You can query the SCOTT schema by using Oracle's SQL*Plus or a third- party freeware tool such as Quest Software's TOAD, which Figure 7, page 2. TOAD, however, is comparable to SQL Server 2. Query Analyzer and Object Browser.

To start SQL*Plus, choose Start, Programs, Oracle, Application Development, SQL*Plus. In the Log On dialog box that Figure 8, page 2.