Handling Dates from Oracle to SQL Server 2005 using OPENQUERY

Question

I have linked SQL Server 2005 to an Oracle Server, and am using OpenQuery() to pass data from Oracle to SQL Server.

So, for instance, the following query works fine:

SELECT *
FROM OPENQUERY(ORACLE_LINK, 'SELECT FIELD1
    FROM ORACLE.TABLE1');

Everything works, unless I ask for a field which is a date/time in the Oracle database. For instance, say that TABLE1 has ten fields, one of which is a timestamp. If I ask for all nine fields except for the timestamp, it works. If I ask:

SELECT *
FROM OPENQUERY(ORACLE_LINK, 'SELECT *
    FROM ORACLE.TABLE1');

I get the following error:

OLE DB provider "MSDAORA" for linked server "ORACLE_LINK" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
OLE DB provider "MSDAORA" for linked server "ORACLE_LINK" returned message "Data type is not supported.".

I also get the same error if I ask for only the timestamp field.

What is the best way of handling this error? Thanks.

Dan Bracuk · Accepted Answer · 2013-08-26 22:48:17Z

2

I do it with this function (partial code):

select @StringOut = '{ts ''''' + convert(varchar(20), @DateIn, 20) + ''''' }';
RETURN @StringOut

and this goes into the query:

and procedure_date_dt = ' + dbo.TimestampString(@date) + '
and event_type = ''''Time Notation''''

and it works when I run it.

answered Aug 26, 2013 at 22:48

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1893148 Over a year ago

Thanks. Are you able to elaborate on how to use this partial code with the code example in my question?

Dan Bracuk Over a year ago

Oh, you are having trouble in the select clause. What happens when you run "select * from openquery (ORACLE_SISI, 'select sysdate from dual');", but with your server name?

user1893148 Over a year ago

I get the now-time, successfully.

Dan Bracuk Over a year ago

What is the datatype of the problematic field?

user1893148 Over a year ago

It is a timestamp. I've solved the problem by casting the timestamp as a date in the query string. Thanks for your help!

Collectives™ on Stack Overflow

Handling Dates from Oracle to SQL Server 2005 using OPENQUERY

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related