Running Queries with the Oracle Linked Server
Once you’ve created and configured your linked server you can begin using it to run queries on the Oracle server. Queries that are directed to linked servers use the following four-part naming syntax: linked_server_name.catalog_name.schema_name.table_name. For example, to select all of the rows from the EMP table you would use the following SQL statement:
SELECT * FROM [OR-PORT-VORA11G]..SCOTT.EMP.
OR-PORT-VORA11g is the linked server name. It’s enclosed in brackets because the name contains dashes. When using the Oracle OLE DB provider, the catalog name should be left blank. The schema name is SCOTT. This corresponds to what SQL Server users would consider the database name. The table name is EMP. You can see the results of running the linked server query in Figure 8.
One important point to note regarding linked servers is that the SQL Server distributed query processor is responsible for optimizing the commands that are sent to the linked server to retrieve data. SQL Server will first query the linked server to determine the level of SQL dialect that it supports and attempt to push operations such as joins, sorts, and grouping to the remote server. For more information about how SQL Server optimizes distributed queries, go to msdn.microsoft.com/en-us/library/ms180972.aspx.
Seamlessly Connect SQL Server and Oracle
SQL Server’s linked server feature lets you create a nearly seamless connection between SQL Server and Oracle. Linked servers can be used for queries and database insert, update, and delete operations. If you need to retrieve and use Oracle data in your SQL Server applications, then linked servers could be just the ticket you’ve been waiting for.