Difficulty with ASP and Client Access ODBC driver -- MIDRANGE-L

Here's the scenario: I have some ASP (classic/3.0) pages that access AS/400
data. Everything works correctly on my machine, running a local installation
of IIS. I can access remote SQL server data or AS/400 data.

However, when I run the ASP pages on our application server, the ASP page
always "hangs" on the "open connection" line when retrieving AS/400 data. I
have Client Access installed on the application server.
The server can access the SQL server database, but not the AS/400 database.
The web page doesn't time out, but sits there for a very long time (I
stopped timing it after 5 minutes).

The script hangs no matter which type of AS/400 connection I try to use:
DSN: "DSN=mydsn;Uid=uid;Pwd=pwd"
DSN-less: "driver={Client Access ODBC Driver
(32-bit)};system=system;uid=uid;pwd=pwd"

I set up a standalone server running a default install of IIS and Client
Access, and have the same problem. It would appear to me to be a
configuration issue, but I'm not sure what to check.
I'm pretty new to ASP, I think I missed a configuration setting or piece of
client access installation somewhere on the server. All of our servers are
in the same network subnet.

If I need to provide more information, let me know.
Any help appreciated, thanks!

Loyd



Sorry for the long post, but here is the complete sample ASP page that
demonstrates the problem.

<%
option explicit
response.buffer = true
response.clear
server.scripttimeout = 30       ' seconds
%><html>
<head>
<style type="text/css">
<!--
body
{
        background-color: white;
        font-family: veranda, arial, sans-serif;
}
.false { background-color: #fed; }
.true { background-color: #dcb; }
// -->
</style>
</head>
<body>
<%
dim mydb, myconn, myrs, mysql, myarray, mycount, myloop
dim mycategoryid, mycategoryname, mycategorydescription, mycategorypicture
dim myfield, myfcount, myfieldloop, flipper

' ******************
' The getdata subroutine will turn any SQL select statement into a table.

' ******************
' SQL Server test

mydb = "driver={sql
server};server=etswvsvsql001;uid=loyd;pwd=loyd;database=PCS"
mysql = "select * from productgroups pg left outer join departments dp on
(dp.pg_id=pg.pg_id) left outer join areas ar on (ar.dept_id=dp.dept_id)
order by pg.pg_sortorder, dp.dept, ar.area_sortorder"

response.write "<b>SQL server test</b><br/>"

call getdata

' SQL server works fine on the app server.

' **********************
' AS/400 test

mydb = "driver={Client Access ODBC Driver
(32-bit)};system=10.121.17.7;uid=loyd;pwd=loyd"
mysql="select * from msdb1t.sts"

response.write "<b>AS/400 test</b><br/>"

call getdata

' End

sub getdata

response.write "Before createobject.<br/>"
response.flush
set myconn = server.createobject("adodb.connection")
response.write "After createobject.<br/>"
response.flush
response.write "Before open connection.<br/>"
response.flush
myconn.open mydb

' ******************* the script always hangs here on AS/400 data retrieval


response.write "After open connection.<br/>"
response.flush

response.write "Before execute.<br/>"
response.flush
set myrs = myconn.execute(mysql)
response.write "After execute.<br/>"
response.flush
if not myrs.eof then
        response.write "Get results.<br/>"
        myarray = myrs.getrows()
        response.write "<table cellspacing=""0"" cellpadding=""3"" frame
rules=""none""><tr>"
        for each myfield in myrs.fields ' List fields names in result set
                response.write "<th>" & myfield.name & "</th>"
        next
        response.write "</tr>" & vbcrlf
        mycount = ubound(myarray,2)     ' Number of rows returned
        myfcount = ubound(myarray,1)    ' Number of fields returned
        myloop = 0
        flipper = false
        do until myloop = mycount       ' Output each row
                if myloop mod 5 = 0 then        ' Show something every 5
rows
                        if response.isclientconnected() = false then
                                exit do
                        end if  ' stop script with cleanup if client
disconnected
                        response.flush
                end if  ' myloop mod 5 = 0
                response.write "<tr class=""" & flipper & """
valign=""top"">"
                for myfieldloop = 0 to myfcount ' Output each field
                        response.write "<td>"
                        if isnull(myarray(myfieldloop,myloop)) then
                                response.write "-"
                        else
                                response.write
trim(myarray(myfieldloop,myloop))
                        end if
                        response.write "</td>"
                next
                response.write "</tr>" & vbcrlf
                flipper = not flipper
                myloop = myloop + 1
        loop    ' myloop = mycount
        response.write "</table>" & vbcrlf
end if  ' not myrs.eof
response.write "Finished results.<br/>"
response.flush
myconn.close
set myconn = nothing
set myrs = nothing

end sub


%></body>
</html>


--  
Loyd Goodbar
Programmer/analyst
BorgWarner Incorporated
ETS/Water Valley
662-473-5713
lgoodbar@xxxxxxxxxxxxxx