Extracting Lots of Data

[edlea]

I've been trying to pull about 40000 (forty thousand) records from a database (the database is about 2gig in total) using Merant's ODBC drivers to get the data into access. This causes Access to crash without fail. I was hoping that there would either be a solution to this, or possibly someone could suggest a Unix shell utility that can take an SQL statement as a parameter and then output the relevent data in some sort of deliminated format.

Many thanks.

 

[cmorgan]

If this is a one-time event, you might be better off writing a 4gl procedure to export the data to a text or csv file, and then loading that into Access.

What type of progress license do you have?

 

[edlea]

It's not, strictly speaking a one off procedure. It will probably want to be run every night to update data in the Access database.

I wouldn't know where to start writing a 4gl procedure, or what one even is - any pointers on where to start?

All I know about the Progress license is that its multi-user. It's Progress 8.3b running HP-UX 10

 

[Andrew Price]

Progress

You can check your progress version by double clicking the file called progress.cfg which is normally located in a directoy called or starting with DLC.


if you locate the file called prowin 32 you can create a shortcut icon to it and then select the program _edit.p as schown bellow:

C:\DLC83B\bin\prowin32.exe -p _edit.p

In this example everything before the -p is the path to prowin.

If this works you are able to write a procedure.

You can write sql statements here but you will get better results if you use statements such as:

find Table1 where table1.field1 = Variable
use-index main no-lock no-error.
if avail table1 then do:


end.

Normally a 4gL procedure is a much better solution as an ODBC connection. In fact it is possible to do everything you can do in access within the 4gl language.


If you need any help please give me a mail and I'll be happy to help you.

regards

 

[avdberg]

I've been trying to pull about 40000 (forty thousand) records from a database (the database is about 2gig in total) using Merant's ODBC drivers to get the data into access. This causes Access to crash without fail. I was hoping that there would either be a solution to this, or possibly someone could suggest a Unix shell utility that can take an SQL statement as a parameter and then output the relevent data in some sort of deliminated format.

Many thanks.

We where also using the Merant's ODBC drivers and had many problems with this and it's very slow. I have made a export routine that is now running every night.

What it's doing is the following:

Export all the needed tables to a seperate csv file
Ftp the files to the MS SQL machine
Use SSIS to import the csv files

It's working like a charm. Export with odbc takes 11 hours and the csv export and import into sql ony 5 hours.

You can search on this forum for my name and you will find the code and can also ask people on the forum and they will help you.

Greeting André van den Berg,

System Administrator
Meyra Holding B.V.

 

[tamhas]

One of your big problems is that the improvement of the SQL drivers from 8.3 to 10.1C has been staggering. You are trying the equivalent of competing in the Indy 500 with a model T.

Which said, have you tried grabbing the records in smaller blocks? Don't want anything that old to have to work too hard, you know.