Oracle connection problem

[John]

I'm having some grief connecting to an Oracle database that I
previously was able to connect to.

The difference is that my web app now lives on a freshly setup server
with a new install of the Oracle client files/drivers.

My web server has both:
Oracle 9 client files (SQL Plus and other annoyances)
WinXP Pro (its a test server)

The web server also has valid TNSNAMES.ORA file in the default
location

I have created a .UDL file on the server that can successfully connect
to the remote Oracle database so it is finding my TNSNAMES.ORA and the
relevant entry with no problem.

The problem is that my ASP 3 application can't connect to the Oracle
DB. I'm assuming its because it (or IIS) can't find the .ORA file.

I don't think i had any worries when i configured the ASP application
on the old server.

The code I'm using to connect is unchanged and is as follows:

'This is in my global.asa
Application("DSNOracle") = "Provider=MSDAORA.1;Data
Source=MyDataSource;User ID=myid;Password=mypasswd"


' this is the page that connects to oracle
Dim objConn, rsOracle
Dim sqlStatement

sqlStatement = "SELECT Fname, Lname FROM DumbTable Where Lname =
'smith'"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open (Application("DSNOracle"))
Set rsOracle= objConn.Execute(sqlStatement)

[Tracey]

Hi John
I recently ran into a similar problem, the work around
ended up being: to remove the Oracle 9 client and install
the Oracle 8 client. We needed a quick fix and this
resolved it ... Good Luck!

>-----Original Message-----
>I'm having some grief connecting to an Oracle database

that I

>previously was able to connect to.
>
>The difference is that my web app now lives on a freshly

setup server

>with a new install of the Oracle client files/drivers.
>
>My web server has both:
>Oracle 9 client files (SQL Plus and other annoyances)
>WinXP Pro (its a test server)
>
>The web server also has valid TNSNAMES.ORA file in the

default

>location
>
>I have created a .UDL file on the server that can

successfully connect

>to the remote Oracle database so it is finding my

TNSNAMES.ORA and the

>relevant entry with no problem.
>
>The problem is that my ASP 3 application can't connect to

the Oracle

>DB. I'm assuming its because it (or IIS) can't find

the .ORA file.

>
>I don't think i had any worries when i configured the ASP

application

>on the old server.
>
>The code I'm using to connect is unchanged and is as

follows:

>
>'This is in my global.asa
>Application("DSNOracle") = "Provider=MSDAORA.1;Data
>Source=MyDataSource;User ID=myid;Password=mypasswd"
>
>
>' this is the page that connects to oracle
>Dim objConn, rsOracle
>Dim sqlStatement
>
>sqlStatement = "SELECT Fname, Lname FROM DumbTable Where

Lname =

>'smith'"
>
>Set objConn = Server.CreateObject("ADODB.Connection")
>objConn.Open (Application("DSNOracle"))
>Set rsOracle= objConn.Execute(sqlStatement)
>
>.
>

[Turkbear]

On Wed, 17 Sep 2003 20:33:14 +0930, John <jnode55@icqmail.com> wrote:

>I'm having some grief connecting to an Oracle database that I
>previously was able to connect to.
>
>The difference is that my web app now lives on a freshly setup server
>with a new install of the Oracle client files/drivers.
>
>My web server has both:
>Oracle 9 client files (SQL Plus and other annoyances)
>WinXP Pro (its a test server)
>
>The web server also has valid TNSNAMES.ORA file in the default
>location
>
>I have created a .UDL file on the server that can successfully connect
>to the remote Oracle database so it is finding my TNSNAMES.ORA and the
>relevant entry with no problem.
>
>The problem is that my ASP 3 application can't connect to the Oracle
>DB. I'm assuming its because it (or IIS) can't find the .ORA file.
>
>I don't think i had any worries when i configured the ASP application
>on the old server.
>
>The code I'm using to connect is unchanged and is as follows:
>
>'This is in my global.asa
>Application("DSNOracle") = "Provider=MSDAORA.1;Data
>Source=MyDataSource;User ID=myid;Password=mypasswd"
>
>
>' this is the page that connects to oracle
>Dim objConn, rsOracle
>Dim sqlStatement
>
>sqlStatement = "SELECT Fname, Lname FROM DumbTable Where Lname =
>'smith'"
>
>Set objConn = Server.CreateObject("ADODB.Connection")
>objConn.Open (Application("DSNOracle"))
>Set rsOracle= objConn.Execute(sqlStatement)

What version of Oracle is the database you are trying to reach?
The V9 client cannot connect to any Oracle database older than v8.

[Abbey]

John, Do you have MDAC_TYP.EXE, (The Microsoft Data Access
Components 2.8) on the new server? I ran into this
recently where I could connect on one box and not
another. It ended up being the lack of the exe above on
the new box that was preventing the connection from taking
place.

Best of Luck!
Abbey

>-----Original Message-----
>I'm having some grief connecting to an Oracle database

that I

>previously was able to connect to.
>
>The difference is that my web app now lives on a freshly

setup server

>with a new install of the Oracle client files/drivers.
>
>My web server has both:
>Oracle 9 client files (SQL Plus and other annoyances)
>WinXP Pro (its a test server)
>
>The web server also has valid TNSNAMES.ORA file in the

default

>location
>
>I have created a .UDL file on the server that can

successfully connect

>to the remote Oracle database so it is finding my

TNSNAMES.ORA and the

>relevant entry with no problem.
>
>The problem is that my ASP 3 application can't connect to

the Oracle

>DB. I'm assuming its because it (or IIS) can't find

the .ORA file.

>
>I don't think i had any worries when i configured the ASP

application

>on the old server.
>
>The code I'm using to connect is unchanged and is as

follows:

>
>'This is in my global.asa
>Application("DSNOracle") = "Provider=MSDAORA.1;Data
>Source=MyDataSource;User ID=myid;Password=mypasswd"
>
>
>' this is the page that connects to oracle
>Dim objConn, rsOracle
>Dim sqlStatement
>
>sqlStatement = "SELECT Fname, Lname FROM DumbTable Where

Lname =

>'smith'"
>
>Set objConn = Server.CreateObject("ADODB.Connection")
>objConn.Open (Application("DSNOracle"))
>Set rsOracle= objConn.Execute(sqlStatement)
>
>.
>

[John]

Well thanks for the replies folks.

The problem has been solved.

For some reason the instance name in my TNSNAMES.ORA file was the
problem.

It used to be "MyDataSource.World" but for some reason, I can only
access it now by removing the ".world" part of the instance name so
that it is just "MyDataSource".

I have no idea why the same verson of the Oracle client should need
that modification in the .ora file. I'm writing it off as "just one
of those things" :-).