November 2, 2006 at 9:59 am
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.
November 3, 2006 at 7:50 am
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
November 3, 2006 at 8:02 am
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.
November 3, 2006 at 8:41 am
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
November 3, 2006 at 8:53 am
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.
November 3, 2006 at 10:34 am
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
November 6, 2006 at 9:28 am
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?
December 9, 2008 at 12:56 am
Don't use *. Use the column names instead.
December 10, 2008 at 7:06 am
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
December 10, 2008 at 8:05 am
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.
December 10, 2008 at 8:37 am
good info on this problem here...
http://forums.oracle.com/forums/thread.jspa?threadID=337842&start=0&tstart=0
Tim White
May 28, 2009 at 12:48 am
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply