Import to SQL table from VFP table

[whateveragain]

I'm trying to import from a free VFP table on my hard drive. 1) Do I have to be concerned about Ad Hoc Distributed Queries being turned off? 2) What is wrong with the syntax in the following code? I'm getting an error message: Msg 102, Incorrect syntax near ','

Code:
use yeldata

go

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 Visual FoxPro Driver SourceDB=c:\yeldata\Dirmsarf2.dbf;
SourceType=DBF','select * from Dirmsarf2')

Thanks

 

[markros]

I see an extra ( here

insert into (...)
(select -- this ( is extra - should be removed

PluralSight Learning Library

 

[whateveragain]

I programmatically changed the Ad Hoc settings to turn enable on then I re-ran the opendatasource and now I'm getting the following errors

If I run the very same code mentioned above: I get the same error message (Incorrect syntax near ').

If I change the code to the following, I get this error: OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Visual FoxPro Driver]File 'dirmsarf.dbf' does not exist.".
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

Code:

SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft FoxPro VFP Driver (*.dbf)};
DBQ=C:\yeldata;
SourceType=DBF',
'SELECT * FROM dirmsarf')

 

[markros]

Have you verified that this table (free table) exists at c:\yeldata directory?

PluralSight Learning Library

 

[whateveragain]

Yes, it does exist. I'm wondering if there's another system change I must make.

 

[markros]

What is your SQL Server version and is it 32 bit or 64 bit?

Try either of the methods in that link:

http://fox.wikis.com/wc.dll?Wiki~VisualFoxProDataFromSQLServer

PluralSight Learning Library

 

[whateveragain]

Thanks. I think we're getting closer to the problem. I'm getting a new error. When I import using the wizard, it works except for the column labelled poppct. It should import as a decimal; however, #'s can be whole or decimals 100.00 or .34. All #'s that should be 100 were importing as 0. When importing some tables, I had to change all values to float in order for the correct #'s to import. Changing them within VFP didn't work; it had be be changed within SQL 2008. When I tried your suggestion above, I now get the following error:

Cannot get the current row value of column "[VFPOLEDB.1].poppct" from OLE DB provider "VFPOLEDB.1" for linked server "(null)". The provider cannot determine the value for this column.

 

[markros]

The difference between SQL Server decimal and VFP numeric formats is that in VFP 5.2 will mean 2 digits before the . and 2 after (the 5 includes the .)

In SQL Server 5.2 will mean 3 and 2.

PluralSight Learning Library

 

[whateveragain]

That would explain the problem if importing into an already existing table. I was expecting this code to create the table. Am I wrong?

 

[whateveragain]

I found the following link that may be the problem, but it's discouraging because I have to pay for the hotfix.

http://support.microsoft.com/?scid=kb;en-us;908915&x=8&y=12

 

[whateveragain]

I GOT IT WORKING!!!! If I convert the decimal columns to double within VFP, the code above works. Thanks all

 

[markros]

I'm wondering if that bug is still valid (the article is from 2007). May be the latest VFPOleDB doesn't have the problem?

PluralSight Learning Library