Cannot Execute Select against Linked Oracle Server

• funooni

  Ten Centuries

  Points: 1314

  More actions

  December 7, 2010 at 12:32 am

  #149508

  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

• Ondrej

  Old Hand

  Points: 345

  More actions

  June 5, 2012 at 2:08 am

  #1496930

  Hi Zee/All

  I faced exactly same problem, probably solution has been discovered. It looks like that is necessary to tick Allow inprocess in options of OraOLEDB.Oracle provider in providers list.

  I hope it helps.

  Ondrej