Linked server error

Question

Linked server error

Aatish Patel

Ten Centuries

Points: 1040
More actions
November 25, 2013 at 11:18 am

#286581

I create a linked server to Oracle in SQL 2008. tested the connection and it tests successfully.
When I try to query
SELECT top 10 * FROM [linkedserver]..[abc].[persons]
GO
it fails with following error :
Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "W263UW" supplied invalid metadata for column "INACTIVE_TS".
The data type is not supported.
Pls help with this.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Aatish Patel (11/25/2013)
I create a linked server to Oracle in SQL 2008. tested the connection and it tests successfully.
When I try to query
SELECT top 10 * FROM [linkedserver]..[abc].[persons]
GO
it fails with following error :
Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "W263UW" supplied invalid metadata for column "INACTIVE_TS".
The data type is not supported.
Pls help with this.

in Oracle, what is the datatype for the column INACTIVE_TS in the [persons] table?

if it's a TIMESTAMP, look at this post:

http://webcache.googleusercontent.com/search?q=cache:6PcBw8RwF54J:drewstechnotes.blogspot.com/2010/05/easy-workaround-for-oracle-linked.html+&cd=1&hl=en&ct=clnk&gl=us

I've seen issues where an "old" drivers for Oracle version 9 is used agaisnt an Oracle version 10/11 database, and returns this kind of error for BLOB/CLOB columns;

the solution was to get a more updated Oracle driver installed on the SQL server to address the issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Aatish Patel Ten Centuries Points: 1040 More actions · Answer 2

Hi,

I really don't know what data types does Oracle has. I was just give the name and info to create Linked server which I did successfully.

So the person table is on Oracle side?

Lowell SSC Guru Points: 323518 More actions · Answer 3

yes it is on the Oracle side; because of the _TS (_TIMESTAMP?) i think that it's the timestamp datatype issue.

pretty sure you can also resolve this by convertint the value to string or a different datetime as well.

you could find out the datatype via TSQL:

untested, but i think this is the syntax

SELECT * FROM [linkedserver]..[abc].[USER_TAB_COLS] WHERE TABLE_NAME = 'PERSON' AND COLUMN_NAME = 'INACTIVE_TS'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Aatish Patel Ten Centuries Points: 1040 More actions · Answer 4

Aatish Patel

Ten Centuries

Points: 1040

November 25, 2013 at 1:21 pm

#1668957

it gives me the same error :