visual foxpro .dbf import into sql 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

visual foxpro .dbf import into sql 2005

Author

Topic

arunsqladmin
Yak Posting Veteran

74 Posts

Posted - 2009-12-22 : 01:25:55

i am trying to import a visual foxpro file(.dbf) using open rowset
into sql server 2005

SELECT * into sampleTable FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;
SourceDB=C:\Documents and Settings\pqr\Desktop\samplefolder;
SourceType=DBF',
'SELECT * FROM xyz')

sample folder consists of xyz.dbf

i get the following error

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Driver does not support this function".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

pl help

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-22 : 01:43:02

Hi

May be

Check the account SQL Server is running under to make sure that that user has (read ? maybe read/write?) permissions to the file.

-------------------------
R...

arunsqladmin
Yak Posting Veteran

74 Posts

Posted - 2009-12-22 : 02:27:27

i have been trying to execute using local administrator account. still i dont get to execute the query

arunsqladmin
Yak Posting Veteran

74 Posts

Posted - 2009-12-22 : 02:31:53

i am able to run the same query in a server which has sql 2000 but the problem is with sql 2005

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-29 : 18:23:30

does the local administrator account have rights to C:\Documents and Settings\pqr\Desktop\samplefolder?
where is C:\Documents and Settings\pqr\Desktop\samplefolder located ? on the server or your PC?
What version of SQL and windows do you have (http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en)
Is this a one time import or daily/monthly import. If not one time I would use a connection manager instead of OPENROWSET.

http://wufta.blogspot.com/2006/03/ssis-ms-access-destinations-and-visual.html

my 2 cents

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jnovation
Starting Member

1 Post

Posted - 2011-06-14 : 12:15:42

What finally worked for us was to use the FoxPro driver (available from Microsoft Download) and use the following syntax. In our case we are using SQL 2008.

select *
from
openrowset('VFPOLEDB','\\VM-GIS\E\Projects\mymap.dbf';'';
'','SELECT * FROM mymap')

Substitute the '\\VM-GIS...' with the location of your dbf file, either UNC or drive path. Also, substitute 'mymap' after the FROM with the name of the dbf file without the .dbf extension.

Best part is that NO linked server was needed.

gfard
Starting Member

1 Post

Posted - 2012-01-18 : 11:46:45

Hi Jnovation,

I am just wondering if you could provide more inside about your approach. we are using "select * from
openrowset('VFPOLEDB','\\VM-GIS\E\Projects\mymap.dbf';'';
'','SELECT * FROM mymap')" in our ASP app but although the following dll (C:\Program Files (x86)\Common Files\System\Ole DB\vfpoledb.dll)is registered within windows 2008 server but we keep getting following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]The OLE DB provider "VFPOLEDB" has not been registered.
The OLE DB provider "VFPOLEDB" has not been registered

and when I ran the query on SQL 2008 I am getting following error:

Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'VFPOLEDB' has been denied. You must access this provider through a linked server.

DisallowAdHocAccess is already set to 0 (http://support.microsoft.com/kb/327489)

I appreciate any thought.

Thanks,

quote:
Originally posted by jnovation

What finally worked for us was to use the FoxPro driver (available from Microsoft Download) and use the following syntax. In our case we are using SQL 2008.

select *
from
openrowset('VFPOLEDB','\\VM-GIS\E\Projects\mymap.dbf';'';
'','SELECT * FROM mymap')

Substitute the '\\VM-GIS...' with the location of your dbf file, either UNC or drive path. Also, substitute 'mymap' after the FROM with the name of the dbf file without the .dbf extension.

Best part is that NO linked server was needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources