Great article, this was exactly what I needed.

But, having made the connection work, I found a new problem:

I can query an Oracle table, but if I want to use SELECT * FROM table, it's not always working.
As soon as the number of fields exceeds eight (8), the query times out.
If I use the same query, but with one field less in the SELECT statement, is gives me back the result in a second.
It doesn't matter which of the field I put in or leave out.

Example:
This works (8 fields):
SELECT MEMBER_ID, GIVEN_NAME, FAMILY_NAME, NAME_PREFIX, EMAIL, STREET, TOWN, POSTCODE
FROM dbora..USERNAME.MEMBER_DATA

This doesn't (9 fields):
SELECT MEMBER_ID, GIVEN_NAME, FAMILY_NAME, NAME_PREFIX, EMAIL, STREET, TOWN, POSTCODE, COUNTRY
FROM dbora..USERNAME.MEMBER_DATA

Obviously, SELECT * FROM dbora..USERNAME.MEMBER_DATA doesn't work either, since it has more fields than 8.

I get the same results using OPENQUERY.

In the table are currently only 11 records, so it assume it's not the size of the table.

I'm using SQL Server 2000 on Windows Server 2003, linking to an Oracle 9 database.


Does anyone have any idea why this occurs?

Thanks,
Patrick

Patrick11/17/2005 9:26:12 AM

Hi,

I have setup a linked server that intermittently crashes.

It connects to an Oracle Database on an alternate server. My tnsnames.ora file is correct and the Linked server does periodically work....

I am not sure what triggers it but it crashes and gives the following error:

Error 7399: OLE DB provider 'MSDAORA' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

Can anyone help with this please ?

Which is the correct configuration in terms of drivers to use and vendor. Are there any registry settings that need changing?

Thanks,

Simon.


Anonymous User 9/2/2005 5:29:33 AM

Thanks article great. Also last user that posted set "Allow InProcess", worked prefect after recreating thru Enterprise with this option.
Thanks

Anonymous User 8/10/2005 7:22:36 PM

Another comment about last post:
i deleted the linked server and add it from begining but this time i did this:

Method 2 Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box.


Anonymous User 6/21/2005 2:22:11 AM

Answer for the last comment.
I had the same prob.This one solved it:

Message 3
Error 7302: Could not create an instance of OLE DB provider 'MSDAORA'
Make sure that the MSDAORA.dll file is registered correctly. (The MSDAORA.dll file is the Microsoft OLE DB provider for Oracle file.) Use RegSvr32.exe to register Microsoft OLE DB Provider for Oracle. If the registration fails, reinstall Microsoft Data Access Components (MDAC). For more information about MDAC, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/data/Default.aspx
Note If you use a third-party Oracle provider, and your Oracle provider cannot run outside a SQL Server process, enable it to run in-process by changing the provider options. To change the provider options, use one of the following methods. • Method 1
Locate the following registry key. Then, change the value of the AllowInProcess (DWORD) entry to 1. This registry key is located under the corresponding provider name:
HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\MSSQLServer\\Providers\\ProviderName
• Method 2
Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box.



Anonymous User 6/21/2005 2:22:00 AM

- SQL Server 2000 & Oracle 10g
- Oracle10g Provider for OLE DB Version 10.1.0.4.0

Able to create linked server and view all tables. However, query give
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute returned 0x80040155].

Query is select count(DEPT_CODE) from ORADB..ORADBA.DEPARTMENTS

Have changed the PATH. But still the same error.

What other config did I miss out ? Pls help... Txs.


Anonymous User 6/12/2005 7:29:57 PM

I have: Oracle 8i and SQL Server 2000.
I want to create a link server with a Oracle Database.

First: I try with the OLE DB Provider of Oracle. I think that all the necesary data is ok, because I can see all the tables of the Oracle Database in my SQL Server and I can create views of the tables, but when I do a select of a table with more of 100 register I obtain an error of the controler like:

Servidor: mensaje 7399, nivel 16, estado 1, línea 1
El proveedor OLE DB 'OraOLEDB.Oracle' informa de un error. El proveedor no proporcionó información acerca del error.

Second: I try with the OLEDB Provider of Microsoft and I can to create the linked server, but whe I try to create a views of any table of the Oracle Data Base I obtain an error like:

Servidor: mensaje 7399, nivel 16, estado 1, línea 1
El proveedor OLE DB 'MSDAORA' informa de un error. El proveedor no proporcionó información acerca del error.

I execute the register script for my windows 2000 correctly but I obtain this error too.

What is the problem with the two OLE DB Provider?
Thanks

Miguel Angel3/30/2005 4:16:45 AM

Very nice article. I established successfully connection to Oracle but I am unable to execute Stored Procedure. Any clue why?

Anonymous User 3/4/2005 12:27:53 PM

Anonymous User above,
Any query over a network with joins will take ages. If you join two tables, the number of "reads" is very roughly equal to the total number of rows multiplied together I think. Hence all these reads become network reads so it takes ages. Try creating a view in oracle and querying that instead - that should be quicker. Also if you want to get technical check the join order of the query plan.

Anyway my comments - article is good, however I still can't get it to work. Having changed the path variable does anyone know if I have to restart the SQL server?
Cheers
Will

Anonymous User 1/26/2005 8:56:46 AM

Hi :
I am retriving data from a oracle database using sql server linked server.

I found that when I run the query in sql plus (oracle) the query executes within seconds but when I am executing the same query using sql server query analyzer it is taking more than 10
min. What could be the problem.

my query involves inner join on 4 tables with millions of data and distinct and where clause. I get 6 rows with the query I am using.



Anonymous User 1/6/2005 4:22:32 PM