Link to home
Start Free TrialLog in
Avatar of jkanis
jkanis

asked on

SQL Server 2005 - Linked FoxPro

I am trying to access a foxPro Database from a SQL Server to create some custom reporting, however I am getting an access denied error when trying to access the FoxPro Database.

I called the 3rd party that developed the database and software we are using, they said there was no password to the database files.  There is no DBC file, just a folder.   The strange thing is that after executing the query below it displays the column headers of the table but no records. (They also said assisting me with this was "out of the realm of the scope of their support")

Not sure what information you need to help troubleshoot, please let me know.

I can access the database using the same driver and login/password (removed below example) via Crystal Reports v10 without a problem.  So I am assuming I am missing something in the SQL set up.

SELECT * FROM 
OPENROWSET('VFPOLEDB','\\VTIMECLOCK\SHARE\DBFS';'JOSH';
'XXXXXX','SELECT * FROM EMPLOYEE')

Results:
-----------------
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "VFPOLEDB" for linked server "(null)".

Open in new window


I also Set it up as a linked Server with the same results
SELECT * FROM OPENQUERY(FOX, 'SELECT * FROM EMPLOYEE')

Open in new window

/****** Object:  LinkedServer [FOX]    Script Date: 11/29/2012 15:25:19 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'FOX', @srvproduct=N'FoxPro', @provider=N'VFPOLEDB', @datasrc=N'\\vtimeclock\share\dbfs'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'FOX',@useself=N'False',@locallogin=NULL,@rmtuser=N'JOSH',@rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'use remote collation', @optvalue=N'true'

Open in new window

Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

1) Make sure the user under which is the SQL Server service running has access to the folder containing DBF files.

2) Use following script to create Linked Server (note differences against your definition)
/****** Object:  LinkedServer [FOX]    Script Date: 10/05/2009 23:04:29 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'FOX', @provider=N'VFPOLEDB', @datasrc=N'd:\LinkFox\'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'FOX',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'collation compatible', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'rpc', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'rpc out', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'use remote collation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Open in new window


At least the SELECT from OPENQUERY should work now.
You may also set above values in Linked Server properties via SQL Management Studio.
Avatar of jkanis
jkanis

ASKER

EXEC master.dbo.sp_addlinkedserver @server = N'FOX', @provider=N'VFPOLEDB', @datasrc=N'\\vtimeclock\share\dbfs'

Results:
Msg 15429, Level 16, State 1, Procedure sp_addlinkedserver, Line 42
'(null)' is an invalid product name.

I added the same parameter that I had from above " @srvproduct=N'FoxPro'," and the server was created with the different Server Options.

I double checked the Access rights on the remote machine, both in the "Share" and the security on the directory.  The SQL Service User is a Domain account that is a member of a group that has access to these directories, but I also added it explicitly as well to both the share and the folder security.  (the Sub folder "DBFS" inherits the security from parent and it does show the "SQLServerService" account that was set up.  I also tried putting in my user and password after the linked server was set up in Management Studio.

Still no luck... in the mean time I also tried to explicitly link to just one DBF file, instead of the directory, but that did not work either.

I could try to use VBS to pull data from the FoxPro database into a recordset and then push it into a SQL table, but that is a last resort and not something I am looking forward to attempting.
You did not mention the data machine is remote. It could be the problem...

You have to start with DBF files on local drive and then switch to the network. You may also define the data path by UNC to local computer as an intermediate step.

You could also try different security context for Linked server: http://mangalpardeshi.blogspot.cz/2009/01/linked-server-error-msg-7399-level-16.html

I have no such enviroinment to test Linked server on remote computer now. I can open DBF on remote commputer using ADODB.Connection over VFP OLE DB provider but that's not so restrictive as SQL Server...

BTW, it is much easier and much more reliable to update SQL data from Visual FoxPro.
Try including the parameter
@srvproduct

EXEC master.dbo.sp_addlinkedserver @server = N'FOX', @srvproduct='', @provider=N'VFPOLEDB', @datasrc=N'\\vtimeclock\share\dbfs'
Avatar of jkanis

ASKER

I copied the DBF folder to a local Drive and tried again, same result, "Access Denied" but the columns all show up in the results tab, with no records.  Some how it is accessing the database file enough not to show the records, but I cannot seem to use a password or user name that is working... but it works using the same driver in Crystal Reports.
---  Update...

Just tried running it again using different security contexts, now I am getting this error... I think I am breaking something on my SQL server...

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "FOX" reported an error. The provider ran out of memory.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT * FROM EMPLOYEE" against OLE DB provider "VFPOLEDB" for linked server "FOX".

The memory is fine on the server, so now I am a little worried and no further on solving the issue.  

In Regards to accessing SQL from FoxPro, I don't really know anything about FoxPro and need to get data on a SQL server.  I don't have any fox pro tools installed, and wouldn't know where to start.
try using windows authentication.
ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jkanis

ASKER

Never got anywhere but appreciate all the suggestions and help... Closing this question.