Problems with Linked Server to Oracle

Question

Post reply

Problems with Linked Server to Oracle

Rick Davis

Mr or Mrs. 500

Points: 572
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

MosbachWil

Old Hand

Points: 363

November 3, 2006 at 7:50 am

#669788

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 Mr or Mrs. 500 Points: 572 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

MosbachWil

Old Hand

Points: 363

November 3, 2006 at 8:41 am

#669811

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 Mr or Mrs. 500 Points: 572 More actions · Answer 4

Rick Davis

Mr or Mrs. 500

Points: 572

November 3, 2006 at 8:53 am

#669816

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 Mr or Mrs. 500 Points: 572 More actions · Answer 6

Rick Davis

Mr or Mrs. 500

Points: 572

November 6, 2006 at 9:28 am

#670106

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: 4481 More actions · Answer 8

2 Tim 3:16

SSCarpal Tunnel

Points: 4481

December 10, 2008 at 7:06 am

#910318

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