ACC2: Errors Exporting or Appending Many Records to SQL Server

• Microsoft Access version 2.0
• Microsoft Access Upsizing Tools version 1.0

SYMPTOMS

When you export or append a large amount of data to Microsoft SQL Server (using the Export command, an action query, or the Upsizing Tools), you may receive an error message such as

   ODBC Call Failed.
   [Microsoft][ODBC.DLL] ODBC Function Sequence Error [#0]

   -or-

   Microsoft [ODBC SQL Server Driver] rollback transaction
   request has no corresponding BEGIN TRANSACTION [#3093]

CAUSE

There are several possible causes for the error message:

• You may have exceeded the number of available locks on the server. Because the Microsoft Access Upsizing Tools exports or appends data within a transaction, the locks accumulate until the transaction is committed or rolled back.

  To determine the number of locks available on the server, you can run the following Microsoft SQL server stored procedure:

        sp_configure "locks"
  

  For more information about the sp_configure stored procedure, see your Microsoft SQL Server documentation.

• You may have run out of available space for the Microsoft SQL Server database.
• You may have filled the Microsoft SQL Server transaction log to capacity.

RESOLUTION

To resolve the error message, try the following:

- You can increase the number of locks available on your Microsoft SQL

  Server. For more information about this procedure, see your Microsoft SQL
  Server documentation.

  before exporting or appending to Microsoft SQL Server. For example, if
  you have a table with 150,000 records, you can create 3 select queries
  with criteria that each returns 50,000 records. Then, export or append
  the queries instead of one large table.

  besides the Microsoft Access Upsizing Tools. First, export your data from
  Microsoft Access to a text file in a comma-delimited format. Then, use
  Microsoft SQL Server's bulk copy program (BCP) to copy the text file to a
  database table. For more information about the BCP utility program, see
  your Microsoft SQL Server documentation.

MORE INFORMATION

To determine whether the error message is caused by running out of locks on Microsoft SQL server, follow these steps:

1. While transferring data to Microsoft SQL Server (using the Export command, an action query, or the Upsizing Tools), run a query against the SQL Server's master database to determine the number of locks in use:

         Select count(*) from master..syslocks
   

2. Run this query a few times. Notice that the number of locks increases as long as Microsoft Access continues to transfer data to Microsoft SQL Server. If you receive an error message such as the ones listed in the "SYMPTOMS" section, then you have exceeded the number of locks available for your Microsoft SQL Server.

REFERENCES

Microsoft SQL Server "Language Reference," version 4.2, pages 471-472