Problems with Linked Server to Oracle

Question

Problems with Linked Server to Oracle

Rick Davis

SSChasing Mays

Points: 632
More actions
November 2, 2006 at 9:59 am

#367970

I have a SQL Server 2005 (SP1) with mdac 2.8 (SP2). I am able to connet to the oracle system using SQL Plus on sql server machine. When every I try to query a table on the system I get the following error message.
Msg 7318, Level 16, State 1, Line1
The OLE DB provider "MSDAORA" for linked server "" returned an invalid column definition for table "".

I would appreciate any help with this issue.

Viewing 12 posts - 1 through 11 (of 11 total)

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

MosbachWil Old Hand Points: 363 More actions · Answer 1

Hi Rick,

I run in the same problem with SSIS reading number fields (without any dimensions in the oracle-table), I think the problem is the NUMBER definition with no dimension,

I made some views in oracle for SSIS, then it works

wilmos

Rick Davis SSChasing Mays Points: 632 More actions · Answer 2

I have seen that problem with number fields as well and like you, I normally handle that with a view. But what I am seeing is on this on is on a string (varchar2) field. Here is the query that I using.

select

LAST_NAME FROM PROD..APPS.PER_ALL_PEOPLE_F WHERE LAST_NAME = 'Davis'

Where PROD is the name of the linked server that I am using.

MosbachWil Old Hand Points: 363 More actions · Answer 3

have you tried with openquery, something like this:

select * from openquery (POD,'select LAST_NAME FROM PER_ALL_PEOPLE_F WHERE LAST_NAME = ''Davis''')

wilmos

Rick Davis SSChasing Mays Points: 632 More actions · Answer 4

Using the openquery function worked. I can use that as a work around until I can get the standard linked server issue worked out.

Thanks for your help.

MosbachWil Old Hand Points: 363 More actions · Answer 5

Rick,

I have a little prc for this, you can direkt query to linked server

--------------------------

CREATE PROCEDURE [dbo].[syp_DOLINKQUERY]

@QER varchar(2000)

AS

-- query a linked table

-- wm 01.2005

declare @QER1 varchar(2000)

declare @LINK varchar(100)

declare @XUSER varchar(100)

DECLARE @nsql nvarchar(4000)

set @XUSER = user

set @LINK = 'PROD'

set @QER1 = REPLACE(@QER,char(39),char(39)+char(39))

set @QER = 'select * from openquery (' + @LINK + ',' + char(39) + @QER1 + char(39)+ ') '

SET @nsql = CAST( @QER AS nvarchar(4000) )

EXEC sp_executesql @nsql

return

------

maybe ist helps, I have the same for inserts and updates

let me know

wilmos

Rick Davis SSChasing Mays Points: 632 More actions · Answer 6

Thanks for all your help with this issue, and I will be using the OPENQUERY workaround. I am still curious of why the query (Select * from PROD..APPS.PER_ALL_PEOPLE_F) is not working. Does anyone have any idea of what I should check?

bluepaperbags SSCommitted Points: 1738 More actions · Answer 7

bluepaperbags

SSCommitted

Points: 1738

December 9, 2008 at 12:56 am

#909491

Don't use *. Use the column names instead.

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 8

I had a similar problem. I was using the "oracle provider for OLE DB" driver. I switched the drivers to use the "Microsoft OLE DB provider for Oracle" and that worked.

Tim White

jgrubb SSCrazy Points: 2542 More actions · Answer 9

I'd be extremely wary of using MSDAORA in production. It's old, and written against Oracle 7

http://support.microsoft.com/kb/244661/"> http://support.microsoft.com/kb/244661/

It doesn't support all the datatypes, and I can't find it now, but I saw on a msdn article it wasn't supported any longer in production.

Oracle has a free one, and there are other options.

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 10

good info on this problem here...

http://forums.oracle.com/forums/thread.jspa?threadID=337842&start=0&tstart=0

Tim White

l_fname Newbie Points: 9 More actions · Answer 11

You need to add schema name befor table name,also you must enter schema name and table name in capital letter.

select * from linkedserver..SCHEMANAME.TABLENAME

It's working fine with me.