System resource exceeded with linked ODBC MS SQL 2000 table

[jrajotte]

Hi,

I have a simple query that reads from a linked Access table a bunch of
records (300 000) and append to a linked ODBC MS SQL 2000 table. With
this configuration, I receive the error "System resource exceeded". If
I replace the linked ODBC table by a linked Access table, it works
without error and I can append many hundred thousands of records at a
time.

Can someone explain me what is happening,
Tx

 

[Rick Brandt]

Hi,

I have a simple query that reads from a linked Access table a bunch of
records (300 000) and append to a linked ODBC MS SQL 2000 table. With
this configuration, I receive the error "System resource exceeded".
If I replace the linked ODBC table by a linked Access table, it works
without error and I can append many hundred thousands of records at a
time.

Can someone explain me what is happening,
Tx

That append is being logged on the SQL Server and the transaction log might be
running out of room. Generally DTS FROM SQL Server is better for bulk moves
like this because they are not logged. Doing it with append queries you might
have to break it up into several smaller chunks.