Copying tables from FoxPro into SQL Server 2000 3

[Dabase]

Hi,

I am trying to automate a daily process of copying FoxPro tables into MS SQL Server 2000. The reason why the tables need to be copied on a daily basis is so that users all able to query this information from our MIS system, which is being developed in ASP and designed on the basis that only SQL databases will be queried.

What I have done so far is to create a DTS package that copies the 20 tables (and data) into SQL. I tried executing the package but it has been running for approximately 22 hours and I am just copying test data (about 4MB in size).

Any ideas or suggestions on how I could either speed this up or attempt another method of copying the data?

Your help will be much appreciated.

Thanks
Dabase

 

[DanaAnderson]

How are you copying the data? Try this:
Export the data using the COPY TO command with the clause TYPE SDF. Then create a package that reads the SDF files into the SQL Server tables. The company I worked for a couple of years ago did this for on-site systems, sometimes with a couple hundred thousand customers. Even the largest export only took under (or about) an hour.

Dana

 

[Dabase]

Hi Dana,

Thanks for your reply.

Using the COPY TO command, I was able to copy a table, but I am having difficulties creating a DTS package to import the table into SQL Server.

Do I need a specific ODBC driver in order to create a DSN?



Thanks
Dabase

 

[Mike Lewis]

Dabase,

Although ODBC isn't essential for DTS, I have always found that I get better results if I do use ODBC. For example, instead of importing a 'dBASE DBF' file, set up an ODBC data source to a directory holding the DBF file (using the Visual Foxpro ODBC driver). That should work OK, and you shouldn't need to go through the intermediate step of creating a SDF.

Also, there's no way that DTS would normally take 22 hours to run a 4 MB import, so something has obviously gone wrong.

Having said all that, I have always found that the most satisfactory way to transfer data from VFP to SQL Server is not through DTS, but to do it directly in VFP. Essentially, you need to write some code that loops through your source table, and, for each record, generates an INSERT command. You then use SQLEXEC() to send that command to the server.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Dabase]

Hi Mike,

Thanks for your response.

I am very interested in investigating the option of transfering data directly from VFP to SQL, but I am not very familiar with VFP coding. Would you be able to give me a sample?

Thanks
Dabase

 

[Mike Lewis]

Dabase,

Here's a very simple example of what you need to do. You'll need to build on this to make it relevant to your needs.

* Open your source table
USE Customer IN 0

* Get a connection handle to SQL Server
lnConn = SQLCONNECT("MyDSN","MyLoginName","MyPassword")
&& MyDSN is an ODBC DSN

* Loop through the source table
SELECT Customer
SCAN

* Build an INSERT command
lcCmd = "INSERT INTO SQL_Customer (cust_name, cust_city) VALUES (" + ALLTRIM(customer.cust_name) + "," + ALLTRIM(customer.cust_city) + ")"
&& SQL_Customer is the customer table on the server

* Send the INSERT command
SQLEXEC(lnConn, lcCmd)

ENDSCAN


As I said, that is a highly simplified example, but I hope it will put you in the right direction.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[SonicChen]

aha u are just discussing my problem.
but my target is oracle.
i think i should learn some foxpro coding.

way to gold shines more than gold

 

[Mike Lewis]

SonicChen,

The example I suggested will work equally well with Oracle as with SQL Server. But, remember, this is a highly simplified example. In practice, you will need to worry about things like differences in data types and other issues. But the principles are straightforward enough.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com