• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

mdac upgrade from 1.5 to 2 problem

Since we have upgraded to mdac 2 from version 1.5 that come with “MS Option pack” we getting errors in asp pages when we call a stored procedure on an MS SQL 6.5 server that return more than one record set. The error message is:

error '80004005'
Unspecified error

on the line were we assign the second record set (“Set rs = rs.NextRecordSet”). Is there a way to fix this or how can I go back to mdac 1.5 without re-installing the whole server.
1 Solution
Try this....

This article presents details for common causes of the 0x800040005 error received by Microsoft Data Access Components (MDAC), including ActiveX Data Objects, OLE DB and the Remote Data Service (RDS). Several other error messages are discussed, including, 80040e21, 80040e14, and 80040e10.

The 80004005 error message can be summarized as "I couldn't get at your data for some reason". This article contains a listing of the various 80004005 error messages, the most frequent causes of the error messages, and troubleshooting steps to resolve them. While this article assumes you are using ActiveX Data Objects (ADO) within an Active Server Pages page (.asp), the causes and many of the troubleshooting steps are applicable to any environment where ODBC is used for data access.

Error Message Listing
This section presents the text of each error message and the causes of each error.

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC Microsoft Access 97 Driver]
   The Microsoft Jet database engine cannot open the file '(unknown)'.
   It is already opened exclusively by another user, or you need
   permission to view its data.


      - This error may occur if the account being used by Internet
        Information Server (IIS), (usually IUSR), does not have the correct
        Windows NT permissions for a file based database or for the folder
        containing the file.

      - Check the permissions on the file and the folder. Ensure that you
        have the ability to create and/or destroy any temporary files.
        Temporary files are usually created in the same folder as database,
        but the file may also be created in other folders such as /Winnt.

      - Use the Windows NT File Monitor to check for file access failures.
        Windows NT File Monitor is available from the following Web site:


      - If you use a network path to the database (UNC or mapped drive),
        check the permissions on the share, the file and the folder.

      - Check to make sure that the file and the data source name (DSN)
        are not marked as Exclusive.

      - Simplify. Use a System DSN that uses a local drive letter. Move
        the database to the local drive if necessary to test.

      - The "other user" might be Visual InterDev. Close any Visual
        InterDev projects that contain a data connection to the database.

      - The error may be caused by a delegation issue. Check the
        authentication method (Basic versus NTLM) if any. If the connection
        string uses the Universal Naming Convention (UNC), try using Basic
        Authentication or an absolute path such as C:\Mydata\Data.mdb. This
        may happen even if the UNC points to a resource local to the IIS

      - An Odbcjt32.dll bug.  The network access of a Microsoft Access
        .mdb file fails. Move the .mdb file to the local Web server hard

   Network paths such as UNC's should work for Basic / Anonymous access,
   but they do not work when you use the updated JET driver under IIS 4.0.
   This is because the user security context is switched to SYSTEM when it
   reads the database. The system account cannot access networked
   resources. Move the database to the Web server and use a local path. For
   Visual InterDev to work from a remote workstation in this situation, set
   up the system DSN's as described in the Microsoft Knowledge Base article
   listed in the REFERENCES section. The article was written to show how to
   work around delegation problems on authenticated projects, but the fix
   is applicable to this problem also.

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't use '(unknown)';
   file already in use.


      - The database cannot be locked correctly for multiple users.

   For more information, please refer to the Microsoft Knowledge Base
   article, Q174943, listed in the REFERENCES section.

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC Driver Manager]
   Data source name not found and no default driver specified.

      - The most common cause is that the connection string is a session
        variable initialized in the Global.asa and Global.asa is not
        firing. You may check to see that the variable is being initialized
        correctly by adding the following code to the .asp page:

           <%= "'auth_user' is " & request.servervariables("auth_user")%>
           <%= "'auth_type' is " & request.servervariables("auth_type")%>
           <%= "connection string is " & session("your_connectionstring")%>

      - If the Global.asa is not firing, check to make sure it is in an
        Application Root for IIS 4.0, or a Virtual Root with the "Execute"
        check box selected if running under IIS 3.0. Also, a bug detailed
        in the Microsoft Knowledge Base article Q173742, may prevent the
        Global.asa from being fired when Windows NT permissions have
        restricted access to the folder.

      - The DSN name is not found. Check to make sure a 'User' type DSN
        is not being used.

      - If using a File or System DSN, try changing the connection string
        to "DSN=MySystemDSN" or "DBQ=MyFileDSN" as appropriate.  Simplify!

      - Check to make sure that the most current drivers are installed.
        If in doubt, download the latest MDAC (Microsoft Data Access
        Components) from the following Web site:


   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC Driver Manager] Data source name not ??


   This appears to be an issue with the order in which software is
   installed and uninstalled on the computer. If the ODBC core files become
   unsynchronized (they should all be the same version) you may see this

   Install the latest version of MDAC (Microsoft Data Access Components)
   from the following Web site to update all the core ODBC drivers:


   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC Access 97 ODBC driver Driver]General error Unable to
   open registry key 'DriverId'.


   This error is caused by reading a value from the registry. Check the
   permissions on the registry key using the registry editor, Regedt32.exe.
   You may also wish to use the Windows NT Registry Monitor to check for
   registry read failures. NTRegMon may be downloaded from the following
   Web site:


   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC SQL Server Driver][dbnmpntw]ConnectionOpen


   There are two causes for this error. Both errors are permission related
   issues and involve a database that is either on a different computer
   from the Web server, or the database is possibly being referenced using
   a UNC path (\\Server\Share). Even if the database is on the same
   computer as the Web server, UNC paths make the database appear to the
   Web server to be on a different computer on the network.

      1. The following is true if the .asp page is accessed by an anonymous

         IIS will use (by default) a Windows NT account called
         USR_<machinename>. This account is local to the Web server and is
         essentially unknown to any other computers on the network. When
         IIS, operating under the security context of the IUSR account,
         tries to access any resources on a remote computer, the remote
         computer tries to validate the account being used. Since the IUSR
         account is a local account that is unknown to the remote computer,
         access is denied.

         There are two solutions when anonymous access causes this problem:

            a. In the Internet Service Manager tool, under Web Properties,
               change the anonymous logon account from the default local
               account to a valid domain based account. In the UserName
               field, enter the domain account to use as 'domain\userid'.
               This way, the remote computer can check with the domain
               controller to validate the security credentials passed to it
               by IIS.

            b. Duplicate the IUSR_<machineaccount> account on the remote
               computer that contains the resource you wish to access. If
               an account is created on the remote computer that has the
               exact same name and password, Windows NT treats them as
               equivalent accounts.

      2. If the .asp page only allows authenticated access, please refer to
         the following:

         If the page does not allow anonymous access, IIS tries to
         authenticate the user making the request, and uses their security
         credentials for all activities such as database accesses. The two
         primary causes for a failure in this scenario are described below.

            a. By default, IIS is configured to use Windows NT
               Challenge/Response as the authentication method. Because of
               limitations in the Windows NT 4.0 (and prior versions)
               security model, a user that has been authenticated using
               Windows NT Challenge/Response cannot access resources on
               remote computers. This is commonly referred to as a
               delegation problem. To verify if this is the case, in the
               Internet Services Manager tool, under Web properties, select
               the Basic (Clear Text) check box and deselect the Windows NT
               Challenge/Response. If this solves the problem, then this is
               a clear delegation issue.

            b. If problems persist, it is likely that the User account
               being used does not have rights to the SQL Windows NT
               computer. Try using an account that has known access to the
               SQL computer.

         For more information concerning delegation, how IIS uses
         authentication to secure a Web site, and problems such as this,
         please see the article "IIS Authentication and Security for
         Internet Developers" located at this Web address:


   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   Microsoft][ODBC Microsoft SQL Driver] Logon Failed()


   This error is generated by SQL server if it does not accept or recognize
   the logon account and/or password being submitted (if using Standard
   security) or if there is no Windows NT account to SQL account mapping
   (when using Integrated security).

      - If you are using standard security, the SQL account name and
        password are incorrect. Try the system Admin account and password
        (UID= "SA" and NULL password). These must be defined on the
        connection string line. DSN's do not store user names and

      - If using Integrated security, check the Windows NT account that is
        calling the page, and find out what account (if any) it is mapped

      - SQL does not allow an underscore in a SQL account name. If someone
        manually mapped the Windows NT IUSR_machinename account to a SQL
        account of the same name, it fails. Map any account that uses an
        underscore to an account name on SQL that does not use the

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC SQL Server Driver][SQL Server] Login failed- User:
   Not defined as a valid user of a trusted SQL Server connection.


      - Integrated Security is turned on in the SQL Enterprise Manager, and
        the Windows NT account being used has not been mapped to a SQL

      - Try changing SQL to use Standard Security (In Enterprise Manager,
        select Server/ SQL Server/Configure[ASCII 133]/Security

      - If running under IIS 4.0, turn off "Password Synchronization"
        for that project.

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC Microsoft Access 97 Driver] Couldn't lock file.


      - May be caused by not having correct rights to create the lock file
        .ldb) for a Microsoft Access database. By default, a lock file is
        created in the same folder as the Microsoft Access .mdb file.

      - Try giving the user accessing the database (usually the
        IUSR_machinename account) full control to the share/folder.

      - Sometimes, permissions are restricted on the share because the file
        is intended to be read only. You may want to try changing the
        connection mode using the following code example:

           Set Conn = Server.CreateObject("ADODB.Connection")
           Conn.Mode = adModeShareDenyWrite    '8
           'From Adovbs.inc
           '---- ConnectModeEnum Values ----
           'Const adModeUnknown = 0
           'Const adModeRead = 1
           'Const adModeWrite = 2
           'Const adModeReadWrite = 3
           'Const adModeShareDenyRead = 4
           'Const adModeShareDenyWrite = 8
           'Const adModeShareExclusive = &Hc
           'Const adModeShareDenyNone = &H10

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC Microsoft Access 97 Driver]
   '(unknown)' isn't a valid path. Make sure that the path name is spelled
   correctly and that you are connected to the server on which the file


      - The path being read by the Web server is not a valid path. This
        most commonly happens when the Global.asa is being used and the
        connection string was created on a different computer than the Web
        server. If the path is a mapped drive letter, it is probably only
        valid for the client computer that created the connection string.

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC SQL Server Driver][SQL Server]
   The query and the views in it exceed the limit of 16 tables.


      - The query is too complex. There are several limitations on a

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC SQL Server Driver][DBNMPNTW]
   ConnectionWrite (GetOverLappedResult()).


      - When the allow Anonymous User context is turned off, Windows NT is
        closing the pipe to SQL Server after the first request is complete.
        This is because the first connection to SQL Server is made under
        the IIS Anonymous User account. IIS then either impersonates the
        browser client on that same thread, or tries to access the
        connection on a different thread that is running in the
        impersonated user context. In either case, Windows NT would detect
        the attempt to use a network named pipe handle that had been opened
        in a different user context and force the pipe closed, per it's
        security rules. When the connections are viewed on the SQL Server
        with a network monitor, a name pipe close request comes from
        Windows NT, causing the error in the Web browser.

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80004005'
   [Microsoft][ODBC SQL Server Driver][DBMSSOCN]
   General network error. Check your network document


   This may occur when a SQL server computer is renamed. DSN's that
   reference the old name fail when the computer name cannot be located.

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
   Errors occurred


   This may be caused by trying to insert more data into a field than is
   allowed. For example, inserting 26 characters into a Microsoft Access
   field that is formatted to accept only 25 characters.

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
   [Microsoft][ODBC Microsoft Access 97 Driver]
   Syntax error in INSERT INTO statement.


   A column name may be a reserved word, such as DATE. Change the column
   name to a non-reserved name such as 'SaleDate'.

   Error Message

   Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
   [Microsoft][ODBC Microsoft Access 97 Driver]
   Too few parameters. Expected 1.


   The column name used in the query syntax does not exist.  Often this is
   just a typographical error. Check the column names in a database
   against your query string. If you are using Microsoft Access, make sure
   that the actual column name is used and not a column's "display" name.

For information on getting Visual InterDev to work from a remote workstation, set up the system DSN's as described in the following Microsoft Knowledge Base article:

   ARTICLE-ID: Q178215
   TITLE     : HOWTO: Configure VID to Work with an Authenticated Web

For additional information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q174943
   TITLE     : PRB: 80004005 "Couldn't Use '(unknown)'; File Already in

   ARTICLE-ID: Q173742
   TITLE     : BUG: Global.asa Not Executed If Restricting Web Access

   ARTICLE-ID: Q172864
   TITLE     : ASP Pages Fail on Access to Session and Application Objects

   ARTICLE-ID: Q156526
   TITLE     : General Error=51 Connecting to an Access Datasource

   ARTICLE-ID: Q175671
   TITLE     : PRB: 80004005 ConnectionOpen (CreateFile()) Error Accessing

   ARTICLE-ID: Q149425
   TITLE     : IDC: Error Performing Query, Not Defined as a Valid User

   ARTICLE-ID: Q167452
   TITLE     : PRB: 'Not a valid path' Error when Using Access Data Source

   ARTICLE-ID: Q125767
   TITLE     : PRB: Query Too Complex Error After Execution of SQL Query

   ARTICLE-ID: Q166659
   TITLE     : PRB: Accessing SQL Database Fails on Second Attempt

   ARTICLE-ID: Q166029
   TITLE     : PRB: Cannot Open File Unknown Using Access

   ARTICLE-ID: Q178215
   TITLE     : HOWTO: Configure VID to Work with an Authenticated Web

lodewykAuthor Commented:
I’ve seen this document already on MSDN but I don’t think it is any of the above problems. I rather think it’s a bug or compatibility problem with the new version of MDAC or the SQL ODBC driver that come with MDAC 2, because it worked fine before the upgrade. We have also detected some other problems like this:
“[Microsoft][ODBC SQL Server Driver][SQL Server]Updated or inserted row is bigger than maximum size (32 bytes) allowed for this table.”
When we call the same Store Procedure from the “SQL Query Tool” within SQL Enterprise Manager it work fine and it will even work for a while fine on the site that were having the original problem, but after a while it will give the same error message. On the other hand when we run it on a server with still the original MDAC and ODBC version that came with MS Option pack we don’t get any of these problems.

Is there any way to UN-INSTALL MDAC 2 and it's ODBC drivers. I already re-installed MS Option Pack.

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.

This is the Community Support link, if help is needed, along with the link to All Topics since many new ones were recently added.

Moderator @ Experts Exchange
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now