Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server error Expand / Collapse
Author
Message
Posted Monday, November 25, 2013 11:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:39 AM
Points: 24, Visits: 234
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.

Post #1517390
Posted Monday, November 25, 2013 11:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1517398
Posted Monday, November 25, 2013 12:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:39 AM
Points: 24, Visits: 234
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?
Post #1517416
Posted Monday, November 25, 2013 12:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1517428
Posted Monday, November 25, 2013 1:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 21, 2014 6:39 AM
Points: 24, Visits: 234
it gives me the same error :
Post #1517441
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse