Cannot Access DB when web application is moved to new server [Answered]RSS

16 replies

Last post Jun 03, 2010 11:34 PM by MannyL

  • Cannot Access DB when web application is moved to new server

    May 30, 2010 12:02 PM|MannyL|LINK

    I want to move a web application I developed to one of my new servers (I host).  The web application uses an Access DB.  I use a CRUD application to Add/Modfiy/Delete records in the Access DB which works just fine on the current live site.  However, after I move the web application to one of my other servers (one is Windows Server 2008 and the other is Windows Server 2003) the CRUD application can no longer access the DB.  I get the error listed below.  The permissions on both the DB folder within the web app as well as permissions on the .mdb file are the same as the current live application where all of this functions just fine.  The current live application is running on another of my Windows 2003 Servers.

    ATAF 6.1.0 debug program
    Server information

    Server name http://danellispreads.com
    Server software Microsoft-IIS/7.0
    Meta base path /category/debug.asp
    Path info C:\dino\
    Testing database connection
     adodb.connection is OK
     
    Could not open database object with connection string
    Error Number= #-2147467259
    Error Desc.= [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
     
    Testing your table
     Could not open database BusinessCategory_tbl
    Error Number= #3704
    Error Desc.= Operation is not allowed when the object is closed.
    Could NOT modify data in your table BusinessCategory_tbl, there must be R/W/C rights in the directory where the database is located
    Error Number= #3704
    Error Desc.= Operation is not allowed when the object is closed. 

    Here is the .asp code used for the DEBUG page:

    <%
    On Error Resume Next
    QU = Chr(34)
    Qs = Chr(39)
    DSN =  "driver={microsoft access driver (*.mdb)}; dbq=" & server.mappath ("../fpdb/member.mdb")
    Table = "BusinessCategory_tbl"
    imagedir = "images/"
    %>
    <html>
    <head>
    <meta http-equiv="+Content-Type" content="text/html"charset="windows-1252">
    <title>ATAF 6.1.0 Debug page</title>
    </head>
    <body style="font-family: Tahoma; font-size: 8pt">
    <table style="font-family: Tahoma; font-size: 8pt">
    <tr><td colspan="2"><b><font size="5">ATAF 6.1.0 debug program</font></b></td></tr>
    <tr><td colspan="2"><b><font size="4">Server information</font></b></td></tr>
    <tr><td nowrap>Server name</td><td>http://<%=Request.ServerVariables("SERVER_NAME")%></td></tr>
    <tr><td nowrap>Server software</td><td><%=Request.ServerVariables("SERVER_SOFTWARE")%></td></tr>
    <tr><td nowrap>Meta base path</td><td><%=Request.ServerVariables("PATH_INFO")%></td></tr>
    <tr><td nowrap>Path info</td><td><%=Request.ServerVariables("APPL_PHYSICAL_PATH")%></td></tr>
    <tr><td colspan="2"><font size="4"><b>Testing database connection</b></font></td></tr>
    <tr><td></td><td>
    <%
    Err.Clear
    Set conntemp = server.CreateObject("adodb.Connection")
    If Err <> 0 Then
       Response.write "<font color="+qu+"#FF0000"+qu+" > "
       Response.Write "Could not create adodb object<br>"
       Response.Write "Error Number= #<b>" & Err.Number & "</b><br>"
       Response.Write "Error Desc.= <b>" & Err.Description & "</b><br>"
       Response.write "</font>"
    Else
        Response.Write "adodb.connection is OK<br>"
    End If
    %>
    </td></tr>
    <tr><td></td><td>
    <%
    Err.Clear
    conntemp.Open DSN
    If Err <> 0 Then
       Response.write "<font color="+qu+"#FF0000"+qu+">"
       Response.Write "Could not open database object with connection string " + DSN + "<br>"
       Response.Write "Error Number= #<b>" & Err.Number & "</b><br>"
       Response.Write "Error Desc.= <b>" & Err.Description & "</b><br>"
       Response.write "</font>"
    Else
        Response.Write "DSN connection is OK<br>"
        Response.Write "ADODB provider : " + conntemp.Provider + "<br>"
        Response.Write "ADODB connectionstring : " + conntemp.ConnectionString + "<br>"
    End If
    %>
    </td></tr>
    <tr><td colspan="2"><font size="4"><b>Testing your table</b></font></td></tr>
    <tr><td></td><td>
    <%
    Err.Clear
    Sqlstr = "SELECT * From " + Table
    Set rs = conntemp.Execute(Sqlstr)
    If Err <> 0 Then
       Response.write "<font color="+qu+"#FF0000"+qu+">"
       Response.Write "Could not open database " + Table + "<br>"
       Response.Write "Error Number= #<b>" & Err.Number & "</b><br>"
       Response.Write "Error Desc.= <b>" & Err.Description & "</b><br>"
       Response.write "</font>"
    Else
        Response.Write "Table " + Table + " exist and is OK<br>"
        Response.Write "Table definition"
        Response.Write "<Table style = "+qu+"font-family: Tahoma; font-size: 8pt"+qu+">"
        Response.Write "<tr><td>name</td><td>Type</td><td>Actual size</td><td>Value</td><td>Nummericscale</td>"
        For i = 0 To rs.Fields.Count - 1
         Response.Write "<tr>"
         Response.Write "<td>" + rs(i).Name + "</td>"
         Response.Write "<td>"
         Response.Write rs(i).Type
         Response.Write "</td>"
         Response.Write "<td>"
         Response.Write rs(i).ActualSize
         Response.Write "</td>"
         Response.Write "<td>"
         Response.Write rs(i).Value
         Response.Write "</td>"
         Response.Write "<td>"
         Response.Write rs(i).NumericScale
         Response.Write "</td>"
         Response.Write "</tr>"
       Next
       Response.Write "</table>"
    End If
    rs.Close
    Err.Clear
    CreateTableSql = "CREATE TABLE ATAFtestDB ( Pubit char(4) )"
    Set rs = conntemp.Execute(CreateTableSql)
    If Err <> 0 Then
       Response.write "<font color="+qu+"#FF0000"+qu+">"
       Response.Write "Could NOT modify data in your table " + Table +", there must be R/W/C rights in the directory where the database is located<br>"
       Response.Write "Error Number= #<b>" & Err.Number & "</b><br>"
       Response.Write "Error Desc.= <b>" & Err.Description & "</b><br>"
       Response.write "</font>"
    Else
       Response.Write "There is R/W/C rights in the table " + Table
         CreateTableSql = "DROP TABLE ATAFtestDB"
         Set rs = conntemp.Execute(CreateTableSql)
    End If
    %>
    </td></tr>
    <tr><td colspan="2"><font size="4"><b>Testing picture upload capability</b></font></td></tr>
    <tr><td></td><td>
    <%
     Response.Write "Your directory where you upload picture to is : http://"+ Request.ServerVariables("SERVER_NAME") + "/" + imagedir + "<br>"
    Err.Clear
    Set fs = CreateObject("Scripting.FileSystemObject")
    If Err <> 0 Then
       Response.write "<font color="+qu+"#FF0000"+qu+">"
       Response.Write "could NOT create FileSystem scriptiong Object<br>"
       Response.Write "Error Number= #<b>" & Err.Number & "</b><br>"
       Response.Write "Error Desc.= <b>" & Err.Description & "</b><br>"
       Response.write "</font>"
    Else
       Response.Write "Scripting FileSystemObject is OK<br>"
    End If
    Path = Mid(server.mappath(Request.ServerVariables("PATH_INFO")), 1, InStrRev(server.mappath(Request.ServerVariables("PATH_INFO")), "\")) + imagedir
    Path = Replace(Path, "/", "\")
    Path = Path + "ataftest.dat"
    Err.Clear
    Set ts = fs.CreateTextFile(Path, True)
    If Err <> 0 Then
       Response.write "<font color="+qu+"#FF0000"+qu+">"
       Response.Write "Could not write a file to the server, if you want to upload picture there must be R/W/C right in the directory where you upload your pictures to<br>"
       Response.Write "Error Number= #<b>" & Err.Number & "</b><br>"
       Response.Write "Error Desc.= <b>" & Err.Description & "</b><br>"
       Response.write "</font>"
    Else
       Response.Write "You can upload pictures to " + imagedir + "<br>"
       ts.Close
    End If
    %>
    </td></tr>
    </table>
    </body>
    </html>
     

  • Re: Cannot Access DB when web application is moved to new server

    May 30, 2010 12:37 PM|J_P|LINK

     the error messsage seems clear: "Could not open database object with connection string "

    Check the path to the database on the new server and make sure your "DSN" is correct.

  • Re: Cannot Access DB when web application is moved to new server

    May 30, 2010 01:16 PM|MannyL|LINK

    Yes... I certainly agree the message seems clear.  However, as I indicated, the path to the DB is exactly the same on both servers,

    DSN =  "driver={microsoft access driver (*.mdb)}; dbq=" & server.mappath ("../fpdb/member.mdb")

    Can you please elaborate further on "make sure your DSN is correct"?

    Thanks

  • Re: Cannot Access DB when web application is moved to new server

    May 31, 2010 12:20 PM|J_P|LINK

     what I meant is simply to verify the path to the database is correct.  Aso, some servers are configured not to allow this type of " ../ " notation .

    It can be a security risk to allow pathnames above the domain root, so it is blocked.  Try using the full physical path and see if it works.

     

     

  • Re: Cannot Access DB when web application is moved to new server

    May 31, 2010 01:09 PM|MannyL|LINK

    The path to the database is correct.  It is identical to the path on the live server.  The path on the liver server also uses ..fpdb/member.mdb just like on the new server.  I have tried using the full UNC path including C:\ however, that fails as well.

  • Re: Cannot Access DB when web application is moved to new server

    May 31, 2010 02:42 PM|J_P|LINK

     try putting a test copy / sample database in the same folder as the script and change the connection string pathname accordingly.

     

    See if that works...

  • Re: Cannot Access DB when web application is moved to new server

    May 31, 2010 03:02 PM|MannyL|LINK

    Yes, that works.  If I copy the database to the same folder as my CRUD application and change the path from ../fpdb/members.mdb to members.mdb, edit the permissions on the CRUD folder for IUSR to have Modify permissions,  it works fine.  However, as I have 3 CRUD applications hitting the database, each in their seperate folds they each need to access the database in the ../fpdb folder as they do on the live server where this is working just fine.

  • Re: Cannot Access DB when web application is moved to new server

    May 31, 2010 09:38 PM|J_P|LINK

     This tells me the problem is not an ASP issue.  It sounds like an IIS issue. 

     You will need to ask the server administrator why the preferred folder is unusable.

  • Re: Cannot Access DB when web application is moved to new server

    Jun 01, 2010 10:04 AM|MannyL|LINK

    I am the server administrator.  The servers are mine, sitting under my desk.  So, I am still at a loss as to what else I need to check.  This all works just fine on my other server with exactly the same configuration.  Any suggestions or help as to what to check for besides what has already been mentioned would be greatly appreciated.

  • Re: Cannot Access DB when web application is moved to new server

    Jun 01, 2010 02:02 PM|J_P|LINK

     Sometimes access will lock the database for usage then fails to clear the lock file. 

    Make sure there is no "loc" file in the folder ("../fpdb/member.mdb")

  • Re: Cannot Access DB when web application is moved to new server

    Jun 01, 2010 02:22 PM|MannyL|LINK

    That was the first thing I checked for.  There is no loc file.

  • Re: Cannot Access DB when web application is moved to new server

    Jun 01, 2010 09:21 PM|MannyL|LINK

    Wow...that's a ton of information, thanks  I have gone through all that applies and yet no luck. 

    I can see that the permissions on the DB folder are indeed correct because if I take my debug.asp page out of the subfolder it presently resides in and place it in the root of my web application, change the path to the DB from ../fpdb/member.mdb to fpdb/member.mdb it all works (see below).  However, I am using 3 different CRUD apps and cannot place the pages in the root of the web app.  Not to mention, this all works like a charm on the live server.

    Testing database connection
    adodb.connection is OK
    DSN connection is OK
    ADODB provider : Microsoft.Jet.OLEDB.4.0
    ADODB connectionstring : Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=C:\monkeyman\gkcchamber\fpdb\member.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False
    Testing your table
    Table calendar2_tbl exist and is OK
    Table definition
    name Type Actual size Value Nummericscale
    ID 3 4 8 255
    EventDate 7 8 7/8/2008 255
    Location 202 138 NW University's Graduate & Prof. Studies Bldg. - 6710 108th Avenue NE 255
    SubjectTitle 202 56 Business Networking Luncheon 255
    Description 203 382 Business Networking Luncheon is held every second Tuesday of the month at the Northwest University’s Graduate & Professional Studies Bldg (accross from the Houghton Starbucks) from 11:15-1pm. 255
    ReservationInformation 202 132 For reservations contact Judi. 255
    Contact 202 50 255
    EventCost 202 56 $12/Members; $15 Non-members 255
    StartTime 202 22 11:15:00 AM 255
    EndTime 202 20 1:00:00 PM 255
    xsubmitted 7 8 2/25/2008 10:53:08 PM 255
    There is R/W/C rights in the table calendar2_tbl

  • Re: Cannot Access DB when web application is moved to new server

    Jun 02, 2010 10:48 PM|Leo Tang - MSFT|LINK

    Hi,

    Have you enabled the Parents Paths(IIS Manager->ASP->Enable Parent Paths)?

    Please mark the replies as answers if they help or unmark if not.
    Feedback to us


  • Re: Cannot Access DB when web application is moved to new server

    Jun 02, 2010 11:46 PM|MannyL|LINK

    YOU ROCK!!!!!!!  That was exactly the issue.  Thanks so much!!!

  • Re: Cannot Access DB when web application is moved to new server

    Jun 03, 2010 02:36 PM|tomkmvp|LINK

    That was the other 1.77%  : )

  • Re: Cannot Access DB when web application is moved to new server

    Jun 03, 2010 11:04 PM|J_P|LINK

     I pointed out the problem of "../ " notation (parent path), not being allowed on some servers, back on May 31.

    sheeeze !

     

  • Re: Cannot Access DB when web application is moved to new server

    Jun 03, 2010 11:34 PM|MannyL|LINK

    Indeed you did.. However, the full path did not work as I indicated.