August 10, 2012 at 7:38 am
I have created a link to an Oracle instance, and the connection looks fine. Under the linked server, I can see some objects that exist on the Oracle database. Using Oracle Instant Client for 11g, ODBC and MSDASQL.
When I attempt to query a table on the Oracle database, I am getting this error:
Msg 7318, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "OraStud" returned an invalid column definition for table ""StudOwner"."STUDENT_DIRECTORY_INFO"".
I've seen a number of discussions (some suggesting Openquery) but none which have resolved this for me.
SQL Server 2008 R2 64-bit on Windows Server 2008, Oracle 11g (11.2.0.3)
Has someone dealt with this and know how to fix?
Thanks -
August 10, 2012 at 7:55 am
When I use Openquery, I get a similar but different error:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT TERM
FROM [OraStud]..[StudOwner].[STUDENT_DIRECTORY_INFO]
WHERE studid = '9170'". The OLE DB provider "MSDASQL" for linked server "OraStud" indicates that either the object has no columns or the current user does not have permissions on that object.
The user specified in the ODBC DSN has connect privileges (obviously) as well as select privileges on the STUDENT_DIRECTORY_INFO view.
August 10, 2012 at 8:16 am
August 24, 2012 at 10:02 am
I have installed the Oracle OLEDB provider and am now using that. Connection test successful, but when I run a query with openquery, I get this:
Msg 7399, Level 16, State 1, Line 11
The OLE DB provider "OraOLEDB.Oracle" for linked server "Ora_Stud" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 11
Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server "Ora_Stud".
When I run the query with four-part name and do not use openquery, I get this:
The OLE DB provider "OraOLEDB.Oracle" for linked server "Ora_Stud" does not contain the table ""StudOwner"."Student_Directory_Info"". The table either does not exist or the current user does not have permissions on that table.
The user has 1) connect privileges to the Oracle instance and 2) select privileges on this view. I can log on and query the view fine with the user's credentials.
I am wondering if the user needs some additional permissions on the Oracle side to be able to obtain metadata needed by SQL Server 2008 R2.
August 24, 2012 at 11:22 am
Strange you get doubledouble quotes on ""StudOwner"."Student_Directory_Info"".
Was the oracle table created in a case sensitive way?
Have you checked the common issues Like not enabling in process @ linked server provider in sqlserver.
August 24, 2012 at 12:25 pm
Allow In Process - I had checked that when working with MSDORA but forgot with OraOLEDB.
I checked it, restarted the instance, and now I'm getting data back using "Openquery!"
But not when I try to use the four-part naming convention (which seems more natural to me but I'm not going to quibble!).
Thanks!!
March 11, 2014 at 8:04 am
I am having the same issue, did you ever get this one figured out?
I get this error message:
The OLE DB provider "MSDASQL" for linked server "OraData" returned an invalid column definition for table ""TESTDTA"."F4101"".
I am running this simple query:
SELECT *
FROM OraData..TESTDTA.F4101
Have you found a solution to the four part naming?
March 11, 2014 at 8:47 am
Yes - take a look at "OPENQUERY"
OpenQuery is a function that takes two parameters - the database link name and the query against the remote database. It essentially creates a logical table from that data, and then you query that.
select myFirstColumn
,mySecondColumn
from OPENQUERY(LinkedServerName, 'Select shisher as myFirstColumn, shasher as mySecondColumn from myOracleTable')
where myFirstColumn = @myOnlyParameter
These are all nonsense names, of course. I use column aliases in the select statement which is the second parameter to OPENQUERY to demonstrate that it is a logical table being created, and you are then, outside OPENQUERY, selecting from that logical table.
Hope this helps. Please note that this is not exhaustive regarding Openquery. Maybe it has multiple signatures and can take other parameters, too. I don't know. I'm just showing how I use it and how it works for my purpose.
Of course, this rests on the linked server being set up correctly to allow you to get at that data.
HTH
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply