Hi,
I have Oracle 9i. Oracle listener is aborting by itself after every few minutes giving TNSLSNR.exe failed. While listener is running I can do all the activity on the database.
Please let me know if there is any solution.
Thanks in Advance.
PushpamDear this is a SQL server related and keep your questions pertaining to SQL server, kindly post in Oracle newsgroups.
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Wednesday, March 7, 2012
Oracle Linked Servers
I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craig
To use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craig
To use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>
Oracle Linked Servers
I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally us
ed
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Exec
ute
returned 0x80040155].
Thanks for any help.
-craigTo use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally
used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally us
ed
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Exec
ute
returned 0x80040155].
Thanks for any help.
-craigTo use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally
used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>
Oracle Linked Servers
I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craigTo use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
--
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craigTo use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
--
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>
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...
> 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
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...
> 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
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
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
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 hav
e
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...
> 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
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 hav
e
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...
> 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
Oracle linked Server Performance
Hello,
we have an Oracle Linked Server (8i) running linked from SQL Server 2000
SP4.
The performance is ok when selecting data from the sql server, but poor when
trying to insert data into the oracle database via linked server.
Even simple inserts (
insert into lserver..xxx.tablename
select * from sqlservertable
)
take pretty long (only a few thousand inserts per hour).
As we want to insert millions of datarows, this is not acceptable.
Any ideas on how to speed this up ?
if it does not work faster over the linked server, we'll need to do flat
file import / export ...
Thank you,
Markus.
Linked servers were never really intended for that much activity. A DTS
solution is probably more appropriate for something like that.
Andrew J. Kelly SQL MVP
"Markus Donath" <crourvaick@.kriocoucke.mailexpire.com> wrote in message
news:%23UCbEoh1FHA.1568@.TK2MSFTNGP10.phx.gbl...
> Hello,
> we have an Oracle Linked Server (8i) running linked from SQL Server 2000
> SP4.
> The performance is ok when selecting data from the sql server, but poor
> when trying to insert data into the oracle database via linked server.
> Even simple inserts (
> insert into lserver..xxx.tablename
> select * from sqlservertable
> )
> take pretty long (only a few thousand inserts per hour).
> As we want to insert millions of datarows, this is not acceptable.
> Any ideas on how to speed this up ?
> if it does not work faster over the linked server, we'll need to do flat
> file import / export ...
> Thank you,
> Markus.
>
we have an Oracle Linked Server (8i) running linked from SQL Server 2000
SP4.
The performance is ok when selecting data from the sql server, but poor when
trying to insert data into the oracle database via linked server.
Even simple inserts (
insert into lserver..xxx.tablename
select * from sqlservertable
)
take pretty long (only a few thousand inserts per hour).
As we want to insert millions of datarows, this is not acceptable.
Any ideas on how to speed this up ?
if it does not work faster over the linked server, we'll need to do flat
file import / export ...
Thank you,
Markus.
Linked servers were never really intended for that much activity. A DTS
solution is probably more appropriate for something like that.
Andrew J. Kelly SQL MVP
"Markus Donath" <crourvaick@.kriocoucke.mailexpire.com> wrote in message
news:%23UCbEoh1FHA.1568@.TK2MSFTNGP10.phx.gbl...
> Hello,
> we have an Oracle Linked Server (8i) running linked from SQL Server 2000
> SP4.
> The performance is ok when selecting data from the sql server, but poor
> when trying to insert data into the oracle database via linked server.
> Even simple inserts (
> insert into lserver..xxx.tablename
> select * from sqlservertable
> )
> take pretty long (only a few thousand inserts per hour).
> As we want to insert millions of datarows, this is not acceptable.
> Any ideas on how to speed this up ?
> if it does not work faster over the linked server, we'll need to do flat
> file import / export ...
> Thank you,
> Markus.
>
Oracle linked Server Performance
Hello,
we have an Oracle Linked Server (8i) running linked from SQL Server 2000
SP4.
The performance is ok when selecting data from the sql server, but poor when
trying to insert data into the oracle database via linked server.
Even simple inserts (
insert into lserver..xxx.tablename
select * from sqlservertable
)
take pretty long (only a few thousand inserts per hour).
As we want to insert millions of datarows, this is not acceptable.
Any ideas on how to speed this up ?
if it does not work faster over the linked server, we'll need to do flat
file import / export ...
Thank you,
Markus.Linked servers were never really intended for that much activity. A DTS
solution is probably more appropriate for something like that.
Andrew J. Kelly SQL MVP
"Markus Donath" <crourvaick@.kriocoucke.mailexpire.com> wrote in message
news:%23UCbEoh1FHA.1568@.TK2MSFTNGP10.phx.gbl...
> Hello,
> we have an Oracle Linked Server (8i) running linked from SQL Server 2000
> SP4.
> The performance is ok when selecting data from the sql server, but poor
> when trying to insert data into the oracle database via linked server.
> Even simple inserts (
> insert into lserver..xxx.tablename
> select * from sqlservertable
> )
> take pretty long (only a few thousand inserts per hour).
> As we want to insert millions of datarows, this is not acceptable.
> Any ideas on how to speed this up ?
> if it does not work faster over the linked server, we'll need to do flat
> file import / export ...
> Thank you,
> Markus.
>
we have an Oracle Linked Server (8i) running linked from SQL Server 2000
SP4.
The performance is ok when selecting data from the sql server, but poor when
trying to insert data into the oracle database via linked server.
Even simple inserts (
insert into lserver..xxx.tablename
select * from sqlservertable
)
take pretty long (only a few thousand inserts per hour).
As we want to insert millions of datarows, this is not acceptable.
Any ideas on how to speed this up ?
if it does not work faster over the linked server, we'll need to do flat
file import / export ...
Thank you,
Markus.Linked servers were never really intended for that much activity. A DTS
solution is probably more appropriate for something like that.
Andrew J. Kelly SQL MVP
"Markus Donath" <crourvaick@.kriocoucke.mailexpire.com> wrote in message
news:%23UCbEoh1FHA.1568@.TK2MSFTNGP10.phx.gbl...
> Hello,
> we have an Oracle Linked Server (8i) running linked from SQL Server 2000
> SP4.
> The performance is ok when selecting data from the sql server, but poor
> when trying to insert data into the oracle database via linked server.
> Even simple inserts (
> insert into lserver..xxx.tablename
> select * from sqlservertable
> )
> take pretty long (only a few thousand inserts per hour).
> As we want to insert millions of datarows, this is not acceptable.
> Any ideas on how to speed this up ?
> if it does not work faster over the linked server, we'll need to do flat
> file import / export ...
> Thank you,
> Markus.
>
Oracle linked server (64-bit)
We have an active-active cluster on two 64-bit servers, with an
instance of SQL Server running on each node.
We installed the 64-bit oracle client and it works just fine using
Oracle's "SQL Plus" app. But when we add it as a linked server in SQL
Server, it hangs when we try to access it, whether in the Enterprise
Manager or executing "sp_catalogs". It then kicks us off and the node
has to be rebooted for all the services and groups to come back online
properly.
we have a linked server to Oracle on another 64-bit Sql Server (not
clustered) which works just fine for us.
Has anyone seen this? Any helpful thoughts?
bryan
Make sure the provider is setup to execute in-process with SQL Server, by
default it is not since it is not a Microsoft provider, but the Oracle
provider does not work correctly out of process.
See http://support.microsoft.com/default...B;EN-US;280106
NOTE : If you use a third-party Oracle provider and it is incapable of
running outside of a SQL Server process, allow it to run in-process by
changing the provider options. To change the provider options, change the
value of the AllowInProcess (DWORD) entry in the following registry key,
which is located under the corresponding provider name, to 1:
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"bk" <r.bryan.kane@.gmail.com> wrote in message
news:1102633081.687411.319750@.f14g2000cwb.googlegr oups.com...
> We have an active-active cluster on two 64-bit servers, with an
> instance of SQL Server running on each node.
> We installed the 64-bit oracle client and it works just fine using
> Oracle's "SQL Plus" app. But when we add it as a linked server in SQL
> Server, it hangs when we try to access it, whether in the Enterprise
> Manager or executing "sp_catalogs". It then kicks us off and the node
> has to be rebooted for all the services and groups to come back online
> properly.
> we have a linked server to Oracle on another 64-bit Sql Server (not
> clustered) which works just fine for us.
> Has anyone seen this? Any helpful thoughts?
> bryan
>
instance of SQL Server running on each node.
We installed the 64-bit oracle client and it works just fine using
Oracle's "SQL Plus" app. But when we add it as a linked server in SQL
Server, it hangs when we try to access it, whether in the Enterprise
Manager or executing "sp_catalogs". It then kicks us off and the node
has to be rebooted for all the services and groups to come back online
properly.
we have a linked server to Oracle on another 64-bit Sql Server (not
clustered) which works just fine for us.
Has anyone seen this? Any helpful thoughts?
bryan
Make sure the provider is setup to execute in-process with SQL Server, by
default it is not since it is not a Microsoft provider, but the Oracle
provider does not work correctly out of process.
See http://support.microsoft.com/default...B;EN-US;280106
NOTE : If you use a third-party Oracle provider and it is incapable of
running outside of a SQL Server process, allow it to run in-process by
changing the provider options. To change the provider options, change the
value of the AllowInProcess (DWORD) entry in the following registry key,
which is located under the corresponding provider name, to 1:
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"bk" <r.bryan.kane@.gmail.com> wrote in message
news:1102633081.687411.319750@.f14g2000cwb.googlegr oups.com...
> We have an active-active cluster on two 64-bit servers, with an
> instance of SQL Server running on each node.
> We installed the 64-bit oracle client and it works just fine using
> Oracle's "SQL Plus" app. But when we add it as a linked server in SQL
> Server, it hangs when we try to access it, whether in the Enterprise
> Manager or executing "sp_catalogs". It then kicks us off and the node
> has to be rebooted for all the services and groups to come back online
> properly.
> we have a linked server to Oracle on another 64-bit Sql Server (not
> clustered) which works just fine for us.
> Has anyone seen this? Any helpful thoughts?
> bryan
>
Saturday, February 25, 2012
Oracle Error When running Niku Clarity with MS JDBC
Hi All
I get a very strange error when running the JDBC in WAS6.0
This is running the Clarity 7.5.3 Application
Failed to verify database: Io exception:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
Any ideas
Not sure that I understand the scenario here. The Microsoft 2005 JDBC driver is a type 4 Sql Server driver. The only databases supported are Sql Server 2000 and Sql Server 2005.
|||After working with the CA Clarity system now for some time, it is apparent that they just did not change the code to reflect another vendor, so the Oracle errors are realy just database errors
Just another case of Oracle coders thinking that their code will never move to MS SQL... 
Oracle Error When running Niku Clarity with MS JDBC
Hi All
I get a very strange error when running the JDBC in WAS6.0
This is running the Clarity 7.5.3 Application
Failed to verify database: Io exception:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
Any ideas
Not sure that I understand the scenario here. The Microsoft 2005 JDBC driver is a type 4 Sql Server driver. The only databases supported are Sql Server 2000 and Sql Server 2005.
|||After working with the CA Clarity system now for some time, it is apparent that they just did not change the code to reflect another vendor, so the Oracle errors are realy just database errors
Just another case of Oracle coders thinking that their code will never move to MS SQL...
Monday, February 20, 2012
Oracle and SQLServer on the same box?
Develop on both Oracle 9i and SQLServer 2000 back ends and would like to set up a new test/development server. Are there any issues with running both systems on one box?I install Oracle 8i personal edition and SQL server 2000 on the same machine without any problem|||... and I installed
1) Sybase ASE
2) Sybase ASIQ
3) MS-SQL 2000
4) Oracle 9.02
5) IBM/UDB 7.2
on the same (big) box without problem (except the shared resource)|||I've installed these on an winXP machine without any problem
1. MSDE (developer version of sql 2000)
2. Oracle 9i
3. MySQL
:)
1) Sybase ASE
2) Sybase ASIQ
3) MS-SQL 2000
4) Oracle 9.02
5) IBM/UDB 7.2
on the same (big) box without problem (except the shared resource)|||I've installed these on an winXP machine without any problem
1. MSDE (developer version of sql 2000)
2. Oracle 9i
3. MySQL
:)
Subscribe to:
Posts (Atom)