Oracle Linked Server Problems

We are attempting to create a linked server to an Oracle database and it
isn't working.
We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
installed the Oracle 9.2.0.1 client and am able to successfully connect to
the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
creating an ODBC DSN using the Oracle driver provided by Oracle, and hitting
the "test connection" button. Both ways work, so I am fairly certain I have
the tnsnames.ora file configured correctly.
The problem is when I try to create the linked server. I have tried to
create the linked server in 2 different ways:
1. Using the Microsoft OLE DB Provider for Oracle (MSDAORA), and pointing it
at the alias in the tnsnames.ora file.
EXEC sp_addlinkedserver 'OrclDB', 'Oracle', 'MSDAORA', 'OracleDB', where
OracleDB is the Net alias.
EXEC sp_addlinkedsrvlogin 'OrclDB', false, NULL, 'jsmith', 'jsmith'
After executing these 2 stored procedures (I know the username and password
to be valid since since I used them to connect using SQL*Plus), I tried to
expand the linked server in Enterprise Manager and browse either Tables or
Views I get the following error message:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
2. Using the Microsoft OLE DB Driver for ODBC Sources (MSDASQL), and
specifying an ODBC DSN name. I have tried this using 2 different DSNs, one
that used the Oracle Corp. provided driver which passes the "test connection"
test. The other DSN was set up using the Micrsoft ODBC for Oracle driver.
No matter which DSN I point to from the linked server, I get the following
error when attempting to expand the tables or views:
--
SQL Server Enterprise Manager
--
Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLAllocHandle on SQL_HANDLE_ENV failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
--
I have 2 other servers in different domains, using the same versions of
Oracle, SQL and Windows, and configuration #2 above works fine for both of
them.
Does anyone know what is going on? Is this because I am using an
unsupported version of Oracle? Thanks in advance."Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> We are attempting to create a linked server to an Oracle database and it
> isn't working.
> We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> installed the Oracle 9.2.0.1 client and am able to successfully connect to
> the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> creating an ODBC DSN using the Oracle driver provided by Oracle, and
> hitting
> the "test connection" button. Both ways work, so I am fairly certain I
> have
> the tnsnames.ora file configured correctly.
>
Did you remember to reboot your server after installing the Oracle client?
Does the SQL Server account have access to the Oracle client install
folders?
David|||David, you are a true friend. The reboot worked. I can't believe it was
that simple, and that I didn't think to reboot. Thank you.
"David Browne" wrote:
> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:B04987DE-4F07-41F6-9B3B-BA57F934CD74@.microsoft.com...
> > We are attempting to create a linked server to an Oracle database and it
> > isn't working.
> >
> > We are running SQL Server 2000, SP3 on Windows 2003 Standard, SP1. I have
> > installed the Oracle 9.2.0.1 client and am able to successfully connect to
> > the Oracle database in either of 2 ways: (1) via SQL*Plus or, (2) by
> > creating an ODBC DSN using the Oracle driver provided by Oracle, and
> > hitting
> > the "test connection" button. Both ways work, so I am fairly certain I
> > have
> > the tnsnames.ora file configured correctly.
> >
> Did you remember to reboot your server after installing the Oracle client?
> Does the SQL Server account have access to the Oracle client install
> folders?
> David
>
>|||"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:8024382F-49C1-4162-BC01-2F4D58D40E4B@.microsoft.com...
> David, you are a true friend. The reboot worked. I can't believe it was
> that simple, and that I didn't think to reboot. Thank you.
>
It's not documented anywhere, but processes load the Oracle client by
loading OCI.DLL, which must be in the path. The PATH is an environment
variable and is set for services (like SQL Server) only on server startup.
David