Proper syntax for TSQL openquery statement

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?
brooksmatoAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this will do better:
select * from openquery(dgtest,'select * from dgtable.house where upper(LAST_NAME) = ''SMITH'' ')
0
 
brooksmatoAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not double quotes ("), the single quotes ('')

looks like the same, but is not.
0
 
brooksmatoAuthor Commented:
Angellll-
You rock! Thanks a bunch.

Brooks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.