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
>
Showing posts with label servers. Show all posts
Showing posts with label servers. 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 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 / opendatasource
I am trying to select from an Oracle 9i table and get error below. I have
tried both linked servers and OPENDATASOURCE both get the same error. The
opendatasource syntax is also listed below.
The user I am using definitely has permissions to the database/table as I
can pull the information with a DTS package using the same credentials.
Any help would be great.
Thanks
Gary
Error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'OraOLEDB.oracle' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.oracle'
IUnknown::QueryInterface returned 0x80070005: Access denied.].
Syntax
SELECT *
FROM OPENDATASOURCE(
'OraOLEDB.oracle',
'Data Source=MyOracleDB;User ID=user;Password=password'
).MyOracleDB.schema.table
What driver are you using? We use both the MS OLEDB Provider for Oracle and
the MS OLEDB Provider for ODBC and don't have problems using linked servers.
"Gary Hindson" <garyhindson@.hotmail.com> wrote in message
news:OHEW3vufEHA.712@.TK2MSFTNGP09.phx.gbl...
> I am trying to select from an Oracle 9i table and get error below. I have
> tried both linked servers and OPENDATASOURCE both get the same error. The
> opendatasource syntax is also listed below.
> The user I am using definitely has permissions to the database/table as I
> can pull the information with a DTS package using the same credentials.
> Any help would be great.
> Thanks
> Gary
> Error
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'OraOLEDB.oracle' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.oracle'
> IUnknown::QueryInterface returned 0x80070005: Access denied.].
> Syntax
> SELECT *
> FROM OPENDATASOURCE(
> 'OraOLEDB.oracle',
> 'Data Source=MyOracleDB;User ID=user;Password=password'
> ).MyOracleDB.schema.table
>
|||I am using the Oracle Provider for OLE DB as I have the Oracle 9i client
tools installed on the box.
"michelle" <michelle@.nospam.com> wrote in message
news:ulRHTvvfEHA.3272@.TK2MSFTNGP11.phx.gbl...
> What driver are you using? We use both the MS OLEDB Provider for Oracle
and
> the MS OLEDB Provider for ODBC and don't have problems using linked
servers.[vbcol=seagreen]
> "Gary Hindson" <garyhindson@.hotmail.com> wrote in message
> news:OHEW3vufEHA.712@.TK2MSFTNGP09.phx.gbl...
have[vbcol=seagreen]
The[vbcol=seagreen]
I
>
|||We had trouble with that driver. We also had the client tools installed. I'm
sorry that I can't help you any further.
"Gary Hindson" <garyhindson@.hotmail.com> wrote in message
news:OGH7Mj3fEHA.3024@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I am using the Oracle Provider for OLE DB as I have the Oracle 9i client
> tools installed on the box.
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:ulRHTvvfEHA.3272@.TK2MSFTNGP11.phx.gbl...
> and
> servers.
> have
> The
as[vbcol=seagreen]
> I
credentials.
>
|||Gary Hindson <garyhindson@.hotmail.com> wrote:
> Error
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'OraOLEDB.oracle' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.oracle'
> IUnknown::QueryInterface returned 0x80070005: Access denied.].
I had a similar problem recently using any OLE DB provider with MSDE. It
produced an identical error message to yours, except the provider was MSDASQL
(the one for ODBC drivers.)
Adding this to the registry fixed it:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers]
"AllowInProcess"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\MSDASQL]
"AllowInProcess"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\SQLOLEDB]
"AllowInProcess"=dword:00000001
"DisallowAdhocAccess"=dword:00000000
If you're using SQL Server 2000 rather than MSDE it will already have these
registry keys in. Looks like one is needed per provider or it won't work.
Try adding whatever is needed above, and this addional line for your Oracle
driver and see what happens:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\OraOLEDB.oracle]
"AllowInProcess"=dword:00000001
If it isn't the default instance of SQL Server look at these registry keys
instead:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \<#>\Providers\]
where <#> is the name of your instance.
Steve
|||Thats works a treat.
Thanks for your help
"Steve Hunter" <FIRSTNAME.LASTNAME@.penn-elcom.com> wrote in message
news:411cd5c5@.nntp.onyx.net...
> Gary Hindson <garyhindson@.hotmail.com> wrote:
>
> I had a similar problem recently using any OLE DB provider with MSDE. It
> produced an identical error message to yours, except the provider was
MSDASQL
> (the one for ODBC drivers.)
> Adding this to the registry fixed it:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers]
> "AllowInProcess"=dword:00000001
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\MSDASQL]
> "AllowInProcess"=dword:00000001
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\SQLOLEDB]
> "AllowInProcess"=dword:00000001
> "DisallowAdhocAccess"=dword:00000000
> If you're using SQL Server 2000 rather than MSDE it will already have
these
> registry keys in. Looks like one is needed per provider or it won't work.
> Try adding whatever is needed above, and this addional line for your
Oracle
> driver and see what happens:
>
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\OraOLEDB.oracle
]
> "AllowInProcess"=dword:00000001
> If it isn't the default instance of SQL Server look at these registry keys
> instead:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \<#>\Providers\]
> where <#> is the name of your instance.
> Steve
>
tried both linked servers and OPENDATASOURCE both get the same error. The
opendatasource syntax is also listed below.
The user I am using definitely has permissions to the database/table as I
can pull the information with a DTS package using the same credentials.
Any help would be great.
Thanks
Gary
Error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'OraOLEDB.oracle' reported an error. Access denied.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.oracle'
IUnknown::QueryInterface returned 0x80070005: Access denied.].
Syntax
SELECT *
FROM OPENDATASOURCE(
'OraOLEDB.oracle',
'Data Source=MyOracleDB;User ID=user;Password=password'
).MyOracleDB.schema.table
What driver are you using? We use both the MS OLEDB Provider for Oracle and
the MS OLEDB Provider for ODBC and don't have problems using linked servers.
"Gary Hindson" <garyhindson@.hotmail.com> wrote in message
news:OHEW3vufEHA.712@.TK2MSFTNGP09.phx.gbl...
> I am trying to select from an Oracle 9i table and get error below. I have
> tried both linked servers and OPENDATASOURCE both get the same error. The
> opendatasource syntax is also listed below.
> The user I am using definitely has permissions to the database/table as I
> can pull the information with a DTS package using the same credentials.
> Any help would be great.
> Thanks
> Gary
> Error
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'OraOLEDB.oracle' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.oracle'
> IUnknown::QueryInterface returned 0x80070005: Access denied.].
> Syntax
> SELECT *
> FROM OPENDATASOURCE(
> 'OraOLEDB.oracle',
> 'Data Source=MyOracleDB;User ID=user;Password=password'
> ).MyOracleDB.schema.table
>
|||I am using the Oracle Provider for OLE DB as I have the Oracle 9i client
tools installed on the box.
"michelle" <michelle@.nospam.com> wrote in message
news:ulRHTvvfEHA.3272@.TK2MSFTNGP11.phx.gbl...
> What driver are you using? We use both the MS OLEDB Provider for Oracle
and
> the MS OLEDB Provider for ODBC and don't have problems using linked
servers.[vbcol=seagreen]
> "Gary Hindson" <garyhindson@.hotmail.com> wrote in message
> news:OHEW3vufEHA.712@.TK2MSFTNGP09.phx.gbl...
have[vbcol=seagreen]
The[vbcol=seagreen]
I
>
|||We had trouble with that driver. We also had the client tools installed. I'm
sorry that I can't help you any further.
"Gary Hindson" <garyhindson@.hotmail.com> wrote in message
news:OGH7Mj3fEHA.3024@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I am using the Oracle Provider for OLE DB as I have the Oracle 9i client
> tools installed on the box.
>
> "michelle" <michelle@.nospam.com> wrote in message
> news:ulRHTvvfEHA.3272@.TK2MSFTNGP11.phx.gbl...
> and
> servers.
> have
> The
as[vbcol=seagreen]
> I
credentials.
>
|||Gary Hindson <garyhindson@.hotmail.com> wrote:
> Error
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'OraOLEDB.oracle' reported an error. Access denied.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.oracle'
> IUnknown::QueryInterface returned 0x80070005: Access denied.].
I had a similar problem recently using any OLE DB provider with MSDE. It
produced an identical error message to yours, except the provider was MSDASQL
(the one for ODBC drivers.)
Adding this to the registry fixed it:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers]
"AllowInProcess"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\MSDASQL]
"AllowInProcess"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\SQLOLEDB]
"AllowInProcess"=dword:00000001
"DisallowAdhocAccess"=dword:00000000
If you're using SQL Server 2000 rather than MSDE it will already have these
registry keys in. Looks like one is needed per provider or it won't work.
Try adding whatever is needed above, and this addional line for your Oracle
driver and see what happens:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\OraOLEDB.oracle]
"AllowInProcess"=dword:00000001
If it isn't the default instance of SQL Server look at these registry keys
instead:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \<#>\Providers\]
where <#> is the name of your instance.
Steve
|||Thats works a treat.
Thanks for your help
"Steve Hunter" <FIRSTNAME.LASTNAME@.penn-elcom.com> wrote in message
news:411cd5c5@.nntp.onyx.net...
> Gary Hindson <garyhindson@.hotmail.com> wrote:
>
> I had a similar problem recently using any OLE DB provider with MSDE. It
> produced an identical error message to yours, except the provider was
MSDASQL
> (the one for ODBC drivers.)
> Adding this to the registry fixed it:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers]
> "AllowInProcess"=dword:00000001
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\MSDASQL]
> "AllowInProcess"=dword:00000001
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\SQLOLEDB]
> "AllowInProcess"=dword:00000001
> "DisallowAdhocAccess"=dword:00000000
> If you're using SQL Server 2000 rather than MSDE it will already have
these
> registry keys in. Looks like one is needed per provider or it won't work.
> Try adding whatever is needed above, and this addional line for your
Oracle
> driver and see what happens:
>
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \Providers\OraOLEDB.oracle
]
> "AllowInProcess"=dword:00000001
> If it isn't the default instance of SQL Server look at these registry keys
> instead:
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer \<#>\Providers\]
> where <#> is the name of your instance.
> Steve
>
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
>
Oracle linked server
I have several customers that are using oracle linked servers on MSSQL2k. Persons that have multisession rights on the oracle side seem to not have problems querying multiple tables but one of my customers only has the rights to have two sessions at any given time. This customer with the session limitation is trying to query multiple tables and it appears that each table query constitutes a session therefore after the second table is queried they exceed the quota and the job stalls.
I am currently using the microsoft oledb provided for each linked server. For the provider options I selected Nested Queries and AllowInProcess. I have tried the oracle provider, recieved errors, but these errors have not been resolved.
Using msaccess and odbc in the prior implementation worked for this customer. Does anyone have any suggestions or tech links that cover this scenario.
ThanksThis is the message at the end of the job\query.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
I am currently using the microsoft oledb provided for each linked server. For the provider options I selected Nested Queries and AllowInProcess. I have tried the oracle provider, recieved errors, but these errors have not been resolved.
Using msaccess and odbc in the prior implementation worked for this customer. Does anyone have any suggestions or tech links that cover this scenario.
ThanksThis is the message at the end of the job\query.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].
Oracle Linked Server
Does anyone have any experience with Oracle linked servers? We are
considering using an Oracle linked server to pull data out of an Oracle
database into a SQL Server application but our understanding is that a
query's where clause issued from SQL server to Oracle is not resoved until
the entire contents of the Oracle table is brought back to SQL server. Should
Oracle linked servers be generally avoided from within a SQL Server OLTP
application?
No...not necessarily. In addition, you can use Openquery
with the linked server. You just need to write the query
using PL/SQL syntax and not T-SQL syntax. But an openquery
will pass the query string to Oracle and the string you
passed will be executed on the Oracle box and the results
returned to SQL Server. Sometimes referred to as a
pass-through query.
-Sue
On Mon, 28 Feb 2005 08:57:02 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:
>Does anyone have any experience with Oracle linked servers? We are
>considering using an Oracle linked server to pull data out of an Oracle
>database into a SQL Server application but our understanding is that a
>query's where clause issued from SQL server to Oracle is not resoved until
>the entire contents of the Oracle table is brought back to SQL server. Should
>Oracle linked servers be generally avoided from within a SQL Server OLTP
>application?
considering using an Oracle linked server to pull data out of an Oracle
database into a SQL Server application but our understanding is that a
query's where clause issued from SQL server to Oracle is not resoved until
the entire contents of the Oracle table is brought back to SQL server. Should
Oracle linked servers be generally avoided from within a SQL Server OLTP
application?
No...not necessarily. In addition, you can use Openquery
with the linked server. You just need to write the query
using PL/SQL syntax and not T-SQL syntax. But an openquery
will pass the query string to Oracle and the string you
passed will be executed on the Oracle box and the results
returned to SQL Server. Sometimes referred to as a
pass-through query.
-Sue
On Mon, 28 Feb 2005 08:57:02 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:
>Does anyone have any experience with Oracle linked servers? We are
>considering using an Oracle linked server to pull data out of an Oracle
>database into a SQL Server application but our understanding is that a
>query's where clause issued from SQL server to Oracle is not resoved until
>the entire contents of the Oracle table is brought back to SQL server. Should
>Oracle linked servers be generally avoided from within a SQL Server OLTP
>application?
Saturday, February 25, 2012
Oracle Linked Server
Does anyone have any experience with Oracle linked servers? We are
considering using an Oracle linked server to pull data out of an Oracle
database into a SQL Server application but our understanding is that a
query's where clause issued from SQL server to Oracle is not resoved until
the entire contents of the Oracle table is brought back to SQL server. Shoul
d
Oracle linked servers be generally avoided from within a SQL Server OLTP
application?No...not necessarily. In addition, you can use Openquery
with the linked server. You just need to write the query
using PL/SQL syntax and not T-SQL syntax. But an openquery
will pass the query string to Oracle and the string you
passed will be executed on the Oracle box and the results
returned to SQL Server. Sometimes referred to as a
pass-through query.
-Sue
On Mon, 28 Feb 2005 08:57:02 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:
>Does anyone have any experience with Oracle linked servers? We are
>considering using an Oracle linked server to pull data out of an Oracle
>database into a SQL Server application but our understanding is that a
>query's where clause issued from SQL server to Oracle is not resoved until
>the entire contents of the Oracle table is brought back to SQL server. Shou
ld
>Oracle linked servers be generally avoided from within a SQL Server OLTP
>application?
considering using an Oracle linked server to pull data out of an Oracle
database into a SQL Server application but our understanding is that a
query's where clause issued from SQL server to Oracle is not resoved until
the entire contents of the Oracle table is brought back to SQL server. Shoul
d
Oracle linked servers be generally avoided from within a SQL Server OLTP
application?No...not necessarily. In addition, you can use Openquery
with the linked server. You just need to write the query
using PL/SQL syntax and not T-SQL syntax. But an openquery
will pass the query string to Oracle and the string you
passed will be executed on the Oracle box and the results
returned to SQL Server. Sometimes referred to as a
pass-through query.
-Sue
On Mon, 28 Feb 2005 08:57:02 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:
>Does anyone have any experience with Oracle linked servers? We are
>considering using an Oracle linked server to pull data out of an Oracle
>database into a SQL Server application but our understanding is that a
>query's where clause issued from SQL server to Oracle is not resoved until
>the entire contents of the Oracle table is brought back to SQL server. Shou
ld
>Oracle linked servers be generally avoided from within a SQL Server OLTP
>application?
Oracle Linked Server
Does anyone have any experience with Oracle linked servers? We are
considering using an Oracle linked server to pull data out of an Oracle
database into a SQL Server application but our understanding is that a
query's where clause issued from SQL server to Oracle is not resoved until
the entire contents of the Oracle table is brought back to SQL server. Should
Oracle linked servers be generally avoided from within a SQL Server OLTP
application?No...not necessarily. In addition, you can use Openquery
with the linked server. You just need to write the query
using PL/SQL syntax and not T-SQL syntax. But an openquery
will pass the query string to Oracle and the string you
passed will be executed on the Oracle box and the results
returned to SQL Server. Sometimes referred to as a
pass-through query.
-Sue
On Mon, 28 Feb 2005 08:57:02 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:
>Does anyone have any experience with Oracle linked servers? We are
>considering using an Oracle linked server to pull data out of an Oracle
>database into a SQL Server application but our understanding is that a
>query's where clause issued from SQL server to Oracle is not resoved until
>the entire contents of the Oracle table is brought back to SQL server. Should
>Oracle linked servers be generally avoided from within a SQL Server OLTP
>application?
considering using an Oracle linked server to pull data out of an Oracle
database into a SQL Server application but our understanding is that a
query's where clause issued from SQL server to Oracle is not resoved until
the entire contents of the Oracle table is brought back to SQL server. Should
Oracle linked servers be generally avoided from within a SQL Server OLTP
application?No...not necessarily. In addition, you can use Openquery
with the linked server. You just need to write the query
using PL/SQL syntax and not T-SQL syntax. But an openquery
will pass the query string to Oracle and the string you
passed will be executed on the Oracle box and the results
returned to SQL Server. Sometimes referred to as a
pass-through query.
-Sue
On Mon, 28 Feb 2005 08:57:02 -0800, "Ray Kurpiel"
<RayKurpiel@.discussions.microsoft.com> wrote:
>Does anyone have any experience with Oracle linked servers? We are
>considering using an Oracle linked server to pull data out of an Oracle
>database into a SQL Server application but our understanding is that a
>query's where clause issued from SQL server to Oracle is not resoved until
>the entire contents of the Oracle table is brought back to SQL server. Should
>Oracle linked servers be generally avoided from within a SQL Server OLTP
>application?
ORACLE Linked DB
I'm playing around with Linked Servers in SQL Server 2K and when linking to an Oracle Database I'm having very slow response time. If I just query the whole table with out a WHERE statement, it seems to go at a decent rate. If I include any parameters whatsoever, it goes horribly slow. I have used the four part name scenraio and the openquery scenerio. Also I get Date errors occasionally which could probably be corrected if I change the format of the date. Any ideas why the linked server acts so slow? If I use a DTS extract I can query the database any way I want and it runs fine. But I cannot do this because so much data changes in these particular tables in the Oracle DB that I need a live connection.1) ------------------
For your date problem, I sympathize !
I've got about the same date problem (format is not the same in both of my Databases)
I've decided to use varchar(26) types in my SQL Server db.
You should consult my threads fore more info on the dates
maybe it will give ideas !
2) ------------------
For the perfs problem maybe it's due to :
- no indexes on the "where columns"
- too much transformations to do on all the date columns
- not using pre-compiled queries
You should post your query so we can see it
For your date problem, I sympathize !
I've got about the same date problem (format is not the same in both of my Databases)
I've decided to use varchar(26) types in my SQL Server db.
You should consult my threads fore more info on the dates
maybe it will give ideas !
2) ------------------
For the perfs problem maybe it's due to :
- no indexes on the "where columns"
- too much transformations to do on all the date columns
- not using pre-compiled queries
You should post your query so we can see it
Subscribe to:
Posts (Atom)