OraOLEDB.Oracle: ICommandText::Execute returned 0×80040155

I just ran into this problem today. Everything was working fine with my linked server connection to the Oracle database (if you haven’t been following the saga.. read up on it) and then it just stopped working.

Anywho, I managed to get things working again by deleting the linked server and recreating it. This time though I made sure to the “Microsoft OLE DB Provider for Oracle” provider and made sure that “Allow In Process” was checked under the “Provider Options” from the advice I got here.

After doing that, everything is working once again.

I’m becoming a bit of an expert with linked servers to Oracle.

More Oracle 0×80004005 errors with linked server

Yeah… Oracle sucks.

For some reason, I was continuing to get the famous 0×80004005 error even after I got everything to work. When the computer started up, I could perform queries to the linked Oracle server. After a little while though, it would start throwing errors at me and for no reason whatsoever. I searched all over and couldn’t find a solution, so I found it on my own. Here’s what I did.

First thing I did was look at my tnsnames.ora file to see what my configuration was, below is what it looked like (hostnames changed to protect the innocent):

FDEV.EXAMPLE.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpdev1.EXAMPLE.COM)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = fdev)
)
)

My sqlnet.ora file was also setup correctly with:

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)

Now I could ping erpdev1.example.com and if I ran Net Configuration Assistant I would pass the connection test to fdev.example.com. Thing was though I couldn’t perform a tnsping to fdev.example.com for some reason. On a hunch I opened up my hosts file (c:\windows\system32\drivers\etc) and added an entry. I mapped fdev.example.com to the same ip as erpdev1.example.com and saved. Tried again and everything worked!

To sum up, if you run into this weird problem just add an entry into your hosts file to point your service name to the same ip address as your host.

UPDATE:

I ran into this problem again. It seems like many things cause it. This time I received error #7399. The trick into solving this one for me was to open up my SQLNET.ORA file and to comment my NAMES.DEFAULT_DOMAIN section. Seems that this was confusing oracle when accessing the service name.

Now while commenting out the line fixed my problem. You may have to add something to it. Point is to just make sure it is correct. You will know by trying to make a connection through SQLPLUS and if you get an error complaining that it cannot access the service name, you know the problem.

SQL Server 2000: Linked Oracle Server Errors

I HATE ORACLE!!! Why does this always happen to me?

Again, I spent the last 4 hours of my day trying to get something as simple as a linked server in MSSQL 2000 to work. What I was doing was setting up a linked server between MSSQL 2000 and Oracle 9i and ran into a lot of problem. Below is ranting and how I finally solved my problem.

The one thing I can’t stand about setting up a linked server in MSSQL with Oracle is that it’s never the same. Sometime you can get away with using the “Microsoft OLE DB Provider for Oracle”, other times you need to use the “Microsoft OLE DB Provider for ODBC Drivers” and still sometimes you need to use the “Oracle Provider for OLE DB”. In summary you have 3 different providers to choose from depending on nothing but the toss of a hat.

Just to prove my point, I already have a linked server setup on another MSSQL 2000 server using the “Microsoft OLE DB Provider for Oracle” provider. On the server I’m setting up, I following the EXACT same configuration and used the same provider and it would connect. I’m getting Error 7312 and Error 7399 every time I try to connect and view the tables.

{Error 7399: OLE DB provider ‘MSDAORA’ reported an error. OLEDB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ]}

{Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider ‘OraOLEDB.Oracle’. [OLE/DB provider returned message: ORA-00942: table or view does not exist]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandPrepare::Prepare returned 0x80040e14]. }

After playing around for 4 hours, I finally found the right combinations to connect and run my queries. Here is what I tried. I will say this, that there is no definitive solution for this. You are going to have to try each suggestion and see which one works for you. I should note that I do all of this inside of Enterprise Manager.

First off, pick one of the following providers and create your linked server:
Microsoft OLE DB Provider for Oracle
Microsoft OLE DB Provider for ODBC Drivers
Oracle Provider for OLE DB

Now once you have your linked server created, I expanded the link server and clicked on TABLES to see if I got a list of the tables or if I got the 7399 error. If you get the error, try a different provider until you get the list of tables. When you finally get the listing of tables, remember the value in the “schema” column for that table, it might come into play in the next part.

After you get the list of tables, open up Query Analyzer and try to do a query against the linked server. I have always had to use OPENQUERY to get the query to work. There are two syntaxes that you will have to try:

First try this:

select * from openquery(my_linked_server, ’select * from table_name’)

If you get the 7321 error try this:

select * from openquery(my_linked_server, ’select * from schema_value.table_name’)

One of those should work. If anyone else comes up with weird errors or a difference method to connect, leave your suggestion in the comments below.