.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
ASPEvil
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Post New Web Links

Trouble with: Linked Server to Oracle using OraOLEDB.ORacle Provider

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server
 

Hi--

 

I am running SQL Server 2005 on Win2k3:

Microsoft SQL Server Management Studio      9.00.2047.00
Microsoft Analysis Services Client Tools      2005.090.2047.00
Microsoft Data Access Components (MDAC)      2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML      2.6 3.0 4.0 6.0
Microsoft Internet Explorer      6.0.3790.1830
Microsoft .NET Framework      2.0.50727.42
Operating System      5.2.3790


I have the OraOLEDB.Oracle provider installed to the (C:\oraclexe) directory.

I am having problems querying from linked oracle server.  When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password.   So it at least knows that.  when i set the correct password and run a query I get this error:

(i replaced the real server name with "someServer".)

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

C


View Complete Post


More Related Resource Links

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server

  

Dear All,

please to try to help me what could be the problem ?. when  i try to excute my stored proc in my sqlserver management studio it excuting fine when i trying to Excute using Asp.net application it giving Error. iam working on Sql Server, mt linked server is Oracle. my proc is like :

select  ID,Name  from OPENQUERY(TEST, ''
SELECT
 ID ,
 Name  from Details)

Error:Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TEST".
OLE DB provider "OraOLEDB.Oracle" for linked server "TEST" returned message "ORA-01017: invalid username/password; logon denied".
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.


Please try to guide me i unable run using asp.net App


thanking you,


santhosh


Linked server config shows no Oracle provider

  

Hi,

In a process to config linked server on MSSQLServer2008 on WS2008R2.

Installed Oracle client 11.2, configured the variables TNS_ADMIN, ORACLE_HOME;

configured Data Sources (ODBC)- System DNS ->Oracle with Driver SQORA32.dll and have all Oracle databases in the drop down menu.

Next:

New linked server - Under provider -> there is no Oracle provider listed!?

What I'm missing here?

 

Thx,


Getting OraOLEDB.Oracle to Show up in List of Providers for Linked Server

  

In 64-bit SQL Server 2008 R2, I am trying to create a linked server to an Oracle 11g database.  I have installed the 32-bit Oracle 11g Instant Client and set up tsnames.ora and sqlnet.ora in proper Oracle home directories. 

In SQL Server management studio, under Server Objects - Linked Servers - Providers, I do not see the OraOLEDB.Oracle in the list.  I've rebooted and re-installed the Instant Client.  I stopped and started the SQL Server service.  I even confirmed that OraOLEDB.Oracle exists as one of the SQL Server providers in the registry.

There are full permissions on the directory where the Oracle Instant Client is installed.

Does anyone have any recommendations about how to get the OraOLEDB.Oracle to show up in the list of providers?  I'm not sure why it would show up in the Registry but not in SQL Server Management Studio.

Thanks,

William H

 

 


Wmholt SharePoint Developer

No Way to retrieve data from oracle ref:_cursor over T-SQL and linked Server?

  
Hello, for an migration projekt we want to compare results from sp's from oracle and sqlsserver. Same calls should retrieve same results. Also we want build an automatic test for this. But is there now way to retrieve results from oracle sp's whit rev_cursor over linked Server? No one answer to this: http://social.msdn.microsoft.com/forums/en-us/sqldataaccess/thread/2BAC6743-8701-4476-8F36-0377A5761525   greetings Michael

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

  
Hi,   I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :   OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error". OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.   Please let me know.   Thanks

Oracle 11g R2 64-bit linked server difficulty

  
We are re-integrating a business unit back into IT support this weekend. They have a Oracle production system that's been upgraded to 11G. The previous linked server worked fine. I'm trying to attach from an XP laptop that has a full blown Sql Server 2005 running. I've seen all the post about "in-process" and it doesn't work. I'm getting either 7303 or 7399 regardless of whether I use the Oracle or Microsoft driver. I can successfully connect and query the database from sql plus with no problem. I can create the linked server fine, it's just when I go to query that it errors. "The test connection to the linked server failed." An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS". OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303 Could this be a problem with a 64-bit server connecting from a 32 bit client?? Thanks in advance..

Oracle 11g R2 64-bit linked server difficulty

  

We are re-integrating a business unit back into IT support this weekend. They have a Oracle production system that's been upgraded to 11G. The previous linked server worked fine.

I'm trying to attach from an XP laptop that has a full blown Sql Server 2005 running. I've seen all the post about "in-process" and it doesn't work. I'm getting either 7303 or 7399 regardless of whether I use the Oracle or Microsoft driver. I can successfully connect and query the database from sql plus with no problem. I can create the linked server fine, it's just when I go to query that it errors.

"The test connection to the linked server failed."

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS".
OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303

Could this be a problem with a 64-bit server connecting from a 32 bit client??

Thanks in advance..


SSAS 2008R2 - Enable to process a database using Oracle OraOLEDB data provider

  

Hi,

I've created a data source in BIDS using provider OraOLEDB.Oracle.1. The Oracle database is 10gR2. The client installed on my computer is 10gR2. The connexion is OK when I use Test Connexion in BIDS.

In the data source view I created a very simple query: select * from user_objects. All is fine, the fields are correctly defined.

I've created a dimension based on this query with object_id as the key.

I've deployed the solution to the server. All is working fine for the moment.

But when I process the dimension I get a dozen of error messages like (sorry error messages are in french) :

"Erreur OLE DB : Erreur OLE DB ou ODBC : Syntax error near 'OraOLEDB.Oracle.1' on line 8; 42000."

Erreurs dans le moteur de stockage OLAP : Une erreur s'est produite lors du traitement de dimension portant l'ID 'Test' et le nom 'Test'.

Erreurs dans le moteur de stockage OLAP : Une erreur s'est produite lors du traitement de l'attribut 'SUBOBJECT NAME' de la dimension 'Test' de la base de données 'CTRL_DOMAINES'.

Serveur : L'opération a été annulée.

Erreur OLE DB : Erreur OLE DB ou ODBC : Syntax error near 'OraOLEDB.O

Not able to see microsoft oledb provider for oracle in sql server 2005 providers

  

Hi,

can any one help me, environment window server 2003  64 bit and sql server 2005 sp3 64 bit and i have installed oracle client  10g 32 bit. problem is, I am not able to see the provider name  while creating linked server.


Another Oracle linked server problem

  

I have trouble making a linked server from a SQL Server 2008 R2 64bit to an Oracle database.  I have a linked servers on a few other PCs working fine to this same Oracle server.

On this particular one, I configured the following and Command Test connection shows that it is working fine:

TNSNames

But when I tried to create the linked server as shown below, it fails.

The error is: 

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "CARE".
OLE DB provider "OraOLEDB.Oracle" for linked server "CARE" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)

I can't find what other parameters I could adjust.  How should I go about solving this problem.  Thanks.


Clustered SQL 2008 R2 x64, Oracle linked server works on one node, fails on the other

  

Juicy bits:

- SQL Server 2008 R2 x64

- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) 

- Active/passive cluster

- Oracle client 11gR1

- "Allow Inprocess" provider option is enabled

 

I'm stumped.  Our development, QA and UAT environments are all running the same versions of Windows, SQL, and Oracle client.  Linked server between SQL and Oracle works just fine.

 

Production is clustered.  The linked server works just fine on one node of the cluster, but if we failover to the other node, attempts to use the linked server in any way, OPENQUERY, GUI, etc..., cause the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" returned message "ORA-12634: Memory allocation failed".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE".

 

What am I missing?  I've reinstalled the Oracle client on the defective node, verified that the SQL Server service account has full rights to the Oracle client dir

OLE DB provider : The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

  

Hi,

Getting following error while trying to connect to ORACEL database using OLEDB manager.

Please advice.

ERROR Log :

TITLE: Microsoft Visual Studio
------------------------------

Error at RADAR [Connection manager "RADAR_Updated"]: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider OraOLEDB.Oracle.1 is not registered. Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

Error at DFT LOAD AGGTYPE TABLE [RADAR AGG TYPE [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "RADAR_Updated" failed with error code 0xC0209302.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

 


Dev

Linked server desde SQLServer 2008R2 (64x) hacia Oracle 9i

  

Hola

Tengo el siguiente problema para crear un servidor vinculado, espero alguien me pueda ayudar

Tengo un servidor con SQL Server 2008 R2 64x y requiero crear un servidor vinculado hacia una base de datos de Oracle 9i, cuando intento crear el servidor no encuentro el provider Microsoft OLE DB Provider for Oracle, para realizar la conexión como se hace en SQL 2005.

¿Alguien sabe que pasos debo realizar para poder crear el servidor vinculado?


Microsoft OLE DB Provider for Oracle 64-Bit, SQL Server 2008: Does it exist?

  

 

Does anyone know if the "Microsoft OLE DB Provider for Oracle" in 64-Bit Windows exists in SQL Server 2008 64-bit?  It only existed in 32-bit SQL 2005,  which was a big problem.    Needed for Linked Server creation.  We do not want to use OPENQUERY calls with the native Oracle 10G driver,  too many coding changes.

 

Thank You


OraOLEDB.Oracle provider is not registered

  

I'm doing some local testing on a Windows 7 64-bit PC. I'm running Oracle 11g 64-bit locally. I'm trying to set up a connection to the Oracle database from a local ASP.NET application. When trying to make a connection to the database I get the error “The 'OraOLEDB.Oracle' provider is not registered on the local machine” (the application is blowing up at the line connection.Open() where I'm trying to connect using a connection string).

 I added IIS_IUSRS to the Oracle Home directory and granted read, write, and execute permissions. I also successfully registered the OraOLEDB11.dll file through the command line. Those are a couple of fixes that I've found so far but my problem still persists.

 I can connect to the Oracle database through SQL PLUS as well as SQL Developer. I've tried rebooting after making the changes that I've made so far. I've tried stopping and starting the database instance as well.

 Does anybody have any other ideas that I can try?

 Thanks for any help!

SQL 2005 Linked Server to Oracle 10g - Very Slow

  

Hi,

 

Anyone have some recommendations for a slow linked server from SQL 2005 to Oracle 10g?

 

Here is the situation.  We have an application that runs on SQL 2005 - Win 2003, and in certain forms it makes calls to an Oracle 10g database running on Sun Unix.  This database was upgraded from SQL 2000 Win 2000.  We have a test system mirroring production and have the same problems.

 

- All indexes are being used.

- Server configuration is correct.

- Changed the Oracle 10g driver from the main driver to the Instant Client - much smaller but same result.

- Read an old post about same issue but the person was connecting to 8i so they were using a different driver.

- I have been running profiler and the problem seems to be a remote scan on the Oracle side.  We are using the OPENQUERY call from the application.

- All other parts of the app are running fine, just the outside connection.

- We are using the Windows Firewall and all DCOM components are set as Microsoft recommends.  I don't think this is an issue because the test server is not using Windows Firewall and we have the same issue on that server.

 

Any suggestions would be greatly appreciated.  I have run into the wall :-)

 

thanks

Matt


Linked Server using Oracle OLEDB 64-bits Win 2008 R2

  

Hi,

I have 2 boxes running SQL Server 2008 and using linked Oracle OLEDB that behaves different for same query.

On 1st machine, using win 2003 , SQL Server 2008 SP1 on a 64-bit server I  I created a linked server to Oracle database using Oracle OLEDB provider. When I submit query to a Oracle database using Oracle OLEBD using WHERE clause, this WHERE clause is sent to Oracle, then it uses an index to resolve query and send result already filttered to SQL Server.

I have a 2nd box running Windows 2008 R2, SQL 2008 SP2 on a 64-bit server and queries are working fine, but when I filter some column of a linked server table using WHERE clause, SQL Server sends to Oracle SELECT without where clause, Oracle performs a table scan on that table and then sends results to SQL Server, that applies filter to the result. 

The problem is that some Oracle tables are huge and sending a SELECT without where clause causes table scans on Oracle, what should be avoided.

I already tried to change OLEDB properties and also tried to change linked server properties, but none of them solved the problem.

Does anyone knows what I should change on 2nd server to make it works like 1st server?


Se a resposta resolveu sua questão ou problema, classifique-a para manter a qualidade do forum e a confiabilidade dos p
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend