Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Invalid column definition for linked Oracle Server Expand / Collapse
Author
Message
Posted Friday, August 10, 2012 7:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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 -
Post #1343393
Posted Friday, August 10, 2012 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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.
Post #1343406
Posted Friday, August 10, 2012 8:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,390, Visits: 6,346
Try the oracle drivers instead of microsoft driver for oracle
Post #1343427
Posted Friday, August 24, 2012 10:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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.
Post #1349787
Posted Friday, August 24, 2012 11:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,390, Visits: 6,346
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.
Post #1349833
Posted Friday, August 24, 2012 12:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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!!
Post #1349868
Posted Tuesday, March 11, 2014 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 8:50 PM
Points: 6, Visits: 34
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?



Post #1549766
Posted Tuesday, March 11, 2014 8:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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
Post #1549792
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse