Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 137115 - Last Review: February 5, 2002 - Revision: 1.0
ACC2: Errors Exporting or Appending Many Records to SQL Server
This article was previously published under Q137115
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]
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:
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.
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.
To determine whether the error message is caused by running out of locks on
Microsoft SQL Server, follow these steps:
- 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
- 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.
Microsoft SQL Server "Language Reference," version 4.2, pages 471-472
APPLIES TO
- Microsoft Access 2.0 Standard Edition
- Microsoft Access Upsizing Tools 1.0
| kberrmsg kbprb kbusage KB137115 |
Retired KB Content DisclaimerThis article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate