I am using a DTS package to export an Oracle database to MS Access.
One of the tables consists of about 18 million records. I have
exported this table to SQL server in the past and not had a problem.
I can also export this data to a CSV file and it takes about 40
minutes.

The problem I am having is that the process of inserting records gets
slower and slower and requires more and more memory the longer it is
run for. I have let the process run for twenty hours and it got to
about 15 million records and ran out of memory. I have increased the
memory to 1GB and played about with the paging file, without any luck.
The same problem occurs when importing from any source, not just
Oracle.

The only work around I have found is to export the file to a CSV file,
then import the file manually using MS Access file import option. I
need this process to be completely automated, preferably using a DTS.

I assume that DTS uses the latest Jet OLEDB provider installed on the
machine. I am using Windows 2000 SP4, and have the Jet 4.0 SP7.

Please can anyone help me?

Hi

I've just installed SP3 for SQL Server 2000 and tried it again, still
have same problem.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Is the Access db on the same server as SQL Server? I can't believe you
would want to put 18 million rows in an Access db anyway. SQL Server loves
memory and once it grabs the memory it will not let it go unless the OS
specifically calls for it. If your trying to insert 18 million rows into
the Access DB it is most likely going to suck up ram as well until
everything is starved. Have you looked at perfmon and task manager to see
who is using all the ram?

--

Andrew J. Kelly
SQL Server MVP


"Chris Foster" <anonymous (AT) devdex (DOT) com> wrote