SQL Server Query .dbf file

What:
Trying to query a .dbf file e.g "Select * from somefile.dbf"

Using:
SQL Server 2008

Problem:
I can't figure out what I need to do to accomplish this.

Tried:
Various forms of linkedserver,  but I can't anything to work.
I even installed vfpoledb on the server.

ALSO

            
            select * from openrowset('MSDASQL',
            'DSN=Visual FoxPro Tables;                              
            SourceDB= \\MYSERVER\FOLDER\;
            SourceType=DBF',
            'select * from dbfTable') 

Open in new window


gives error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

Msg 7303, Level 16, State 1, Line 7
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"


Request:
Can someone help me with some straight script to connect to this .dbf file?

Something similar to if say I was going to connect to .xls file:
(this is from another solution)

     EXEC sp_addlinkedserver MyExcel,
     'Jet 4.0',
     'Microsoft.Jet.OLEDB.4.0',
     'c:\ee\test1_work.xls', 
      NULL,
     'Excel 5.0;'
 
GO 
--Set up login mappings (just ADMIN - jet wants something).
EXEC sp_addlinkedsrvlogin MyExcel, FALSE, NULL, Admin, NULL
GO
--List the tables in the linked server (these are the worksheet names).
EXEC sp_tables_ex MyExcel
GO
select * from myexcel...Sheet1$
GO

Open in new window




Thank you!
UrsinoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TempDBACommented:
Check out the following link:-
http://msdn.microsoft.com/en-us/library/aa337084.aspx

Looks like driver is not installed in your system. Can you do it?
Olaf DoschkeSoftware DeveloperCommented:
Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This means you can't use an OLEDB driver through a DSN, only ODBC drivers. You trigger the ODBC driver manager, and it is as it's named, just an ODBC driver manager, not OLEDB, not data access in general.

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

herefore you need:
select * from openrowset ('VFPOLEDB.1', 
'\\MYSERVER\FOLDER\'; ' '; ' ',
'select * from dbftable')

Open in new window


You need to configure SQL Server to allow 'Ad Hoc Distributed Queries' via

sp_configure 'show advanced options', 1
RECONFIGURE
GO

sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

Open in new window


You also need to assure you get file access. Typically the SQL Server Service only has rights to address local drives.

Bye, Olaf.
UrsinoAuthor Commented:
select * from openrowset ('VFPOLEDB.1', 
'\\MYSERVER\FOLDER\'; ' '; ' ',
'select * from dbftable')

Open in new window


QUESTION

If foxpro requires a user name and password, where do I put it?


RETURNED

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB.1" for linked server "VFPoledbDbf" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "VFPOLEDB.1" for linked server "VFPoledbDbf".


Recommendations?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Olaf DoschkeSoftware DeveloperCommented:
That's what I said:

You also need to assure you get file access. Typically the SQL Server Service only has rights to address local drives.
VFP never requires a user and password, but it (of course) adheres to the rules of file system access rights.
I told you, SQL SERVER SERVICE needs to run with a windows account having rights to access the file. Typically SQL SERVER SERVICE runs with a system account having very limited file access rights only to local files.

Even if you grant "EVERYBODY" full access rights to the dbf or the dbf folder, doesn't mean SQL SERVER get's access. It may first need to be changed to an account, which before anything else has the right to access LAN shares at all to even get to the file at all.

Look into management console and there into Services and change the account used by the MSSQSERVER Service or an additional Service for another SQL Server Instance you use to a domain account having rights to the file share and the dbf you want to query.

Bye, Olaf.
UrsinoAuthor Commented:
In SQL Services under the configuration manager in 'LOG ON AS' all of them are set to an administrative account.

So much so,  the the select query, begins by displaying the column headings of the dbf table before it actually errors out.  So it is connecting.




TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "VFPOLEDB.1" for linked server "VFPoledbDbf" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "VFPOLEDB.1" for linked server "VFPoledbDbf". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
Olaf DoschkeSoftware DeveloperCommented:
>In SQL Services under the configuration manager in 'LOG ON AS' all of them are set to an administrative account.
Local admin or domain administrator?

Administrator sounds good, but a local admin might not be allowed to access the dbf anyway, if \\MYSERVER\ is a seperate server.

>Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "VFPOLEDB.1"
This points to some additional problem, which I didn't cross yet.
What version of VFPOLEDB have you installed, and I don't mean .1, I mean the vfpoledb.dll file version. You'll find it in \Program Files\Common Files\System\Ole DB\vfpoledb.dll

Also perhaps take baby steps and first try to query the Northwind sample data installed together with the VFPOLDB Provider in C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind

If you can query that data you know the oledb provider is ok.

Bye, Olaf.

Next question: What is byte 0 of the dbf file (this encodes what dbf type you have there)?
Olaf DoschkeSoftware DeveloperCommented:
Found something that might play a role:

Go into SSMS, connect to the Database Module, go into Server Objects > Linked Servers > Providers > VFPOLEDB

Look into Properties.
In the Provideroptions check the option "Allow InProcess".

Besides if there are two versions of vfpoledb installed try VFPOLEDB as the provider instad of VFPOLEDB.1

Bye, Olaf.
UrsinoAuthor Commented:
OK.  Got it! it was this: the provider was setup

AS: VFPOLEDB
NOT:  VFPOLEDB.1



when I did this:

SELECT * FROM VFPoledbDbf...myDBFtable

Open in new window


SO HERE IS THE ENTIRE SOLUTION:

1. I downloaded and installed the provider:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=14839

2. I enabled adhoc queries: (i think I used something else)


sp_configure 'show advanced options', 1
RECONFIGURE
GO

sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

Open in new window

 
3. I made sure the SQL Services under the Account Manager  were setup correctly
       
4. I used the following link to get the following code:
http://fox.wikis.com/wc.dll?Wiki~VisualFoxProDataFromSQLServer

-- Example of using VFP free tables in SQL Server
sp_addlinkedserver @server = 'VFPoledbDbf',
@srvproduct = 'VFP',
@provider = 'VFPOLEDB.1',
@datasrc = 'C:\VFP9\Samples\Data'
GO
SELECT * FROM VFPoledbDbf...products
GO
sp_dropserver @server = 'VFPoledbDbf'
GO

Open in new window


5. I modified the .1 in the provider and just used VFPOLEDB

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UrsinoAuthor Commented:
Can you please assign OLAF 500 points and use my answer as the solution?
Olaf DoschkeSoftware DeveloperCommented:
Thanks, Ursino.

Glad it works. Instead of compiling an answer you can also mark multiple posts. But of course this will be easier to read and follow for future users.

Just one thing to add: using openrowset() instead of creating a linked server also creates a temp linked server, also making use of the provider settings in Server Objects > Linked Servers > Providers > VFPOLEDB

Creating a linked server makes your queries shorter and perform better (I assume).

Bye, Olaf.
UrsinoAuthor Commented:
I had to compile the answer, but here it is entirely.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.