Import to SQL from VFP table programmatically

Question

Import to SQL from VFP table programmatically

jesuslives01

Old Hand

Points: 326
More actions
August 30, 2010 at 3:08 pm

#227053

I'm getting an error pointing to the last ')'; however, that may not be the real problem. Anyone know what is wrong?
I tried it with and without the ';User Id=;Password='. It doesn't work either way.
Insert into dirmsarf(dircode, msa, poppct, hhldpct, emppct, buspct, whtpct, blkpct, homecode, msacode2)
select dirmsarf2.dircode, dirmsarf2.msa, dirmsarf2.poppct,
dirmsarf2.hhldpct, dirmsarf2.emppct, dirmsarf2.buspct, dirmsarf2.whtpct,
dirmsarf2.blkpct, dirmsarf2.homecode, dirmsarf2.msacode2 from
OPENDATASOURCE( 'MSDASQL', 'Driver=Microsoft FoxPro VFP Driver;SourceType=DBF;SourceDb=C:\yeldata\dirmsarf.dbf;User Id=;Password=' )

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

jesuslives01 (8/30/2010)
I'm getting an error pointing to the last ')'; however, that may not be the real problem. Anyone know what is wrong?
I tried it with and without the ';User Id=;Password='. It doesn't work either way.
Insert into dirmsarf(dircode, msa, poppct, hhldpct, emppct, buspct, whtpct, blkpct, homecode, msacode2)
select dirmsarf2.dircode, dirmsarf2.msa, dirmsarf2.poppct,
dirmsarf2.hhldpct, dirmsarf2.emppct, dirmsarf2.buspct, dirmsarf2.whtpct,
dirmsarf2.blkpct, dirmsarf2.homecode, dirmsarf2.msacode2 from
OPENDATASOURCE( 'MSDASQL', 'Driver=Microsoft FoxPro VFP Driver;SourceType=DBF;SourceDb=C:\yeldata\dirmsarf.dbf;User Id=;Password=' )

Try:

Insert into dirmsarf(dircode, msa, poppct, hhldpct, emppct, buspct, whtpct, blkpct, homecode, msacode2)

select dirmsarf2.dircode, dirmsarf2.msa, dirmsarf2.poppct,

dirmsarf2.hhldpct, dirmsarf2.emppct, dirmsarf2.buspct, dirmsarf2.whtpct,

dirmsarf2.blkpct, dirmsarf2.homecode, dirmsarf2.msacode2 from

OPENDATASOURCE( 'MSDASQL', 'Driver=Microsoft FoxPro VFP Driver;SourceType=DBF;SourceDb=C:\yeldata\dirmsarf.dbf;User Id=;Password=' ) dirmsarf2

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jesuslives01 Old Hand Points: 326 More actions · Answer 2

I tried your code and it gave an error on the 'dirmsarf2' at the end. I took it off and it gave an error on the last closing ')'

Lowell SSC Guru Points: 323522 More actions · Answer 3

BOL examples show that after the connection info, you need the table to extrac t from, outside fo the parenthesis, and it appears to need 4 part naming conventions;

here'sa BOL example,

SELECT *

FROM OPENDATASOURCE('SQLNCLI',

'Data Source=London\Payroll;Integrated Security=SSPI')

.AdventureWorks2008R2.HumanResources.Employee

and heres something that passes the syntax check:

Insert into dirmsarf(dircode, msa, poppct, hhldpct, emppct, buspct, whtpct, blkpct, homecode, msacode2)

select

dirmsarf2.dircode,

dirmsarf2.msa,

dirmsarf2.poppct,

dirmsarf2.hhldpct,

dirmsarf2.emppct,

dirmsarf2.buspct,

dirmsarf2.whtpct,

dirmsarf2.blkpct,

dirmsarf2.homecode,

dirmsarf2.msacode2

from OPENDATASOURCE( 'MSDASQL',

'Driver=Microsoft FoxPro VFP Driver;SourceType=DBF;SourceDb=C:\yeldata\dirmsarf.dbf;User Id=;Password=;')

...dirmsarf2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jesuslives01 Old Hand Points: 326 More actions · Answer 4

Thanks but that gives me an error pointing to the last dirmsarf2:

Incorrect syntax near 'dirmsarf2'.

Lowell SSC Guru Points: 323522 More actions · Answer 5

jesuslives01 (8/31/2010)
Thanks but that gives me an error pointing to the last dirmsarf2:
Incorrect syntax near 'dirmsarf2'.

well, what is the name of the table inside the foxpro database? is it dirmsarf, dirmsarf2 or something else? you aliased it in teh code as dirmsarf2.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jesuslives01 Old Hand Points: 326 More actions · Answer 6

Thanks for all your effort. I found the problem. If I convert the decimal columns in the VFP table to type double, the above code works.

Import to SQL from VFP table programmatically

Cookies on SQLServerCentral