Wednesday, March 7, 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. 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

Hello,
I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
database.
I've set up the linked server and necessary permissions and it has been
working successfully for months. However occasionally after rebooting the
machine I receive this message when attempting a query from the linked
server:
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
I can still connect successfully through either Oracle Net Configuration
Assistant or an ODBC DSN and the only way I know how to solve this is by
stopping and restarting the SQL Server instance. The only thing I can think
of is that during server startup, the SQL Service gets started before the
oracle service starts, and somehow SQL caches this connection.
Has anyone experienced this? If so, what's the best way to deal with this
without stopping the server?
Thank you,
Aaron Lowe
In some similar situations, reinstalling the Oracle client
on the server when logged in using the service account has
fixed this issue.
-Sue
On Tue, 12 Apr 2005 10:28:46 -0500, "Aaron M. Lowe"
<alowe@.uic.edu> wrote:

>Hello,
>I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
>database.
>I've set up the linked server and necessary permissions and it has been
>working successfully for months. However occasionally after rebooting the
>machine I receive this message when attempting a query from the linked
>server:
>Server: Msg 7399, Level 16, State 1, Line 3
>OLE DB provider 'MSDAORA' reported an error.
>[OLE/DB provider returned message: Oracle client and networking components
>were not found. These components are supplied by Oracle Corporation and are
>part of the Oracle Version 7.3.3 or later client software installation.
>Provider is unable to function until these components are installed.]
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>I can still connect successfully through either Oracle Net Configuration
>Assistant or an ODBC DSN and the only way I know how to solve this is by
>stopping and restarting the SQL Server instance. The only thing I can think
>of is that during server startup, the SQL Service gets started before the
>oracle service starts, and somehow SQL caches this connection.
>Has anyone experienced this? If so, what's the best way to deal with this
>without stopping the server?
>Thank you,
>Aaron Lowe
>

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?

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?

Oracle Linked Server

Hi,
I have an Oracle 9i client installed on SQL Server 2000, and I want to add a
linked Oracle server to SQL Server, I got an error says 'OLEDB error, OLEDB
error trace: initialize returned 0x80004005', and I can not view the tables
or views in the Oracle lin
ked server, can anybody help me on this?
HongHi
0x80004005 is usually a general error, have you checked the connection
string?
http://www.connectionstrings.com/
John
"Hong Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:8AB9BCEB-F6C5-4AD6-ACA2-0A3C0A8890CC@.microsoft.com...
> Hi,
> I have an Oracle 9i client installed on SQL Server 2000, and I want to add
a linked Oracle server to SQL Server, I got an error says 'OLEDB error,
OLEDB error trace: initialize returned 0x80004005', and I can not view the
tables or views in the Oracle linked server, can anybody help me on this?
> Hong|||Try turning on trace flag 7300 on or use SQL Profiler to capture the
OLEDB Errors event. This should allow you to get more information
about the error.
For additional info, you may want to refer to the following:
HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106
-Sue
On Wed, 2 Jun 2004 13:01:08 -0700, Hong Wang
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have an Oracle 9i client installed on SQL Server 2000, and I want to add a linked
Oracle server to SQL Server, I got an error says 'OLEDB error, OLEDB error trace: i
nitialize returned 0x80004005', and I can not view the tables or views in the Oracle
li
nked server, can anybody help me on this?
>Hong

Oracle linked server

Hello,
I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
database.
I've set up the linked server and necessary permissions and it has been
working successfully for months. However occasionally after rebooting the
machine I receive this message when attempting a query from the linked
server:
Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking componen
ts
were not found. These components are supplied by Oracle Corporation and are
part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
I can still connect successfully through either Oracle Net Configuration
Assistant or an ODBC DSN and the only way I know how to solve this is by
stopping and restarting the SQL Server instance. The only thing I can think
of is that during server startup, the SQL Service gets started before the
oracle service starts, and somehow SQL caches this connection.
Has anyone experienced this? If so, what's the best way to deal with this
without stopping the server?
Thank you,
Aaron LoweIn some similar situations, reinstalling the Oracle client
on the server when logged in using the service account has
fixed this issue.
-Sue
On Tue, 12 Apr 2005 10:28:46 -0500, "Aaron M. Lowe"
<alowe@.uic.edu> wrote:

>Hello,
>I have a SQL 2003 SP3a on Windows 2003 and am connecting to an Oracle 9i
>database.
>I've set up the linked server and necessary permissions and it has been
>working successfully for months. However occasionally after rebooting the
>machine I receive this message when attempting a query from the linked
>server:
>Server: Msg 7399, Level 16, State 1, Line 3
>OLE DB provider 'MSDAORA' reported an error.
>[OLE/DB provider returned message: Oracle client and networking compone
nts
>were not found. These components are supplied by Oracle Corporation and are
>part of the Oracle Version 7.3.3 or later client software installation.
>Provider is unable to function until these components are installed.]
>OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
>returned 0x80004005: ].
>I can still connect successfully through either Oracle Net Configuration
>Assistant or an ODBC DSN and the only way I know how to solve this is by
>stopping and restarting the SQL Server instance. The only thing I can think
>of is that during server startup, the SQL Service gets started before the
>oracle service starts, and somehow SQL caches this connection.
>Has anyone experienced this? If so, what's the best way to deal with this
>without stopping the server?
>Thank you,
>Aaron Lowe
>

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