openquery WHERE clause syntax

Am attempting to download data from an oracle linked server.  Data is from a view not a physical table.  Using Openquery works but having problem with timeouts.  Is there another more efficient way to download a lot of data?  

OS-> Win2k   DB-> SQL Server  RemoteDb-> Oracle 9

SELECT * INTO L_Tic1 FROM OPENQUERY(ORADB, 'SELECT * FROM S_TIC1 ') WHERE  TICKET_NUMBER LIKE N'2003%' and CENTER = N'ABC'

This is taking to long....  Would their be any performance savings if the where clause was embedded within the openquery?  Attempts to test the differences have been thwarted b/c the column name is not recognized even though different naming qualifications were attempted.  What naming convention would work.. mind you that this is a view and no access to the original physical tables is available.  Have the schema name but no catalog name or user table owner for the oracle db.  

Glass
GlassAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
putting the where clause into the Query is the best solution

as the Owning Database can then perform its optimal extraction...

if you put it outside then SQL server is going to receive all the data and then
filter it...


have you tried

declare @OSQL nvarchar(4000)
set @OSQL = n'SELECT * FROM S_TIC1  WHERE  TICKET_NUMBER LIKE ''2003%'' and CENTER = ''ABC'''

  SELECT * INTO L_Tic1 FROM OPENQUERY(ORADB, @OSQL)

what column names does sql server choose for the l_tic1 table....

your oracle dba must be able to tell you the column names in the S_tic1 table and
similarly the general ddl etc must be available for the view....


 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GlassAuthor Commented:
LowFatSpread:  This pass through query has to execute on an Oracle database not another SQL Server so the proposed query fails.  

How should the query be written to execute on the Oracle database so that the colunmn names can be recognized?  Since the data can be downloaded using Access successfully, the column names are not in question; but the catalog and table owner are unknown.  What is know is the schema name which doesn't seem to be sufficient or my syntax is wrong.  

 
Glass
GlassAuthor Commented:
This is the error message generated......

Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'MSDAORA' reported an error.  
[OLE/DB provider returned message: ORA-01747: invalid user.table.column, table.column, or column specification

Glass
GlassAuthor Commented:
Solved the syntax problem:  


SELECT *  FROM OPENQUERY(ORADB,'Select * from S_TIC1 Where "TICKET_NUMBER" like ''2003%''  and "CENTER" = ''ABC'' ')


Note:   columns in double quotes and values enclosed with two single quotes before and after value.

LowFatSpread... Thanxs!  You made my mistake obvious....

Glass
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.