OPENQUERY on SQL Server 2005 64bit behaves weird

Question

I just recently moved a SQL DB from SQL2005 32 bit --> 64 bit. I am having an issue connecting to Oracle using the OraOLEDB.Oracle Provider.

I was able to install Oracle 10G Client , ODAC 64 bit. I was also able to add a linked server to the Oracle instance. I am able to run a query using the linked server name directly:

SELECT top 10 *
  FROM [DB0PBB0]..[DB0PBB0].[DM_CLICK]

So far it is good, however, the problem occurs when I try to use OPENQUERY. I tried the following:

select * from 
OPENQUERY(DB0PBB0,'select *  from DB0PBB0.DM_CLICK where Date_stamp <''24-Jul-09'' and Date_stamp >= ''23-Jul-09'' ')

SET FMTONLY OFF
select * from 
OPENQUERY(DB0PBB0,'select * from DB0PBB0.DM_CLICK where Date_stamp <''24-Jul-09'' and Date_stamp >= ''23-Jul-09'' ')

and I get only the column names, no rows :(

if I run this script:

SET FMTONLY OFF
select * from 
OPENQUERY(DB0PBB0,'select ''hello'' from dual ')

I get

hello

My question is, has anyone tried running OPENQUERY against Oracle from a SQL05 64bit ? Any ?Idea why would I get only columns back instead of data? I tried the same query on another server with the same link and it worked, it returned rows.

Answer 1

Hi Wamra,

since you are getting rows with the SELECT * FROM DUAL chances are that it is a query issue. Regarding your query one potential pitfall is that you are comparing what looks like a date column (DATE_STAMP) with a VARCHAR.

You should not rely on implicit conversions to compare dates. Instead you should use the appropriate explicit functions, for exemple:

select * from 
OPENQUERY(DB0PBB0,'select *  
                     from DB0PBB0.DM_CLICK 
                    where Date_stamp < to_date(''24-Jul-09'', ''dd-mon-rr'') 
                      and Date_stamp >= to_date(''23-Jul-09'', ''dd-mon-rr'')')