SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invalid column definition for linked Oracle Server


Invalid column definition for linked Oracle Server

Author
Message
Michael Gerholdt
Michael Gerholdt
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 240
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 -
Michael Gerholdt
Michael Gerholdt
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 240
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.
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2733 Visits: 9894
Try the oracle drivers instead of microsoft driver for oracle
Michael Gerholdt
Michael Gerholdt
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 240
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.
Jo Pattyn
Jo Pattyn
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2733 Visits: 9894
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.
Michael Gerholdt
Michael Gerholdt
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 240
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!!
dongerard1
dongerard1
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 36
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?



Michael Gerholdt
Michael Gerholdt
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 240
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search