Solved

openquery WHERE clause syntax

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

758 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

22 Experts available now in Live!

Get 1:1 Help Now