Solved

openquery WHERE clause syntax

Posted on 2003-11-04
4
3,936 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

Title # Comments Views Activity
Calculated columns 13 62
Sql Count with Select Distinct 4 27
Update data using formula 22 21
MSDN Licensing query 5 46
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now