The OLE DB provider "OraOLEDB.Oracle" for linked server "ORA" reported an error

Article
07/19/2012

Question

_{Thursday, July 19, 2012 1:56 PM}

Hi,

I use Sql Server 2005 and create a Linked Servers to an Oracle Database 9i

With this Linked Server I can Query a Sql Server database and Oralce Database in the same query

We are facing a error one in a while and I would like to know how I can resolve this problem

In the provider Ora.Oracle I select the option Allow inprocess

To fix the problem very quickly I have to stop de Sql Server and restarted and the problem is fix

When we are in a developpement environment it is oK but in a production environmenet we can not stop and start a database server when we want .

This is the error message that I receive almost one ot two times a week

System.Data.SqlClient.SqlException was unhandled by user code
Class=16
ErrorCode=-2146232060
LineNumber=12
Message=The OLE DB provider "OraOLEDB.Oracle" for linked server "ORA" reported an error. The provider reported an unexpected catastrophic failure.
Cannot obtain the schema rowset "DBSCHEMA_INDEXES" for OLE DB provider "OraOLEDB.Oracle" for linked server "ORA". The provider supports the interface, but returns a failure code when it is used.
Number=7399
Procedure=up_Supplier_GetListLDI_BySearchValue
Server=EPDEV1
Source=.Net SqlClient Data Provider
State=1
StackTrace:
       at WebDigico.Supplier.GetSupplierList_BySearchValue(DBConnection dbConnection, String sParamSearchValue) in C:\developpement\DigicoWeb\WebDigico\WebDigico\Class\Supplier.vb:line 371
       at WebDigico.frmSupplierList.GetDataForGrid() in C:\developpement\DigicoWeb\WebDigico\WebDigico\frmSupplierList.aspx.vb:line 120
       at WebDigico.frmSupplierList.FillGrid(String sSort) in C:\developpement\DigicoWeb\WebDigico\WebDigico\frmSupplierList.aspx.vb:line 81
       at WebDigico.frmSupplierList.imgSearchField_Click(Object sender, ImageClickEventArgs e) in C:\developpement\DigicoWeb\WebDigico\WebDigico\frmSupplierList.aspx.vb:line 16
       at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e)
       at System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:

Thanks in advance :)

Gilles

GC99

All replies (6)

_{Tuesday, September 11, 2012 7:54 PM ✅Answered}

Hi,

This is to inform you how I resolve this issue

01- First I change my Query by using OPENQUERY instead of doing a normal Select (See the thread before to view an exemple)

(But I still have the same problem after)

02- I install the Oracle Provider for Oracle 9i version 9.2.0.7 from Oracle download web site and I instal all the component one by one

I think there are 5 or 6 component

03- Once I have done that I create my Linked server Object

04- After I call a store proc to test everything directely from sql Server

05- I had another problem of memory which was in relation with the temp file of Oracle database .

So I delete the Temp file and recreate a new one with the reuse check box activated.

Since than I never had any more problem

So I hope this can help you and resolve your problem if you face this type of problem

Have a nice day ! :)

GC99

_{Monday, July 23, 2012 8:42 AM}

Hi GC99,

According to your description, you can query a SQL Server database and Oracle database in the same query. So what action did you do then received this error?

Base on your error description, maybe you can try to turn on trace flag 7300 or use SQL Profiler to capture the “OLEDB Errors” event to retrieve extended OLEDE error information for further troubleshooting. For more details, please refer to this article: How to set up and troubleshoot a linked server to an Oracle database in SQL Server.

Best Regards,
Ray Chen

_{Monday, July 23, 2012 3:26 PM}

Hi,

Thanks for the reply

Concerning your question

" If I can quer a SQl Server database and Oracle database in the same query"

The answer is Yes

The query works well from the Sql Server and From the Web Interface .

The problem arrive once or two times a week

It happens when from a web page that call the Sql Procedure which query the Sql Server database and Oracle database in the same query. Like I said It works well for one week and for a reason once in a while we receive that error message

The next time the problem will happen I will use the Sql Profile and try to get more information about this error

Thanks Il will get back o this forum soon as I have more information qith the Sql Profiler

Have a nice day!

GC99

_{Thursday, August 16, 2012 1:57 PM}

Hi,

I finally get more information about this error

I use the DBCC command to get more information about the error with this command

DBCC TRACEON(3604, 7300)
go
SELECT * FROM OPENQUERY(ORA, 'Select * from DIGICO_SA.ProductList')
go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORA" returned message "揭㢨 揭웰擺㓬 揭笚粁揭㢨 ".
OLE DB provider "OraOLEDB.Oracle" for linked server "ORA" returned message "ROW-00001: Cannot allocate memory".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "Select * from DIGICO_SA.ProductList" against OLE DB provider "OraOLEDB.Oracle" for linked server "ORA".

It seems to be a memory problem

Are we talking about ROM memory

The problem is that it works for a certain time and after a while the ORA LinkedServer stop working

So is it the pool connection that takes memory?

For the connection we use the Using Command like this

Using dbConnection As New Web.CSQLConnection(Utils.GetConnectionString, True)

My code .....

End Using

When the End Using line is reach the connection is suppose to clean the connection

Also for the Ora linkedserver Connection we use the provider Oracel Provider For Ole DB

So is there a way to correct this issue

Is there something that I am doing wrong :)

Thanks in advance

GC99

_{Friday, August 17, 2012 3:02 AM}

Hi GC99,

Thanks for update.

Base on your description, maybe the memory on your production server is low. Using openquery might solve this issue, for more details, please refer to this similar thread.

Best Regards,
Ray Chen

_{Wednesday, August 22, 2012 2:47 PM}

Hi,

I try the OpenQuery solution but I still have the same problem

This is an exemple of a store proc that I modify to use the OpenQuery solution

tProduct is a Sql server Table

PRODUCTLIST is a view in Oracle that return dataset from oracle database

SELECT
  tProduct.ProductId AS 'PicklistEntryID',
  tProduct.NoProductSynergytek + ' - ' + OraProduct.ProductDescription as 'TxtID'
FROM
(
  SELECT * FROM OPENQUERY(ORA,'SELECT * FROM PRODUCTLIST')
) OraProduct

   INNER JOIN tProduct ON
   tProduct.NoProductSynergytek = OraProduct.NoProductSynergytek

WHERE
  tProduct.NoProductSynergytek Like '%' + @searchValue + '%' OR
  OraProduct.ProductDescription Like '%' + @searchValue + '%'
ORDER BY
  tProduct.NoProductSynergytek

It is ok to do that ?

Also I read that the version of the Oracle provider can fix this issue
Instead of using the oracle provider 9.2.0.4.01 we should use the 9.2.0.7 version
This is my next step to try to fix this issue

If you have any other idea I am open to try different things

Thanks in advance

GC99 :)

GC99

Share via

The OLE DB provider "OraOLEDB.Oracle" for linked server "ORA" reported an error

Question

All replies (6)

Additional resources