Solved

SQL Server Query .dbf file

Posted on 2012-04-09
11
2,971 Views
Last Modified: 2012-04-16
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!
0
Comment
Question by:Ursino
  • 5
  • 5
11 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 37826316
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?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 37827180
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.
0
 

Author Comment

by:Ursino
ID: 37829791
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?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 37831210
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.
0
 

Author Comment

by:Ursino
ID: 37833767
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
------------------------------
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 37834565
>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)?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 37834871
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.
0
 

Accepted Solution

by:
Ursino earned 0 total points
ID: 37835393
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
0
 

Author Comment

by:Ursino
ID: 37835483
Can you please assign OLAF 500 points and use my answer as the solution?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 37836185
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.
0
 

Author Closing Comment

by:Ursino
ID: 37850396
I had to compile the answer, but here it is entirely.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 47
sql server query? 6 28
while loop in html mail format 5 32
backups - Strategies 1 13
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now