Re: Can't connect to Oracle using MSDAORA OLE DB provider

On Sep 7, 11:16 am, Jojo <jojoh..._at_gmail.com> wrote:
> Thanks for your reply. Everything that you mentioned has been set
> correclty. I can connet to Oracle from Access. I can view Oracle data
> tables in Oracle enterprise manager console and SQL Plus But I can not
> connect to oracle in my application. This application workes fine in
> another Windows computer. I am in the middle of setting up a new
> computer for running this application. I suspect that something
> different in the OS which causes this problem.
>
> On Sep 7, 10:32 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
> > On Sep 6, 6:10 pm, Jojo <jojoh..._at_gmail.com> wrote:
>
> > > When use MS OLE oracle driver to connect to Oracle, I got the error
> > > "Test connection failed because of an error in initializing provider.
> > > Oracle error occurred, but the error message could not be retreived
> > > from Oracle."
>
> > > I have noticed that smeone had the same problem back to 2000. Does
> > > anyone know a solution for this problem?
>
> > > Thanks.
>
> > There are many potential causes of this problem. You can find
> > information on how to set up and debug a linked database to Oracle on
> > the Microsoft SQL Server web site.
>
> > Some things that have to be set:
> > You must have an Oracle client installed on the SQL Server platform
>
> > You must have configured tnsnames.ora
>
> > You must reference the correct tnsname.ora service name (alias) in the
> > lined db
>
> > You must set up the correct security context in the link and depending
> > on this setup you must provide a valid Oracle username and password
>
> > MS DTC must be configured
>
> > Artice: How to Troubleshoot a linked server to an Oracle database in
> > SQL Server article id: 280106 (formerly Q280106) may be of interest.
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

Jojo, (if you are still working on this) is your applicaiton trying to issue remote queries to Oracle using a SQL Server linked database or is it trying to connect directly to Oracle? My response was for using a linked databases meaning the application connects to SQL Server and then just issues queries against the linked database.

With SQL Server 2000 when you create the linked database if you select the tables icon and a list of tables appears then the link is good.

MS provides events that can be set to capture better error messages that are discussed or linked to in the article I referenced.

HTH -- Mark D Powell -- Received on Mon Sep 10 2007 - 12:35:07 CDT