Connection from ASP Page to Oracle Fails

[Brian]

I am trying to connect to our corporate Oracle database to
query some data in an ASP page. My experience with ASP has
always been with SQL Server and Access databases, but I
figured I could get a connection string to work. I tried
using the following connection string:
"Driver={Microsoft ODBC for
Oracle};Server=PROD01;Uid=username;Pwd=password;"
and got the following error:
SQLAllocHandle on SQL_HANDLE_ENV failed

I then set up an Oracle8 ODBC Driver connection and tried
this:
"DSN=ORDERS;Uid=username;Pwd=password;"
and got this error:
Specified driver could not be loaded due to system error
126

Finally, I set up an Access database, created 2 linked
tables to my ODBC connection, tested them (I could open
them fine within Access) and tried this string:
"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\inetpub\wwwroot\aftweb\ORDERS_LINK.mdb" and got:
Microsoft JET Database Engine (0x80004005)
ODBC--connection to 'ORDERS' failed.
(ORDERS is the ODBC connection to the Oracle database)

Also tried this one:
"Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\inetpub\wwwroot\aftweb\ORDERS_LINK .mdb"
and got the same error as above.

Meanwhile, if I substitute an Access database which does
not have linked tables into the same code, the connection
works fine. So, any ideas? I have no access to the Oracle
servers and no support from the Oracle DBAs, so patches to
Oracle (which have been recommended before) are not an
option.

Thanks much for any help you can offer!
Brian

[Turkbear]

On Mon, 29 Dec 2003 09:06:47 -0800, "Brian" <anonymous@discussions.microsoft.com> wrote:

>I am trying to connect to our corporate Oracle database to
>query some data in an ASP page. My experience with ASP has
>always been with SQL Server and Access databases, but I
>figured I could get a connection string to work. I tried
>using the following connection string:
>"Driver={Microsoft ODBC for
>Oracle};Server=PROD01;Uid=username;Pwd=password ;"
>and got the following error:
>SQLAllocHandle on SQL_HANDLE_ENV failed
>
>I then set up an Oracle8 ODBC Driver connection and tried
>this:
>"DSN=ORDERS;Uid=username;Pwd=password;"
>and got this error:
>Specified driver could not be loaded due to system error
>126
>
>Finally, I set up an Access database, created 2 linked
>tables to my ODBC connection, tested them (I could open
>them fine within Access) and tried this string:
>"Provider=Microsoft.Jet.OLEDB.4.0; Data
>Source=C:\inetpub\wwwroot\aftweb\ORDERS_LINK.md b" and got:
>Microsoft JET Database Engine (0x80004005)
>ODBC--connection to 'ORDERS' failed.
>(ORDERS is the ODBC connection to the Oracle database)
>
>Also tried this one:
>"Driver={Microsoft Access Driver
>(*.mdb)};Dbq=C:\inetpub\wwwroot\aftweb\ORDERS_LIN K.mdb"
>and got the same error as above.
>
>Meanwhile, if I substitute an Access database which does
>not have linked tables into the same code, the connection
>works fine. So, any ideas? I have no access to the Oracle
>servers and no support from the Oracle DBAs, so patches to
>Oracle (which have been recommended before) are not an
>option.
>
>Thanks much for any help you can offer!
>Brian

1: Is an Oracle client installed on the web server? ( If not, read no further, you cannot connect to Oracle without one)

2: Try using a dns-less connection like:
myconn= Server.CreateObject("ADODB.Connection");
myconn.ConnectionTimeout = 15;
myconn.CommandTimeout = 30;

myConnString="Provider=MSDAORA;Password=youroracle password;UserID=youroracleusernamet;DataSource=you rINSTANCE(tnsnames.ora
alias);"
myconn.ConnectionString=myConnString
myconn.Open;

This is more reliable and avoids many permissions issues involved in reading the DSN from the server.

hth

[Mark Schupp]

First try setting up a DSN to the oracle database and see if it works using
the Oracle ODBC Test program. I suggest using the Microsoft driver rather
than the Oracle driver although you may have success with the Oracle driver
(we never have but it depends on the type of SQL statements and recordsets
that you need).

Once you have the connection working in ODBC Test try the same DSN
connection in an ASP page. Here is what one of our connection strings look
like:

dsn=oracle-wbtman1ms;uid=<userid>;pwd=<password>

If that does not work and it works in ODBC Test then there is probably a
permission problem with the IUSR account.

Once you have everything working through a DSN then you can try converting
to a DSN-less connection.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Brian" <anonymous@discussions.microsoft.com> wrote in message
news:05bc01c3ce2e$24a55ba0$a001280a@phx.gbl...

> I am trying to connect to our corporate Oracle database to
> query some data in an ASP page. My experience with ASP has
> always been with SQL Server and Access databases, but I
> figured I could get a connection string to work. I tried
> using the following connection string:
> "Driver={Microsoft ODBC for
> Oracle};Server=PROD01;Uid=username;Pwd=password;"
> and got the following error:
> SQLAllocHandle on SQL_HANDLE_ENV failed
>
> I then set up an Oracle8 ODBC Driver connection and tried
> this:
> "DSN=ORDERS;Uid=username;Pwd=password;"
> and got this error:
> Specified driver could not be loaded due to system error
> 126
>
> Finally, I set up an Access database, created 2 linked
> tables to my ODBC connection, tested them (I could open
> them fine within Access) and tried this string:
> "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=C:\inetpub\wwwroot\aftweb\ORDERS_LINK.mdb" and got:
> Microsoft JET Database Engine (0x80004005)
> ODBC--connection to 'ORDERS' failed.
> (ORDERS is the ODBC connection to the Oracle database)
>
> Also tried this one:
> "Driver={Microsoft Access Driver
> (*.mdb)};Dbq=C:\inetpub\wwwroot\aftweb\ORDERS_LINK .mdb"
> and got the same error as above.
>
> Meanwhile, if I substitute an Access database which does
> not have linked tables into the same code, the connection
> works fine. So, any ideas? I have no access to the Oracle
> servers and no support from the Oracle DBAs, so patches to
> Oracle (which have been recommended before) are not an
> option.
>
> Thanks much for any help you can offer!
> Brian

Mark,

I do have a DSN set up. I am not aware of the "Oracle ODBC
Test program", however I have successfully accessed the
Oracle database through Access using the DSN I set up. I
tried the Oracle8 ODBC driver and also the Microsoft ODBC
driver for Oracle, and they both worked fine. So, I
successfully set up an Access database with linked tables
to Oracle, however I can't get the DSN to work with my ASP
page, nor can I connect to the linked tables using the
Access database I set up. Any ideas?

Thanks,
Brian

>-----Original Message-----
>First try setting up a DSN to the oracle database and see

if it works using

>the Oracle ODBC Test program. I suggest using the

Microsoft driver rather

>than the Oracle driver although you may have success with

the Oracle driver

>(we never have but it depends on the type of SQL

statements and recordsets

>that you need).
>
>Once you have the connection working in ODBC Test try the

same DSN

>connection in an ASP page. Here is what one of our

connection strings look

>like:
>
>dsn=oracle-wbtman1ms;uid=<userid>;pwd=<password>
>
>If that does not work and it works in ODBC Test then

there is probably a

>permission problem with the IUSR account.
>
>Once you have everything working through a DSN then you

can try converting

>to a DSN-less connection.
>
>--
>Mark Schupp
>Head of Development
>Integrity eLearning
>[url]www.ielearning.com[/url]
>
>
>"Brian" <anonymous@discussions.microsoft.com> wrote in

message

>news:05bc01c3ce2e$24a55ba0$a001280a@phx.gbl...

>> I am trying to connect to our corporate Oracle database

to

>> query some data in an ASP page. My experience with ASP

has

>> always been with SQL Server and Access databases, but I
>> figured I could get a connection string to work. I tried
>> using the following connection string:
>> "Driver={Microsoft ODBC for
>> Oracle};Server=PROD01;Uid=username;Pwd=password;"
>> and got the following error:
>> SQLAllocHandle on SQL_HANDLE_ENV failed
>>
>> I then set up an Oracle8 ODBC Driver connection and

tried

>> this:
>> "DSN=ORDERS;Uid=username;Pwd=password;"
>> and got this error:
>> Specified driver could not be loaded due to system error
>> 126
>>
>> Finally, I set up an Access database, created 2 linked
>> tables to my ODBC connection, tested them (I could open
>> them fine within Access) and tried this string:
>> "Provider=Microsoft.Jet.OLEDB.4.0; Data
>> Source=C:\inetpub\wwwroot\aftweb\ORDERS_LINK.mdb" and

got:

>> Microsoft JET Database Engine (0x80004005)
>> ODBC--connection to 'ORDERS' failed.
>> (ORDERS is the ODBC connection to the Oracle database)
>>
>> Also tried this one:
>> "Driver={Microsoft Access Driver
>> (*.mdb)};Dbq=C:\inetpub\wwwroot\aftweb\ORDERS_LINK .mdb"
>> and got the same error as above.
>>
>> Meanwhile, if I substitute an Access database which does
>> not have linked tables into the same code, the

connection

>> works fine. So, any ideas? I have no access to the

Oracle

>> servers and no support from the Oracle DBAs, so patches

to

>> Oracle (which have been recommended before) are not an
>> option.
>>
>> Thanks much for any help you can offer!
>> Brian

>
>
>.
>

[Brian]

I do not have an Oracle client installed on the web
server, but my DSN works fine with Access. Any ideas on
how I can get around the need for the client? I tried
setting up an Access database with linked tables, which
works fine, but I get errors connecting to it through my
ASP page.

Thanks again,
Brian

>-----Original Message-----
>On Mon, 29 Dec 2003 09:06:47 -0800, "Brian"

<anonymous@discussions.microsoft.com> wrote:

>

>>I am trying to connect to our corporate Oracle database

to

>>query some data in an ASP page. My experience with ASP

has

>>always been with SQL Server and Access databases, but I
>>figured I could get a connection string to work. I tried
>>using the following connection string:
>>"Driver={Microsoft ODBC for
>>Oracle};Server=PROD01;Uid=username;Pwd=password; "
>>and got the following error:
>>SQLAllocHandle on SQL_HANDLE_ENV failed
>>
>>I then set up an Oracle8 ODBC Driver connection and

tried

>>this:
>>"DSN=ORDERS;Uid=username;Pwd=password;"
>>and got this error:
>>Specified driver could not be loaded due to system error
>>126
>>
>>Finally, I set up an Access database, created 2 linked
>>tables to my ODBC connection, tested them (I could open
>>them fine within Access) and tried this string:
>>"Provider=Microsoft.Jet.OLEDB.4.0; Data
>>Source=C:\inetpub\wwwroot\aftweb\ORDERS_LINK.mdb " and

got:

>>Microsoft JET Database Engine (0x80004005)
>>ODBC--connection to 'ORDERS' failed.
>>(ORDERS is the ODBC connection to the Oracle database)
>>
>>Also tried this one:
>>"Driver={Microsoft Access Driver
>>(*.mdb)};Dbq=C:\inetpub\wwwroot\aftweb\ORDERS_LI NK.mdb"
>>and got the same error as above.
>>
>>Meanwhile, if I substitute an Access database which does
>>not have linked tables into the same code, the

connection

>>works fine. So, any ideas? I have no access to the

Oracle

>>servers and no support from the Oracle DBAs, so patches

to

>>Oracle (which have been recommended before) are not an
>>option.
>>
>>Thanks much for any help you can offer!
>>Brian

>
>1: Is an Oracle client installed on the web server? ( If

not, read no further, you cannot connect to Oracle without
one)

>
>2: Try using a dns-less connection like:
> myconn= Server.CreateObject("ADODB.Connection");
> myconn.ConnectionTimeout = 15;
> myconn.CommandTimeout = 30;
>
>myConnString="Provider=MSDAORA;Password=youroracl epassword

;UserID=youroracleusernamet;DataSource=yourINSTANC E
(tnsnames.ora

>alias);"
> myconn.ConnectionString=myConnString
> myconn.Open;
>
>This is more reliable and avoids many permissions issues

involved in reading the DSN from the server.

>
>hth
>.
>

[Turkbear]

I assume the Access database is on a server that does have the Oracle client..The issue seems to be that you cannot pass
the Oracle authentication to the Access database to use with its link to Oracle.
Check the properties of the DSN ( or the link) on the Access machine and see if you can save the username/password
combination so that it would not 'prompt' for it..Not sure it will work, however..

Without an Oracle client ( to get SqlNet ) and absent Java ( which can use a thin-client connection) you will not be
able to connect directly to any Oracle database, although there are some ODBC drivers from DirectConnect ( I think) that
do not need an Oracle client ( they use something called Wire protocol - similar to Java's thin client).




On Mon, 29 Dec 2003 10:05:48 -0800, "Brian" <anonymous@discussions.microsoft.com> wrote:

>I do not have an Oracle client installed on the web
>server, but my DSN works fine with Access. Any ideas on
>how I can get around the need for the client? I tried
>setting up an Access database with linked tables, which
>works fine, but I get errors connecting to it through my
>ASP page.
>
>Thanks again,
>Brian
>

>>-----Original Message-----
>>On Mon, 29 Dec 2003 09:06:47 -0800, "Brian"

><anonymous@discussions.microsoft.com> wrote:

>>

>>>I am trying to connect to our corporate Oracle database

>to

>>>query some data in an ASP page. My experience with ASP

>has

>>>always been with SQL Server and Access databases, but I
>>>figured I could get a connection string to work. I tried
>>>using the following connection string:
>>>"Driver={Microsoft ODBC for
>>>Oracle};Server=PROD01;Uid=username;Pwd=password ;"
>>>and got the following error:
>>>SQLAllocHandle on SQL_HANDLE_ENV failed
>>>
>>>I then set up an Oracle8 ODBC Driver connection and

>tried

>>>this:
>>>"DSN=ORDERS;Uid=username;Pwd=password;"
>>>and got this error:
>>>Specified driver could not be loaded due to system error
>>>126
>>>
>>>Finally, I set up an Access database, created 2 linked
>>>tables to my ODBC connection, tested them (I could open
>>>them fine within Access) and tried this string:
>>>"Provider=Microsoft.Jet.OLEDB.4.0; Data
>>>Source=C:\inetpub\wwwroot\aftweb\ORDERS_LINK.md b" and

>got:

>>>Microsoft JET Database Engine (0x80004005)
>>>ODBC--connection to 'ORDERS' failed.
>>>(ORDERS is the ODBC connection to the Oracle database)
>>>
>>>Also tried this one:
>>>"Driver={Microsoft Access Driver
>>>(*.mdb)};Dbq=C:\inetpub\wwwroot\aftweb\ORDERS_L INK.mdb"
>>>and got the same error as above.
>>>
>>>Meanwhile, if I substitute an Access database which does
>>>not have linked tables into the same code, the

>connection

>>>works fine. So, any ideas? I have no access to the

>Oracle

>>>servers and no support from the Oracle DBAs, so patches

>to

>>>Oracle (which have been recommended before) are not an
>>>option.
>>>
>>>Thanks much for any help you can offer!
>>>Brian

>>
>>1: Is an Oracle client installed on the web server? ( If

>not, read no further, you cannot connect to Oracle without
>one)

>>
>>2: Try using a dns-less connection like:
>> myconn= Server.CreateObject("ADODB.Connection");
>> myconn.ConnectionTimeout = 15;
>> myconn.CommandTimeout = 30;
>>
>>myConnString="Provider=MSDAORA;Password=yourorac lepassword

>;UserID=youroracleusernamet;DataSource=yourINSTAN CE
>(tnsnames.ora

>>alias);"
>> myconn.ConnectionString=myConnString
>> myconn.Open;
>>
>>This is more reliable and avoids many permissions issues

>involved in reading the DSN from the server.

>>
>>hth
>>.
>>

[Mark Schupp]

Oracle ODBC Test should have been installed with your oracle client software
under "Network Administration" (that is where the Net8 configuration program
is).

I don't know what Access might be doing differently with a DSN connection
but if it is working I would suspect a permission issue. Try setting the
anonymous account for the web site temporarily to "Administrator", restart
IIS, and see if that makes a difference.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


<anonymous@discussions.microsoft.com> wrote in message
news:018b01c3ce35$87234510$a101280a@phx.gbl...

> Mark,
>
> I do have a DSN set up. I am not aware of the "Oracle ODBC
> Test program", however I have successfully accessed the
> Oracle database through Access using the DSN I set up. I
> tried the Oracle8 ODBC driver and also the Microsoft ODBC
> driver for Oracle, and they both worked fine. So, I
> successfully set up an Access database with linked tables
> to Oracle, however I can't get the DSN to work with my ASP
> page, nor can I connect to the linked tables using the
> Access database I set up. Any ideas?
>
> Thanks,
> Brian
>

> >-----Original Message-----
> >First try setting up a DSN to the oracle database and see

> if it works using

> >the Oracle ODBC Test program. I suggest using the

> Microsoft driver rather

> >than the Oracle driver although you may have success with

> the Oracle driver

> >(we never have but it depends on the type of SQL

> statements and recordsets

> >that you need).
> >
> >Once you have the connection working in ODBC Test try the

> same DSN

> >connection in an ASP page. Here is what one of our

> connection strings look

> >like:
> >
> >dsn=oracle-wbtman1ms;uid=<userid>;pwd=<password>
> >
> >If that does not work and it works in ODBC Test then

> there is probably a

> >permission problem with the IUSR account.
> >
> >Once you have everything working through a DSN then you

> can try converting

> >to a DSN-less connection.
> >
> >--
> >Mark Schupp
> >Head of Development
> >Integrity eLearning
> >[url]www.ielearning.com[/url]
> >
> >
> >"Brian" <anonymous@discussions.microsoft.com> wrote in

> message

> >news:05bc01c3ce2e$24a55ba0$a001280a@phx.gbl...

> >> I am trying to connect to our corporate Oracle database

> to

> >> query some data in an ASP page. My experience with ASP

> has

> >> always been with SQL Server and Access databases, but I
> >> figured I could get a connection string to work. I tried
> >> using the following connection string:
> >> "Driver={Microsoft ODBC for
> >> Oracle};Server=PROD01;Uid=username;Pwd=password;"
> >> and got the following error:
> >> SQLAllocHandle on SQL_HANDLE_ENV failed
> >>
> >> I then set up an Oracle8 ODBC Driver connection and

> tried

> >> this:
> >> "DSN=ORDERS;Uid=username;Pwd=password;"
> >> and got this error:
> >> Specified driver could not be loaded due to system error
> >> 126
> >>
> >> Finally, I set up an Access database, created 2 linked
> >> tables to my ODBC connection, tested them (I could open
> >> them fine within Access) and tried this string:
> >> "Provider=Microsoft.Jet.OLEDB.4.0; Data
> >> Source=C:\inetpub\wwwroot\aftweb\ORDERS_LINK.mdb" and

> got:

> >> Microsoft JET Database Engine (0x80004005)
> >> ODBC--connection to 'ORDERS' failed.
> >> (ORDERS is the ODBC connection to the Oracle database)
> >>
> >> Also tried this one:
> >> "Driver={Microsoft Access Driver
> >> (*.mdb)};Dbq=C:\inetpub\wwwroot\aftweb\ORDERS_LINK .mdb"
> >> and got the same error as above.
> >>
> >> Meanwhile, if I substitute an Access database which does
> >> not have linked tables into the same code, the

> connection

> >> works fine. So, any ideas? I have no access to the

> Oracle

> >> servers and no support from the Oracle DBAs, so patches

> to

> >> Oracle (which have been recommended before) are not an
> >> option.
> >>
> >> Thanks much for any help you can offer!
> >> Brian

> >
> >
> >.
> >