Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

openquery WHERE clause syntax

Posted on 2003-11-04
4
Medium Priority
?
4,774 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 1500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

886 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