Solved

openquery WHERE clause syntax

Posted on 2003-11-04
4
4,118 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Glass
  • 3
4 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 9681326
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....


 

0
 

Author Comment

by:Glass
ID: 9681577
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
0
 

Author Comment

by:Glass
ID: 9681930
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
0
 

Author Comment

by:Glass
ID: 9681986
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question