Oracle 11g Linked Server OLE DB Data Type Error

Question

Oracle 11g Linked Server OLE DB Data Type Error

chris.thornburg

SSC-Addicted

Points: 477
More actions
December 7, 2010 at 9:51 am

#232161

Hello....
Okay so my company has just upgraded its accounting package to Oracle 11g...and we have custom code running that needs to query the Oracle DB from SQL server 2005 via a linked server...well this used to work but now I am getting an error when I try to query the data:
The OLE DB provider "MSDAORA" for linked server "PENTA" supplied invalid metadata for column "UDF_XML_DATA". The data type is not supported.
PENTA is my Linked server and the column mentioned in the error is not included in my query.
I know that I can use the OPENQUERY function but it really not very friendly for what I need.
Is there a solution to the linked server error that I can use so I can get back to using native 4-part queries in my code?
-chris

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i seem to remember that the msdbora driver was valid only up to oracle version 9; with 10 and above, the blob type fields cause issues.

if your select statement did not have any BLB or CLOB fields, it'll still work with that driver, but you get that metadata issue, or otehr errors, whent hey are included.

i'm aweful sure you have to install the oracle driver and use the ORAOLEDB.Oracle provider instead.

--#################################################################################################

--Linked server Syntax for Oracle 10G/11

--#################################################################################################

DECLARE @server sysname,

@srvproduct nvarchar(256),

@provider nvarchar(256),

@datasrc nvarchar(100),

@location nvarchar(100),

@provstr nvarchar(100),

@catalog sysname,

@sql varchar(1000)

--add an access Database as a linked server

SET @server = N'MyOracle' --this is your ALias

SET @srvproduct = N'Oracle'

SET @provider = N'ORAOLEDB.Oracle' --optionally 'MSDAORA' to use the MS driver

SET @datasrc = N'SFMN10G' --this is the SID

set @provstr = ''

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

-- exec sp_dropserver AccessDb

exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle',

@useself = N'FALSE',

@locallogin = 'sa',

@rmtuser = N'CHANGE_ME',--oracle username

@rmtpassword = 'NotARealPassword' --oracle password

--list all the tables and their names

EXEC sp_tables_ex 'MyOracle'

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!

chris.thornburg SSC-Addicted Points: 477 More actions · Answer 2

Hey thanks for the quick response 🙂

Okay so I downloaded and installed the Oracle 11 drivers on my local computer...and deleted my linked server and added a new one, and chose the Oracle Provider for OLE DB, and I still get the same error...but I did not re-boot my machine.

So I am going to try it again.

chris.thornburg SSC-Addicted Points: 477 More actions · Answer 3

Okay in your scipt when you say ALias do you mean my TNSNAMES server alias or the name of my Linked server?

Lowell SSC Guru Points: 323518 More actions · Answer 4

chris.thornburg (12/7/2010)
Okay in your scipt when you say ALias do you mean my TNSNAMES server alias or the name of my Linked server?

The Alais is just what the linked server will be named in SQL; it can be completely disconnected from the stuff in TNSnames; most people make the alias to be the same name as the Oracle Instance, but it's nice to know it's optional.

this line:

SET @datasrc = N'SFMN10G' --this is the SID

is the name of the service you might have in your tns names; mine for this specific looks like this

####################################
# TNSNAMES.ORA Configuration File:C:\Oracle\product\10.1.0\Client_1\NETWORK\ADMINtnsnames.ora
# Generated by Oracle Enterprise Manager V2
# Date..........: Wed Apr 21 11:52:28 EDT 2004
####################################
sfmn10g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DBOra10g)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sfmn10g)
)
)

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!

chris.thornburg SSC-Addicted Points: 477 More actions · Answer 5

Okay I got it...mine is a little different...but I think it will be fine.

So I just un-installed my Oracle drivers and I am in the process of re-installing the new ODAC drivers.

Then I will re-boot, and then try to setup the linked server again.

🙂

Thanks for your help...this is really creating a huge headache for me...

chris.thornburg SSC-Addicted Points: 477 More actions · Answer 6

Now I have screwed up my computer and I can't connect to Oracle at all.

crap...