Content area
Remember, [MARTIN] is the necessary alias to Oracle, and not an actual Oracle instance. You create the alias using the SQL*NET tools on the Oracle Server; the alias simply serves as a pointer to the Oracle database. Later, when you use SQL to retrieve data, you just specify the alias.
Full text
A department in my organization is using Microsoft SQL Server and they need it to link directly to data on the Oracle database. I'm not an expert on SQL Server, so how can I show them how to connect?
Applications:
Oracle RDBMS 8+, Microsoft SQL Server 7+
In a perfect world, everything would be in Oracle databases, and then you wouldn't have this problem. But sometimes you may need to work together with other databases as well as Oracle. In this case, you're asking about linking to Oracle from SQL Server, so we'll have to discuss a little bit about how linked servers work for that application.
Set up the server alias
If your friends in the other department are having difficulty, it may be because you must set up a server alias on Oracle NET protocols. As you may know, SQL*NET is a communication protocol that listens for requests sent to the server. Without this protocol, a SQL Server linked server won't work.
To link SQL Server to Oracle:
1. Create an alias for Oracle, using the NET Configuration Assistant. As an example, we created an alias named MARTIN for an Oracle9i database.
2. Create the actual linked server (e.g., server that links to MARTIN).
To create a linked server using MARTIN named OracleTest, run the following statement in the Query Analyzer (SQL Server's equivalent to SQL*Plus):
Remember, MARTIN is the necessary alias to Oracle, and not an actual Oracle instance. You create the alias using the SQL*NET tools on the Oracle Server; the alias simply serves as a pointer to the Oracle database. Later, when you use SQL to retrieve data, you just specify the alias.
This arrangement is different from SQL Server links to other data sources, in which case the data source is a file.
After creating the linked server in the SQL Server application:
1. Open SQL Server's Enterprise Manager and press [F5] to refresh the linked servers.
2. Right-click on the newly linked server, OracleTest, and choose Properties from the resulting shortcut menu to display the options shown in Figure A. As you can see, we've selected the Be Made Using This security Context option button and entered the appropriate username and password to gain access to the Oracle database via the SQL*NET alias MARTIN.
3. After setting the properties, return to the SQL Server application.
As the final step, execute the following statement to retrieve the DNAME, LOC, DEPTNO fields from the DEPT table owned by Scott:
Remember, the linked server MARTIN already knows which Oracle database to hit, so you don't have to reference the database by name.
Copyright Element K Journals May 2006