Openrowset and FoxPro Database

I wanted to write about one of my experiences where I nearly pulled off my hair while trying to make an Openrowset call to my FoxPro Database work from my SQL Server. It was high time when we decided to move over, discard my old FoxPro databases and migrate completely to SQL Server and hence was the need to make Openrowset calls to FoxPro, query the data and backup just what I needed. I had written plenty of Openrowset queries in the past against diverse data sources with ease, but with FoxPro I stumbled upon the error: (Note: I am using the OLE DB provider for Visual FoxPro)

Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'VFPOLEDB' reported an error.
[OLE/DB provider returned message: Feature is not available.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IDBInitialize::Initialize returned 0x80004005: ].

Spending hours of research, it finally looked to me as syntactical problem (Syntax is a nightmare) causing this error. I’ve never seen such a bad looking syntax before but thank god! I was finally able to get one going and returning data from my FoxPro database. I hope this will help plenty of users who fall into this pit looking for a correct Openrowset query to FoxPro from SQL Server.

SELECT * FROM OPENROWSET('VFPOLEDB.1','\\path.dbf';'';'','SELECT * FROM <table>')

Important: Please note that ALL are single quotes in the above query and you should key in the correct path where your FoxPro Database (.dbf file) resides as the second parameter to Openrowset.

 

 

Author : Debarchan(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft