Can't connect to Oracle Dbase with SQL Server 2005 Express

[danbredy@hotmail.com]

Hi,

I'm attempting to connect to an Oracle database using SQL Server 2005
Express (OS is Windows XP Professional) and having absolutely no luck.
Here is the information SQL Plus
gives me about the database when I log in -

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

As you can see I have no problem connecting with the Oracle specific
tools (SQL PLUS and PL/SQL Developer).

I've tried creating a linked server with both the Microsoft provided
OLE DB provider and the Oracle provider. With the Microsoft Provider I
get the following error when attempting a query in the query edit
window.

OLE DB provider "MSDAORA" for linked server "ORACLE" 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.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDAORA"
for linked server "ORACLE".

When I try connecting withe Oracle provider I get the following -

OLE DB provider "OraOLEDB.Oracl e" for linked server "ORACLETEST 2"
returned message "ORA-01017: invalid username/password; logon denied".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider
"OraOLEDB.Oracl e" for linked server "ORACLETEST 2".

I've set the password up as a local to remote login mapping using the
security page of the Linked Server property dialog. The username /
password are definitely correct -- I used them to log in using SQL PLUS
and PL/SQL Developer. I'm using an Oracle 9 client.

PLEASE HELP !!!!!

Thanks,

Signed Frustrated

[Mark Townsend]

Re: Can't connect to Oracle Dbase with SQL Server 2005 Express

danbredy@hotmai l.com wrote:
[color=blue]
> Hi,
>
> I'm attempting to connect to an Oracle database using SQL Server 2005
> Express (OS is Windows XP Professional) and having absolutely no luck.[/color]

Does the phrase "going from the sublime to the ridiculous" have any
meaning for you :-)
[color=blue]
> OLE DB provider "MSDAORA" for linked server "ORACLE" 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.".[/color]

Is this on the same machine that Oracle is installed on ? Then it's
likely that you haven't installed the required client software. The
SQLPlus connections don't necessarily go through the network layer if
you are doing them on the same box.
[color=blue]
> When I try connecting withe Oracle provider I get the following -
>
> OLE DB provider "OraOLEDB.Oracl e" for linked server "ORACLETEST 2"
> returned message "ORA-01017: invalid username/password; logon denied".
> Msg 7303, Level 16, State 1, Line 2[/color]

Cool - This shows that the Oracle provider is connecting to the Oracle
database - that error message comes from the database itself.
[color=blue]
> I've set the password up as a local to remote login mapping using the
> security page of the Linked Server property dialog. The username /
> password are definitely correct[/color]

Nope - the username/password being provided to the Oracle database are
definitely NOT correct. I have no idea what "local to remote mapping
using the security page of the Linked Server property dialog" actually
means, so can't help there, but I'm guessing that's the problem.

As an aside, why SQL Server Express ? If you download Oracle Database
Express Edition and use that to connect to the backend Oracle database,
you may find life a little easier, a lot less sublime, and definitely
not as ridiculous -

http://www.oracle.com/technology/products/database/xe/index.html

[Crazy Cat]

Re: Can't connect to Oracle Dbase with SQL Server 2005 Express

Hi Mark,

I figured mabye my problem could be solved by upgrading the version of
Oracle so I installed Oracle 10.1 on Friday and had success with the
Microsoft Oracle OLEDB provider -- not so with the Oracle provider, but
rather than wasting a bunch of time figuring out why I couldn't connect
with that I figured the MS Oracle DB provider would fill my needs.
However this morning I attempted to once again connect via SQL Server
Express figuring and had gotten over the hurdle and I got a new error
-- OLE DB provider "MSDAORA" for linked server "MSORACLE" returned
message "Oracle error occurred, but error message could not be
retrieved from Oracle.". And again I can connect to this database fine
using PL/SQL Developer. So once again I'm back to square one and
totally perplexed. Any idea how
to retrieve the Oracle messages?

As to why I'm using SQL Server Express -- I am doing an MS Excel
automation project, so I'm not quite sure that Oracle is an option on
the client side. Also the backend is not on my machine -- I actually
have no idea where it is, except that I know it's some huge (6
Terabyte) database server somewhere.

By "Local to remote mapping" I'm referring to the selection on SQL
Server Express' Security Property Page (right click on Properties for
the linked server, then click on the Security selection in left pane of
the dialog box that appears). It appears to allow you to enter a user
and password combination that will be used to attach to the linked
server.

Thanks again

[DA Morgan]

Re: Can't connect to Oracle Dbase with SQL Server 2005 Express

Crazy Cat wrote:[color=blue]
> Hi Mark,
>
> I figured mabye my problem could be solved by upgrading the version of
> Oracle so I installed Oracle 10.1 on Friday and had success with the
> Microsoft Oracle OLEDB provider -- not so with the Oracle provider, but
> rather than wasting a bunch of time figuring out why I couldn't connect
> with that I figured the MS Oracle DB provider would fill my needs.
> However this morning I attempted to once again connect via SQL Server
> Express figuring and had gotten over the hurdle and I got a new error
> -- OLE DB provider "MSDAORA" for linked server "MSORACLE" returned
> message "Oracle error occurred, but error message could not be
> retrieved from Oracle.". And again I can connect to this database fine
> using PL/SQL Developer. So once again I'm back to square one and
> totally perplexed. Any idea how
> to retrieve the Oracle messages?
>
> As to why I'm using SQL Server Express -- I am doing an MS Excel
> automation project, so I'm not quite sure that Oracle is an option on
> the client side. Also the backend is not on my machine -- I actually
> have no idea where it is, except that I know it's some huge (6
> Terabyte) database server somewhere.
>
> By "Local to remote mapping" I'm referring to the selection on SQL
> Server Express' Security Property Page (right click on Properties for
> the linked server, then click on the Security selection in left pane of
> the dialog box that appears). It appears to allow you to enter a user
> and password combination that will be used to attach to the linked
> server.
>
> Thanks again[/color]

If you are in the United States you might want to take a good look at
the requirements of Sarbanes Oxley and your probable violation by
using MS Excel.

If you have something to do with Oracle data you might want to just
leave it in the Oracle database and create your reports directly from
that repository.
--
Daniel A. Morgan

http://www.psoug.org

damorgan@x.wash ington.edu
(replace x with u to respond)

[Crazy Cat]

Re: Can't connect to Oracle Dbase with SQL Server 2005 Express

Please elaborate on Sarbanes Oxley -- I looked it up but not sure how
it applies to me (software developer). Isn't that
targeted at accountants? My function and the project I'm working on
have nothing to do with accounting -- it's an engineering
function.

I found a solution to my problem with the Oracle OLE DB provider and am
providing a link to the solution for all interested

http://msdn2.microsoft.com/en-us/library/ms152516.aspx

Hope this helps any out there having similar problems.

Thanks

[DA Morgan]

Re: Can't connect to Oracle Dbase with SQL Server 2005 Express

Crazy Cat wrote:[color=blue]
> Please elaborate on Sarbanes Oxley -- I looked it up but not sure how
> it applies to me (software developer). Isn't that
> targeted at accountants? My function and the project I'm working on
> have nothing to do with accounting -- it's an engineering
> function.
>
> I found a solution to my problem with the Oracle OLE DB provider and am
> providing a link to the solution for all interested
>
> http://msdn2.microsoft.com/en-us/library/ms152516.aspx
>
> Hope this helps any out there having similar problems.
>
> Thanks[/color]

If you are working in Excel then it stands to reason you are working
with numbers: Accounting related information.

If those numbers are not constrained so that each every alteration is
auditable and numbers are then used for financial decisions or financial
statements you are in potential violation.

You as a developer are not in violation. But the result of your work
likely will be.
--
Daniel A. Morgan

http://www.psoug.org

damorgan@x.wash ington.edu
(replace x with u to respond)

[HansF]

Re: Can't connect to Oracle Dbase with SQL Server 2005 Express

On Mon, 19 Dec 2005 09:35:08 -0800, Crazy Cat wrote:
[color=blue]
> Please elaborate on Sarbanes Oxley -- I looked it up but not sure how
> it applies to me (software developer). Isn't that
> targeted at accountants? My function and the project I'm working on
> have nothing to do with accounting -- it's an engineering
> function.[/color]

Sarb-Ox is a legal device for enforcing accountability, not accounting.

Basically it means that there must be a verifiable audit trail (just like
ISO-900x) for any operation that *may* impact the financial status of the
organization. This includes any data manipulation using any database
and/or programming tools.

(To counter Enron, everything must be verifiably unschreddable, or
verifiably have no impact on the org's financials.)

It also means that the CEO is personally on the hook for ensuring the
compliance, at risk of a jail term and huge fines. Since CEOs delegate
responsibility, that means that everyone down the corporate ladder may
directly or indirectly be in the path of the delegation.

The succeess of your project and the pay or consulting fees of the
team on your project are expected to have a long-term impact on the
corporate financials. Therefore Sarb Ox applies.

(Heck, in a small organization the refund from recycling pop (soda) cans
from the company-supplied Coke machine could be considered having an
impact - there is no end to how silly the interpretation can become.)

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard _at_gmail.com
*** Top posting [replies] guarantees I won't respond. ***

[krishna mohan]

Re: Can't connect to Oracle Dbase with SQL Server 2005 Express


am working in .net with oracle 10g

while am going to connect database am getting this error

please help me

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.

*** Sent via Developersdex http://www.developersdex.com ***

[DA Morgan]

Re: Can't connect to Oracle Dbase with SQL Server 2005 Express

krishna mohan wrote:

am working in .net with oracle 10g
>
while am going to connect database am getting this error
>
please help me
>
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.
>
*** Sent via Developersdex http://www.developersdex.com ***

The above message is not an Oracle message. Use the Oracle
provided .NET components.
--
Daniel A. Morgan
University of Washington
damorgan@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group

https://www.psoug.org