Linked Server Oracle Unable to execute select QUERY

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Linked Server Oracle Unable to execute select...

Linked Server Oracle Unable to execute select QUERY

Rate Topic

Display Mode

Topic Options

Author

Message

funooni

Posted Tuesday, December 07, 2010 1:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 8:39 AM
Points: 198, Visits: 140

Gentlemen

I have browsed lots of threads after which i achieved my initial target, however, this is something i could not find out.

I have the following Setup:

1) SQL SERVER 2005 Developer Edition Running on Windows XP
2) Oracle Developer Toolkit and ODAC (ODTwithODAC112012.zip)
3) Linked Server Oracle is accessible with following details

EXEC master.dbo.sp_addlinkedserver @server = N'ORCNOTNS',
@srvproduct=N'OracleDSN',
@provider=N'OraOLEDB.Oracle',
@datasrc=N'ORCLDSN',
@provstr=N'Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER)(PORT=1522)))(CONNECT_DATA=(SID=ORACLE)(SERVER=DEDICATED)));User Id=USER;Password=PASSWORD;'

Allow Inprocess is unchecked.
Collation Compatible True
Data Access True
RPC True
RPC Out True
Use Remote Collation True
Collation Name <<BLANK>>
Provider used is OraOLEDB.Oracle

The connection is successfull as i checked it from right click server and then test connection

Moreover, following query works successfully:

insert into ORCNOTNS..SCHEMA.Table values ( 11,21,'abc','varchar',51,getdate(),'varchar','varchar','varchar',101,'C',getdate())

The values are inserted into the respective table in Oracle and i can query them from Oracle SQLPlus client. However, the getdate() datetime is truncated to date only.

My problem is that when i execute the following query it gives me an error:

SELECT * FROM ORCNOTNS..SCHEMA.Table

Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT "Tbl1002"."COLUMN_A" "Col1004" FROM "SCHEMA"."TABLE" "Tbl1002"" against OLE DB provider "OraOLEDB.Oracle" for linked server "ORCNOTNS".

However, this query does bring the column headers for this table.

Whereas when i execute the OPENQUERY using the following :

select * from openquery(ORCNOTNS,'SELECT * FROM SCHEME.TABLE')

This shows me the following error:

Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT * FROM SCHEMA.TABLE" against OLE DB provider "OraOLEDB.Oracle" for linked server "ORCNOTNS".

I have full control over my SQL Server 2005 i can make changes amendments etc. However, Oracle server is at client site and i don't have any privileges except Insert , Delete and select statements.

What bothers me more is that i can insert but i cannot select.

Please let me know if any other info is required.

Your help is highly appreciated.

Zee

Post #1031081

Geoff A

Posted Tuesday, December 07, 2010 7:16 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 485, Visits: 1,568

does this work?

select * from openquery([ORCNOTNS],'select * from all_objects')

Post #1031204

funooni

Posted Tuesday, December 07, 2010 8:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 8:39 AM
Points: 198, Visits: 140

No it gives the same error message

however i was just trying some commands and i found that the following works and returns all the tables.

exec sp_tables_ex @table_server = 'ORCNOTNS'

I dont understand what is wrong with select query.

Post #1031278

Geoff A

Posted Tuesday, December 07, 2010 8:57 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 485, Visits: 1,568

it may be related to the user defined in your linked server not having permissions.

Post #1031282

« Prev Topic | Next Topic »

Permissions