Rip's Domain

SQL Server 2000: Linked Oracle Server Errors

Posted in Oracle, SQL, TechSupport by rip747 on December 14, 2007

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 wouldn’t 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 combination 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, expand the link server and clicked on TABLES to see if you get a list of the tables or if you get 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.

7 Responses

Subscribe to comments with RSS.

  1. […] my last post, I spend 4 hours trying to figure out exactly how to get a linked server working with SQL Server. […]

  2. Andrea said, on September 11, 2008 at 3:18 pm

    Ooh finally I get results in Query Analyzer at least. Now I need to find a solution to that 7405 Error…

    Thanks!

  3. David said, on February 26, 2009 at 5:56 pm

    Thanks! Sorted my issue

  4. morten said, on September 10, 2009 at 10:05 am

    hi

    I got the 7312 error while trying to query a linked server to Oracle. In my case the problem was the query syntax; the following works for me (notice the double punctuation):

    select *
    from linked_server..schema.table

    I suggest this explains the part in the error message that says “A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.”

  5. Reziere said, on December 15, 2009 at 9:12 am

    I was having the following error message:

    {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.

    But after reading your post, the solution where we use the SCHEMA name before the table/view name worked properly! {select * from openquery(my_linked_server, ’select * from schema_value.table_name’)}

    Thanks for helping.

  6. vbabu said, on September 19, 2011 at 9:12 am

    Cool, got connection and query issues solved by following your steps posted.

    Thanks

  7. ofni999@hotmail.com said, on June 22, 2012 at 8:57 am

    cheers mate have spent DAYS trying to figure this ..now need to UPDATE through openquery from SQL Server 2000 to Oracle 9i, whilst checking if record already exists


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: