Solved

Openquery and where clause

Posted on 2003-11-03
3
985 Views
Last Modified: 2007-12-19
Would like help to query a view for data using openquery on a machine running Win2k server and SQL server 2000.  The remote machine is running Oracle 816.  

My general download query works:

select * from openquery(LinkServer, 'select * from tbl_name')

Would like to add a where clause->    where col_name like "2003%"  but erroring out with doesn't recognize column.  Have tried various combinations of naming but to no avail.  

Oracle DB
Schema -> Enterprise
table owner for remote db -> unknown
userid for remotedb -> user_id
table name -> tbl_name
column name -> col_name

Thanxs,
Glass


0
Comment
Question by:Glass
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 3

Accepted Solution

by:
htarlow earned 250 total points
ID: 9675053
is your where condition within the openquery or after it?

Placing the condition within the query causes the filtering to be performed on the remote server.
If you place the condition after the openquery, all data is returned and then filtering is done by sqlserver.
0
 

Author Comment

by:Glass
ID: 9675088
I have tried both ways.  If within, the query returns the column is unknown.  If outside, then the query fails to return and timesouts.

Glass
0
 

Author Comment

by:Glass
ID: 9675183
htarlow,

You pointed me in the right direction...

Thanxs,
Glass
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

738 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