ACC2: Errors Exporting or Appending Many Records to SQL Server

• Microsoft Access 2.0
• Microsoft Access Upsizing Tools 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.



• You can divide the data into smaller recordsets in Microsoft Access 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.



• You can use a different method for adding data to Microsoft SQL Server 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

Additional query words: Upsizing Wizard

Keywords : kberrmsg kbusage OdbcSqlms
Version : 2.0
Platform : WINDOWS
Issue type : kbprb