DTS Export Wizard (SQL to Access 2000) fails

ctudose · June 12th, 2003, 09:26 PM

Hi all,
I have a SQL Server table with 1.2 milion records.
All fields are char type with max record length 1066 bytes .
I need to export this table into Access 2000.I'm using DTS Export Wizard and after 500,000 records the process fails with an error:
".... Invalid argument"
The Access database reaches maximum size 2GB.
If I export the table as a text file (size of the file 1.2GB) and then importing into Access , everything seems to be OK and the size is 600MB
I want to avoid exporting the table as a text file and then importing into Access.
Why DTS fails ??
Any suggestion woulg be much appreciated.

Thanks,
Cris

saint · June 13th, 2003, 02:26 AM

well firstly u got to understand that Access is not really that capable of handling 1.2 million entries. When u actually do a DTS export, the properties of the table are being exported as well.

Access does have a properties storage cap and it is not advised to use access as yr database.

If its aint broken dont fix it.

ctudose · June 13th, 2003, 05:46 AM

First: the output must go in Access 2000 format.
Can you be more specific regarding the way DTS it's working ?
Let's say I have a SQL table(only char type fields,no indexes,no PK) - 1.2 mil. records, 1GB
When I am using DTS Export Wizard to Access 2000 what else can be exported to reach 2GB only after 500,000 records ?
Thanks