Linked Server oracle 0x80004005 error

Linked Server oracle 0x80004005 error
Has anyone gotten around the .....
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ]. ...
error when creating a linked server to oracle using the oracle drivers ?
Have no problem with the linked server ms oracle driver other than the fact
that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
plus.
Any ideas greatly appreciated ! Thanks, Steve.Hi
0x80004005 = Access Denied. Check that your credentials are correct that you
are supplying. I have also seen this when there are not enough connections
available on the Oracle side to connect.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Note that linked servers are dangerous if you are using the four part naming
method. It is very very very easy to end up pulling the whole table over and
the where clause being processed locally rather than on Oracle. If you are
going to use linked server then use the OpenQuery method instead of 4 part
naming.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> Linked Server oracle 0x80004005 error
> Has anyone gotten around the .....
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ]. ...
> error when creating a linked server to oracle using the oracle drivers ?
> Have no problem with the linked server ms oracle driver other than the
> fact
> that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> plus.
> Any ideas greatly appreciated ! Thanks, Steve.
>|||Bruce,
That's an interesting point you raise. I use linked servers with 4 part
names heavily. They are all MS SQL 2000 servers and I have no problems
at all with this. Are you suggesting this is a problem with all linked
servers or specifically Oracle?
Having said that I want the report server doing the processing rather
than loading the linked servers.
Chris
Bruce L-C [MVP] wrote:
> Note that linked servers are dangerous if you are using the four part
> naming method. It is very very very easy to end up pulling the whole
> table over and the where clause being processed locally rather than
> on Oracle. If you are going to use linked server then use the
> OpenQuery method instead of 4 part naming.|||I am using linked servers against Sybase but it makes no difference. It
could be that your tables are small enough that you haven't seen it. Another
SQL MVP did some testing for me. This gets better in Yukon. But he saw this
a whole lot. I suggest you take some of your queries and do get the query
plan for them and see what is happening. I would be surprised if some of
them aren't bringing all the data over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:uHCpg%23JIFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Bruce,
> That's an interesting point you raise. I use linked servers with 4 part
> names heavily. They are all MS SQL 2000 servers and I have no problems
> at all with this. Are you suggesting this is a problem with all linked
> servers or specifically Oracle?
> Having said that I want the report server doing the processing rather
> than loading the linked servers.
>
> Chris
> Bruce L-C [MVP] wrote:
>> Note that linked servers are dangerous if you are using the four part
>> naming method. It is very very very easy to end up pulling the whole
>> table over and the where clause being processed locally rather than
>> on Oracle. If you are going to use linked server then use the
>> OpenQuery method instead of 4 part naming.
>|||Grutzi Mike,
Can connect to the oracle server with the dsn from ms
access on the server with no problems. Can also see all the tables and views
for this linked server from the enterprise manager console.
Used these instructions to set it up.How to use Microsoft SQL Analyzer with
the Oracle Provider for OLE DB
---
Configuration: ============== Microsoft SQL Server 2000 SP 1 or 2 Microsoft
Windows 2000 Server SP 2 Oracle Provider for OLE DB 8.1.7.3.0 Oracle Client
8.1.7.3.0 Oracle Database 8.1.7.3.0 Instructions to Setup the Linked Server
Connection: =================================================== 1. Open SQL
Server Enterprise Manager 2. Click on the + to expand the tree for the
database server. You will need to expand it three times. Note: If the
SQL Server does not appear here, you will need to right click and go
through the Wizard for SQL Server Registration. - The first level of
the tree is called Microsoft Sql Server, The second level of the tree is
called SQL Server Group, and the third level of the tree is your SQL
Server Database. For these instructions we will call our SQL Server
Database, 'Scott'. 3. Under the Scott Database, click on the + to expand
the tree control for 'Security' 4. Under Security, right click on Linked
Servers and select "New Linked Server" 5. In the Linked Server Properties
do the following: a. Type in a Linked Server Name, for this example we
will call it 'Tiger' b. Under Server Type select Other data source
- For the provider name select Oracle Provider for OLE DB. c. In the
Data Source field, type in the Oracle Service name (SQL Net Alias)
d. Leave the Product Name and Provider String blank. e. Click on the
Provider Options button. - Check the check boxes for "Dynamic
Parameters" and "Allow InProcess" f. Click Apply & then click OK. - The
Provider Options dialog closes. 6. Go to the "Security" tab in the Linked
Server Properties. a. Select the radio button "Be made using this security
context" at the bottom of the box. b. Type in the User ID (your
Oracle login), ie. Scott in the "Remote login" field and your
Password, ie. Tiger in the "With Password" field. 7. Go to the "Server
Options" tab in the Linked Server Properties. a. While leaving the
default selections selected, also check the checkbox for "Collation
Compatible". b. Click OK This will create your Linked Server named Tiger.
Test the Linked Connection ================================== To test the
Linked Server, open up Microsoft Query Analyzer (It is found under the
Microsoft SQL Server menu option). 1. Select your SQL Server in the Connect
to SQL Server box. a. Since we have the SQL Server installed on the same
machine, we select (local). b. Select the appropriate Connect using
option. This is determined when you Installed/Created your SQL Server
database. For our example here we use Windows Authentication. And click
on the Ok button. 2. In the Query screen you can type a query. The
query will follow this format: SELECT * FROM <Linked Server Name>..<Oracle
user name in all CAPS>.<Oracle Table Name in all CAPS> IE. SELECT * FROM
TIGER..SCOTT.EMP 3. If you have the default scott/tiger schema installed in
your Oracle database you can use the following query: SELECT * FROM
TIGER..SCOTT.EMP 4. Type it into the Query box. 5. Click on the Green Arrow
to run the query. 6. You should see the contents of the Emp table in the
Grid.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that your credentials are correct that you
> are supplying. I have also seen this when there are not enough connections
> available on the Oracle side to connect.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Hi Bruce,
Thank you so much for the reply. Created the VMFO linked server
again using the Oracle driver and tried the openquery method and... still
got the ...
SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
OLE DB provider 'OraOLEDB.Oracle' reported an error.
[OLE/DB provider returned message: ]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
returned 0x80004005: ].
"Bruce L-C [MVP]" wrote:
> Note that linked servers are dangerous if you are using the four part naming
> method. It is very very very easy to end up pulling the whole table over and
> the where clause being processed locally rather than on Oracle. If you are
> going to use linked server then use the OpenQuery method instead of 4 part
> naming.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> ..
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > Linked Server oracle 0x80004005 error
> >
> > Has anyone gotten around the .....
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: Unspecified error]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ]. ...
> > error when creating a linked server to oracle using the oracle drivers ?
> > Have no problem with the linked server ms oracle driver other than the
> > fact
> > that it is SLOW !!!! A query returns in 1min30sec that is instant in sql
> > plus.
> >
> > Any ideas greatly appreciated ! Thanks, Steve.
> >
>
>|||Go to your linked server in Enterprise Manager. Right mouse click,
properties. Go to the Security tab. Do you have a username and password set
for it?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> Hi Bruce,
> Thank you so much for the reply. Created the VMFO linked
server
> again using the Oracle driver and tried the openquery method and... still
> got the ...
> SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> OLE DB provider 'OraOLEDB.Oracle' reported an error.
> [OLE/DB provider returned message: ]
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> returned 0x80004005: ].
> "Bruce L-C [MVP]" wrote:
> > Note that linked servers are dangerous if you are using the four part
naming
> > method. It is very very very easy to end up pulling the whole table over
and
> > the where clause being processed locally rather than on Oracle. If you
are
> > going to use linked server then use the OpenQuery method instead of 4
part
> > naming.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > ..
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > Linked Server oracle 0x80004005 error
> > >
> > > Has anyone gotten around the .....
> > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > [OLE/DB provider returned message: Unspecified error]
> > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > returned 0x80004005: ]. ...
> > > error when creating a linked server to oracle using the oracle drivers
?
> > > Have no problem with the linked server ms oracle driver other than the
> > > fact
> > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
sql
> > > plus.
> > >
> > > Any ideas greatly appreciated ! Thanks, Steve.
> > >
> >
> >
> >|||Hi Bruce,
Thanks for your reply. Yes, there is a user/pw set. Also, all
the tables and views for the linked server appear perfectly. Do not think
this would be possible without that. Steve.
"Bruce L-C [MVP]" wrote:
> Go to your linked server in Enterprise Manager. Right mouse click,
> properties. Go to the Security tab. Do you have a username and password set
> for it?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:B0507C65-F50B-431D-AD66-E01381CB9112@.microsoft.com...
> > Hi Bruce,
> > Thank you so much for the reply. Created the VMFO linked
> server
> > again using the Oracle driver and tried the openquery method and... still
> > got the ...
> >
> > SELECT * FROM openquery(VMFO, 'SELECT * FROM SYSADM.REQUEST_FOR_QUOTE')
> >
> > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > [OLE/DB provider returned message: ]
> > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > returned 0x80004005: ].
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Note that linked servers are dangerous if you are using the four part
> naming
> > > method. It is very very very easy to end up pulling the whole table over
> and
> > > the where clause being processed locally rather than on Oracle. If you
> are
> > > going to use linked server then use the OpenQuery method instead of 4
> part
> > > naming.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > > ..
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:D119F7DF-ACC9-4019-8087-6672A956F633@.microsoft.com...
> > > > Linked Server oracle 0x80004005 error
> > > >
> > > > Has anyone gotten around the .....
> > > > OLE DB provider 'OraOLEDB.Oracle' reported an error.
> > > > [OLE/DB provider returned message: Unspecified error]
> > > > OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData
> > > > returned 0x80004005: ]. ...
> > > > error when creating a linked server to oracle using the oracle drivers
> ?
> > > > Have no problem with the linked server ms oracle driver other than the
> > > > fact
> > > > that it is SLOW !!!! A query returns in 1min30sec that is instant in
> sql
> > > > plus.
> > > >
> > > > Any ideas greatly appreciated ! Thanks, Steve.
> > > >
> > >
> > >
> > >
>
>sql