Solved

Proper syntax for TSQL openquery statement

Posted on 2007-11-14
4
1,178 Views
Last Modified: 2012-08-13
From SQL Server 2005, I am able to query a linked Oracle 10g database with this:
select * from openquery(dgo1,'select * from DGOUSER.HOUSEHOLD')

I'm trying to add a where clause
select * from openquery(dgtest,'select * from dgtable.house where upper(LAST_NAME) = 'SMITH' ')

The error I get is:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'SMITH'.

What do I have to correct to get the query to work?
0
Comment
Question by:brooksmato
[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
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20283339
this will do better:
select * from openquery(dgtest,'select * from dgtable.house where upper(LAST_NAME) = ''SMITH'' ')
0
 

Author Comment

by:brooksmato
ID: 20283418
angellll, running your query:
select * from openquery(dgtest,'select * from dgtable.house where upper(LAST_NAME) = ''SMITH'' ')

Gives me this error now.

OLE DB provider "MSDAORA" for linked server "dgtest" returned message "ORA-00904: "SMITH": invalid identifier
".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from dgtable.house where upper(LAST_NAME) = "SMITH" " for execution against OLE DB provider "MSDAORA" for linked server "dgtest".
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20283439
not double quotes ("), the single quotes ('')

looks like the same, but is not.
0
 

Author Comment

by:brooksmato
ID: 20283509
Angellll-
You rock! Thanks a bunch.

Brooks
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

735 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