Error Message: "system resource exceeded" in Access when trying to append data

sql server express 2005
access 2003

I have an access database that I'm using as  place to append data into my sql server express tables(via linked).

I have a connection to a network sql server 2003 =   Table1
connection to sqlserver express 2005 database =   Table2

Through access I'm trying to append from table1 to table2...
both tables are sql server tables.

908,000 Table1

I have succeeded in appending data with less records...with no problem.

Question :  Is there something in sql server express2005 that I can tweak to succeed in appending large data.

I know I can use the dts wizard...
But doing it through access I can automate the process...

Any suggestions ?

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
<<jdettman, Still got the "system resource exceeded" error.>>
Bummer.  Only other thing I can think of is to make sure that the environemtn variables TMP and TEMP point to valid drives/directories and that the drive they point to has plenty of free disk space (3-4 GB's).
However it may be the 2GB database size limit that your bumping into as 8080_Diver suggested, which there is no way of getting around in a single operation.
2 ideas:
1. try appending first to an access "Temporary Table" (empty access table) & from there append to the second table

2. Try appending in blocks- id up to 300,000 etc

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
IF your using an append query to do this, set the queries UseTransaction property to no.
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

FordraidersAuthor Commented:
Thanks but tried both..

jdettman, Ok...what does setting this proerty do...Never done that before ?
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<jdettman, Ok...what does setting this proerty do...Never done that before ?>>
  When you run a action query (Insert, Update, Delete), queries are set by default to use a transaction; do the whole query or rollback.  To do that, it takes locks out on everything it touches and run unit jornals all the changes (writes the records to a temp file).  
  Wehn you set use transaction to no, all that is bypassed.  Access/JET does things in pieces and doesn't consume anywhere near the same amount of resources.  But if you query fails for any reason, the changes are not rolled back.
  Setting that to no usually solves resource type problems with large queries.
FordraidersAuthor Commented:
jdettman, Still got the "system resource exceeded" error.

appending 980,000 records..
8080_DiverConnect With a Mentor Commented:
I think your problem is that, by using the Access database as your tool, you are reading the entire 980,000 rows of data into your local box before it tries to append them to the target table.
One option that you might want to explore is linking to the network SQL Server instance from your local instance or vice versa.  However, I am a bit confused by your reference to "SQL Server 2003" because SQL Server doesn't have a 2003 version.  Could it be that you are referencing a SQL Server 2000 instance?
You may want to consider upgrading your SQL Server 2005 to SQL Server 2008 because it has the more cvapabilities in this area.
You could check the following link and maybe get a better ability to do the transfer:
Also, is this a 1-time thing or do you need to do it periodically or every X time units (days, hours, weeks, whatever)?
Is this for a personal or a job situation?  Either way, the "Developer Edition" of SQL Server 2008 can be acquired for about $45 (from Amazon) and it has SSIS (among other cool things ;-) and if you are careful to only use the datatypes that SQL Server 2005 hase, you can transfer data between the two easily.
If you can get it, I would recommend the SS2008 Developer and using SSIS.
FordraidersAuthor Commented:
Thanks !
All Courses

From novice to tech pro — start learning today.