Need to Import from a Fox cursor into SQL db

[Manna]

We have a database with a FoxPro 6.0 front end and a SQL Server 7.0 SP6 back end. I'm running a Fox prg script that gives me a cursor or a file. I can output the file in FoxPro, Excel, and other formats.

I need to import the data, which already has the correct field names and format, into our Fox/SQL database. I've tried running the DTS via Enterprise Manager, but don't find the option for the type of file I have. Is there a better way to import this data into SQL Server?

Mary Dardinger Manna

 

[tlbroadbent]

What type file do you have? DTS can handle a variety of formats and file types. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[foxdev]

Hi, Mary. By into our Fox/SQL database I assume you mean from your VFP cursor to a SQL Server table.

There are a bunch of different ways; indirect and direct:

Indirect
1. Output to XLS and use DTS to import
2. Output to DBF and use DTS to import
3. Output to text file (using delimiters of your choice, including the default VFP delimiters of comma/CR.
4. As above, but use BCP to import.

Direct
1. Use SQLStringConnect() or SQLConnect() to establish a connection, then use SQLExec() and loop through your local cursor, issuing Insert statements for each row.
2. Create a persistent updateable remote view, which looks and smells like any other VFP table, and insert records into it (which will then automatically cause inserts into the back end).

Choose one and have fun! -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[Manna]

Thanks, tlbroadbent and foxdev! I shared my difficulty with my husband over the weekend, and he and I worked out the solution to the part that I was "stuck" on. It was a "DBF" FoxPro cursor/file/free-table that I was trying to import into SQL Server. (Although I could have used XLS or other formats if that hadn't worked.) The reason I couldn't get the DTS Wizard to work for me on Friday was that I didn't have an appropriate ODBC Driver set up for my DBF file. And I didn't know how to set one up, either. It's under Control Panel on NT servers. Once that part was in place, the work flowed smoothly.

Manna