Visual FoxPro linked server

&quot&#59;Lord&quot&#59; Byron

I am trying to add a FoxPro linked server to MS SQL 2005, and I can't seam to create a linked server that works. What am I doing wrong in linking the server

I have an ODBC connection that worked but not OLEDB; how can I do this with OLEDB (either VFPOLEDB or Jet, if it will work) and not ODBC.

This is what I thought was right

Code Snippet

sp_addlinkedserver 'test',

'FoxPro',

'VFPOLEDB',

'C:\Data\SomeDatabase.dbc',

NULL,

NULL,

NULL

But it gives the error:

Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "test".

Also I know in MS SQL 2000 once you linked a server you could view it in EM, but when I linked the VFP via ODBC I could query agianst it, but I could not open it in Mangament Studio.

Thank you in advance.

Cindy Winegarden

Hi Byron,

You're missing the provider string, "VFPOLEDB.1. That is a character string with the name of the OLE DB data provider.

Code Snippet

EXEC master.dbo.sp_addlinkedserver

@server = N'VFP_Northwind',

@srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',

@provider=N'VFPOLEDB',

@datasrc=N'C:\Program Files\Microsoft Visual FoxPro 9\Samples\Northwind\Northwind.dbc',

@provstr=N'VFPOLEDB.1'

"Lord" Byron

Cindy,

Thanks for you help again, but it's still giving me the same error.

Code Snippet

EXEC master.dbo.sp_addlinkedserver

@server = N'VFP_test',

@srvproduct=N'Microsoft Visual FoxPro OLE DB Data Provider',

@provider=N'VFPOLEDB',

@datasrc=N'C:\Data\database.dbc',

@provstr=N'VFPOLEDB.1'

GO

Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "VFP_test"

The database is really there and I have the latest VFP driver installed, but it's still failing. I can connect to it just fine from my .net program but not from SQL.

"Lord" Byron

Ok, it looks like it might be a permission problem -- I know permissions could never be the root of a problem in SQL.

When connecting to the database via TCP (with a SQL Login) from a remote sesion of Management Studio I get the above listed error. When connecting via Shared Memory on the server with Windows Auth I get a different error:

Code Snippet

The OLE DB provider "VFPOLEDB" for linked server "VFP_test" reported an error. Access denied.

Cindy Winegarden

Hi Byron,

For permissions issues, the account the SQL Server service runs under must have NT permissions to the directory where the DBFs are. You can test this issue by copying DBFs to the SQL Server machine and try querying the linked server. If it works that way and not when the DBFs are on the network share then permissions is the issue. For example, if I open SSMS on my server and query the linked server I get data as expected. If I open SSMS on another machine and connect to my server box and query the linked server I run into the permissions issue.

For a "file access denied" error, it's usually a case of one user opening the DBFs exclusively and then another user (your linked server) trying to open the DBFs. If more than one user needs to work with the tables then both should open them shared. Shared is the default for OLE DB.

"Lord" Byron

I'm not sure where the "access denied" error is coming from, but after rebooting and disabling shared memory it went away; now it is telling me "Invalid path or file name", but the path and filename are correct. Here is the configuration:

On the Server I'm I'm running:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

On my development box:

MSSMS Standard

On my laptop:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

MSSMS Express

All three have the latest version of VFPOLEDB installed, and the two with SQL Server installed both have the FoxPro database in the C:\Data\ directory. Both copies of SQL are setup to allow remote TCP conections and to use mixed mode.

Ok, the Express on the laptop is giving me the "invalid path" error and Standard on the server is still giving the "Cannot create instance" error. I'm really at a loss here. The FoxPro database doesn't have any security on it, but do I have to set some sort of impersation rights to it, so it doesn't pass my sa login cerdentials I'm using the same script to link the server and the Data directory is an exact copy of the other, so I'm kind of confused as to these diferent errors.

"Lord" Byron

And, I'm managing both servers from the development box, I was only really using MSSMS Exp to make sure I get teh same error locally as across the network.

"Lord" Byron

Ok, the linked server on the express is working fine now, it was permissions.

The linked server on the standard version is now giving a different error:

Cannot fetch a row from OLE DB provider "VFPOLEDB"

This is after I changed the SQLSERVER process to run as the administrator account (yes, in production this will be fixed).

I saw another thread (http://forums.microsoft.com/TechNet/ShowPost.aspx PostID=490000&SiteID=17)where you (Cindy) had posted that this could be caused by a bad version of the OLEDB driver, but it is the lastest version. Also in that thread you mention the dbf could be croupted, but I'm getting the same error on any table I try to open.

"Lord" Byron

On the server with SQL running as administrator I get the "Cannot fetch a row from OLE DB provider "VFPOLEDB"" error, if I set it to run as Network service the error I get is "Cannot create an instance of OLE DB provider "VFPOLEDB""

Both accounts have read/write access to the directory, and the administrator account has full control. Any ideas

"Lord" Byron

Ok, I found the answer.

Cindy had posted back in Jan on a TechNet forum:

http://forums.microsoft.com/TechNet/ShowPost.aspx PostID=1143871&SiteID=17

I had actually ruled out the inprocess setting because it wasn't set on express and it was working fine. But it was the same thing Cindy qouted Stepahnie as saying: that it worked some of the time on one, and all of the time on the other.