MORE INFORMATION
Microsoft SQL Server version 7.0 provides the ability to perform queries
against OLE DB providers. This is done by using the OpenQuery or OpenRowset
Transact-SQL functions or by using a query with four-part names including a
linked-server name. For example,
EXEC sp_addlinkedserver 'OraDB', 'Oracle', 'MSDAORA', 'MyServer'
GO
-- add a login mapping using sp_addlinkedsrvlogin as needed
select * from OraDB..SCOTT.SALES
where QTY > 100
SQL Server delegates as much of the query execution as possible to an OLE
DB provider that supports SQL. There are, however, some operations that SQL
Server does not delegate to the provider, because such operations do not
have standard SQL syntax and semantics. One such operation is type
conversion. This article describes how to determine why a query is not
being delegated to the provider and provides techniques to rewrite the
query so that it is delegated.
If the query involves arithmetic or Boolean expressions, where the two
operands of a comparison or arithmetic operation are of different type, an
implicit conversion is done to one of the types before the expression is
evaluated. SQL Server does not delegate such queries to providers other
than SQL Server. The user can rewrite the query in such a way that data
type conversion is not necessary, thereby allowing SQL Server to delegate
more of the query to the provider.
Examples
In Oracle, all fixed-point numeric data (integer, numeric, decimal) are
represented as one internal type number(p,s) where p and s are the
precision and scale respectively. Integers are represented as numeric with
a scale of 0. Therefore, any comparisons of such columns against integer
constants require a type conversion. For example for the query
select * from OraDB..SCOTT.SALES
where QTY > 100
the predicate QTY > 100 is not sent to Oracle because of the type
conversion needed from 100 to QTY because they are of distinct types. This
query can be rewritten in one of two ways to make SQL Server delegate the
predicate to Oracle:
- Change the constant to a type that matches the Oracle column type.
select * from OraDB..SCOTT.SALES
where QTY > 100.0
-or-
- Change the constant to a parameter of type that matches the Oracle
column type.
declare @var1 numeric(10,0)
select @var1 = 100
select * from OraDB..SCOTT.SALES
where QTY > @var1
This technique can be used in general for any OLE DB provider; the general
idea is to determine the type of the column and use a constant or parameter
of the same type. The second solution is applicable only if the provider
supports parameterized query execution using '?' as parameter markers.
For more information about setting up and using Distributed Queries, search
on sp_addlinkedserver, OpenQuery, OpenRowset, and related topics in SQL
Server 7.0 Books Online.
For more information about Oracle data types, please refer to the section
'Data Types in Oracle and SQL Server' in the SQL Server 7.0 Books Online,
as well as the Oracle documentation.