Oracle linked server - TIMESTAMP incompatibility

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Oracle linked server - TIMESTAMP incompatibility

Author

Topic

andyhallam
Starting Member

9 Posts

Posted - 2011-01-26 : 06:47:40

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
--
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
--

I have successfully created a linked server to Oracle using the OraOLEDB.Oracle provider.

I can select from my oracle tables fine *EXCEPT* if the table in question has a column of type TIMESTAMP.

My test table schema on Oracle –

create table linkedserverdatatypetest (
ss_nvarchar NVARCHAR2 (10) NOT NULL,
ss_varchar VARCHAR2 (10) not null,
ss_char CHAR (10) NULL,
ss_nchar NCHAR (10) NULL,
ss_smallint NUMBER (5),
ss_integer NUMBER (10),
ss_money NUMBER (19, 4),
ss_float FLOAT (53));

Issue a select from SQL SERVER via my linked server -

select * from andyh..TRAINDB.LINKEDSERVERDATATYPETEST

SS_NVARCHAR SS_VARCHAR SS_CHAR SS_NCHAR SS_SMALLINT SS_INTEGER SS_MONEY SS_FLOAT
----------- ---------- ---------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- ----------------------

(0 row(s) affected)

So all OK.

Change the schema to –

drop table linkedserverdatatypetest;
/
create table linkedserverdatatypetest (
ss_nvarchar NVARCHAR2 (10) NOT NULL,
ss_varchar VARCHAR2 (10) not null,
ss_char CHAR (10) NULL,
ss_nchar NCHAR (10) NULL,
ss_smallint NUMBER (5),
ss_integer NUMBER (10),
ss_money NUMBER (19, 4),
ss_float FLOAT (53),
ss_datetime TIMESTAMP);

Issue the select from SQL SERVER via my linked server -

select * from andyh..TRAINDB.LINKEDSERVERDATATYPETEST

Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "andyh" supplied invalid metadata for column "SS_DATETIME". The data type is not supported.

I also get the error even if I omit the offending column –

select SS_NVARCHAR from andyh..TRAINDB.LINKEDSERVERDATATYPETEST

Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "andyh" supplied invalid metadata for column "SS_DATETIME". The data type is not supported.

With the fact that I get the error even when omitting the TIMESTAMP column from the select list suggests a more fundamental problem rather than some formatting issue.

Any ideas?

Thanks and regards,

Andy

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-01-26 : 10:25:24

Best I can advise is in the "select" clause to convert the ss_datetime column to some useable format - char/varchar.

To my knowledge, TIMESTAMP is not a timestamp per-se but an internal number/char which is unique per server - but i'm open to correction on this.

Also try to avoid SELECT * (in general) - it can cause unexplained (performance) problems.

andyhallam
Starting Member

9 Posts

Posted - 2011-01-26 : 16:07:34

quote:
Originally posted by AndrewMurphy

Best I can advise is in the "select" clause to convert the ss_datetime column to some useable format - char/varchar.

To my knowledge, TIMESTAMP is not a timestamp per-se but an internal number/char which is unique per server - but i'm open to correction on this.

Also try to avoid SELECT * (in general) - it can cause unexplained (performance) problems.

Encapsulating the timestamp column with CONVERT(...) is not a solution as, as I stated before, this cannot be a formatting problem as such. We can see this as 1 - it is a 'meta' error that is thrown, 2 - there is no data to return and 3 - I get the error even when not selecting the column.

BTW - Your advise on SELECT * and performance. I used * for convenience of use. RDBMS and performance are far from unexplainable. You just need to know what you are doing.

regards

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-26 : 17:25:28

Have you tried using OPENQUERY instead of the 4-part name syntax?

andyhallam
Starting Member

9 Posts

Posted - 2011-01-27 : 03:46:47

quote:
Originally posted by robvolk

Have you tried using OPENQUERY instead of the 4-part name syntax?

Yes, I did try OPENQUERY() in my initial tests and it worked, but to me this seems some what of a bodge and I wanted to avoid this if possible.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-27 : 07:40:37

Unless you can find another driver that doesn't cause this error, you might need to go that route. Written correctly, OPENQUERY can deliver much better performance than 4-part queries, especially if there are JOINs on the remote server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources