SQL Server 2000 offers an alternative to creating a view. Inline table-valued user-defined functions (UDFs) can give you the same functionality as a parameterized view. You can query UDFs as you would a view, but UDFs can accept parameters, which a view can't. You can create the following UDF:
CREATE FUNCTION fn_oradb_scott_dept (@deptno int)
RETURNS TABLE
AS RETURN (SELECT * FROM oradb..SCOTT.DEPT WHERE deptno
= @deptno)
When you execute this function instead of querying the view, as follows, the WHERE clause is resolved remotely on the Oracle server:
SELECT * FROM fn_oradb_scott_dept(10)
This approach gives you better performance and reduced network traffic compared with using a view.
Updating Linked Data
Now, consider executing a simple INSERT statement from SQL*Plus. Note that unlike SQL Server, Oracle requires the INSERT INTO syntax in every INSERT statement:
INSERT INTO dept (deptno) values (1);
If you execute a similar query from Query Analyzer, as follows,
INSERT INTO oradb..SCOTT.DEPT (deptno) values (2)
you get the following error message:
Server: Msg 7344, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' could not INSERT INTO table
'[oradb]..[SCOTT].[DEPT]' because of column 'DNAME'. The
column used the default value.
[OLE/DB provider returned message: Multiple-step operation
generated errors. Check each status value.]
This example illustrates a limitation of the Microsoft OLE DB provider. The DEPT table has three columns: deptno, dname, and loc. The dname and loc columns are nullable, so they shouldn't be necessary in an INSERT statement. The Microsoft provider doesn't properly accommodate nullable columns if they're not supplied in an INSERT statement, but you can use a simple workaround. Specify the nullable columns and give them values:
INSERT oradb..SCOTT.DEPT (deptno,dname,loc) values (2,NULL,NULL)
Also note that when you're inserting data into an Oracle table from SQL Server, you don't need to include INTO. Both the Microsoft and Oracle OLE DB providers add INTO to the INSERT statement they execute on the Oracle server.
Using the Oracle Provider for OLE DB, I created a new linked server named orclprvdr and attempted a linked-server INSERT, as follows:
INSERT orclprvdr..SCOTT.DEPT (deptno) values (3)
This query worked, so you can see that Oracle's provider offers advantages when you're doing linked-query INSERT statements. However, Oracle's provider can't execute an INSERT into a table that contains a column that has a DEFAULT constraint defined on it. Microsoft's provider can execute that INSERT as long as you pass an actual value for the column instead of using the keyword DEFAULT.
Unlike an INSERT statement, an UPDATE statement doesn't have problems with nullable columns. If you're issuing a linked-server UPDATE to Oracle, you don't need to specify the nullable columns if you aren't updating them.
UPDATE oradb..SCOTT.DEPT set deptno = 3 WHERE deptno = 2
If you need to join a SQL Server table to an Oracle table, you're executing a heterogeneous query and you must have a linked server. To construct the following example, I used Data Transformation Services (DTS) to move a copy of the Northwind database's Orders table to the SCOTT schema, changing the default destination of "SCOTT"."Orders" to "SCOTT"."ORDERS". I changed the case here to avoid having to use quoted identifiers when I'm using Oracle tools to refer to the table. Oracle databases can support mixed-case object names, but administration and development of an Oracle database is easier when you use all-uppercase object names. The following query joins the Northwind Employees table to the SCOTT schema's copy of Orders:
SELECT lastname FROM employees e
INNER JOIN oradb..SCOTT.ORDERS o
ON e.employeeid = o.employeeid
WHERE o.orderid = 10248
If your heterogeneous query needs to be inside a transaction, you must have the Microsoft Distributed Transaction Coordinator (MS DTC) service running on your SQL Server. For more information, see the sidebar "Transactions," page 28.
Here's one more way to execute a query when you're using a linked-server definition. SQL Server's OPENQUERY system-defined function passes the query through from SQL Server to Oracle:
SELECT * FROM OPENQUERY (oradb,'SELECT * FROM dept
WHERE deptno = 10')
Using OPENQUERY doesn't incur the overhead of the Oracle metadata lookups that I discuss in the sidebar "Analyzing Linked-Server Queries," page 30. With OPENQUERY, all query processing happens on the Oracle linked server and you avoid the metadata lookups. OPENQUERY performs a linked-server query better, but you can't use it in a heterogeneous query.
OPENROWSET is a pass-through query that doesn't use a linked-server definition. It's similar to OPENQUERY, except that it requires you to provide all connection criteria as input parameters:
SELECT * FROM OPENROWSET ('MSDAORA','Ora817';
'SCOTT';'TIGER',' SELECT * FROM dept
WHERE deptno = 10')
Linked servers are only for executing queries, but as the sidebar "Executing Stored Procedures Remotely" shows, you can execute a query to execute a stored procedure.
Bear in Mind
Before you can use your linked-server setup effectively, you need to know the differences between Oracle and SQL Server data types. If you're storing historical dates in both Oracle and SQL Server, be aware that Oracle's date data type can store dates back to January 1, 4712 b.c., but SQL Server's datetime data type begins at a.d. January 1, 1753. Additionally, Oracle's date data type has a granularity of 0.01 second and SQL Server's datetime has a granularity of 0.001 second. (For more information about the SQL Server datetime data type, see Kalen Delaney, Inside SQL Server, "Solving the Datetime Mystery," September 2000.) For more information about Oracle data types, go to http://technet.oracle.com. You'll need to register (it's free) to get access to the Oracle documentation.
Sometimes you need to get data from more than one database or even from another database server. And when the other database is Oracle, that task is particularly challenging. Microsoft isn't in the business of providing customer support for Oracle databases, and Oracle isn't in the business of providing customer support for SQL Server. However, linked servers can provide convenient access to Oracle data from SQL Server. Now that you know how to configure and use Oracle linked servers, you can get realtime Oracle data in the SQL Server environment you're comfortable in.
End of Article
Prev. page
1
2
[3]
next page -->
