I need to pull records from a linked server based on values in my local
tables. For example I have a table with employee numbers on my local
server. These numbers are a very small subset of the linked servers
records. I also need to filter on Fiscal_Year, Accounting Period and Cycle.
All these values must come from a local table. I do not have access to the
external server so I cannot load my MSSQL tables out to the linked server.
I am planning on using the OPENQUERY option, but I need to know if the
statement will pull all rows from the linked server and then perform the
WHERE on my local server. This would not be acceptable as the linked server
is a financial data warehouse with millions of rows. I am hoping that the
OPENQUERY will only bring across the rows that match the criteria of my
local table. Is this the case? Thanks in advance.....

Here is my query statement:

SELECT *
FROM OPENQUERY([CDW], 'SELECT * FROM VW_EMPLOYEE') Rowset_1
WHERE (FISCAL_YEAR IN (SELECT FISCAL_YEAR FROM DW_CURRENT_PERIOD) AND
PERIOD IN (SELECT PERIOD FROM DW_CURRENT_PERIOD) AND CYCLE IN (SELECT CYCLE
FROM DW_CURRENT_PERIOD) AND EMP_NO IN (SELECT EMP_NO FROM OLTP_EMPLOYEE))

All tables in the where portion on the statement are in my local dbase and
VW_EMPLOYEE is in the linked server.

Thanks in advance for any assistance!!

With OPENQUERY the query should be done as a passthrough so you only get the
rows you need.


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Robert Thomas" <thomasjrr (AT) saic (DOT) com> wrote

That is what I thought but when I run the query it seems to me that it is bringing every single record across and then performing the filter. This is also what is shown in query analyzer. I am using Oracle 9 client and I saw this section in BOL which I think may be why my query is not using the OLE DB software to its fullest

Registry Entrie
To enable the OLE DB Provider for Oracle to work with your Oracle client software, the client's registry must be modified by running a registry file from a command line. Multiple instances of the client software should not run concurrently. These files are listed in the following table and are located within the same directory structure that contains your Microsoft Data Access Component (MDAC) installation, which typically is in C:\Program Files\Common Files\System Files\OLE DB

Oracle client Windows NT or 9x Windows 2000
7.x mtxoci7x_winnt.reg mtxoci7x_win2k.reg
8.0 mtxoci80x_winnt.reg mtxoci80x_win2k.reg
8.1 mtxoci81x_winnt.reg mtxoci81x_win2k.reg

Only problem is there is no .reg for Oracle Client 9

Any suggestions?

You can see what is happening by looking at Profiler. You will see the
fetches performed.

In your OPENQUERY statement try to filter as much as you can there as well
so it is picknig up a subset of your data anyway.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Robert Thomas" <anonymous (AT) discussions (DOT) microsoft.com> wrote

also what is shown in query analyzer. I am using Oracle 9 client and I saw
this section in BOL which I think may be why my query is not using the OLE
DB software to its fullest.
from a command line. Multiple instances of the client software should not
run concurrently. These files are listed in the following table and are
located within the same directory structure that contains your Microsoft
Data Access Component (MDAC) installation, which typically is in C:\Program
Files\Common Files\System Files\OLE DB.