[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

How do I configure a Linked Server in Microsoft SQL Server 2008 to use Microsoft Visual Fox Pro OLE DB as the provider?

When I try to setup a Linked Server in Microsoft SQL Server 2008 to use Microsoft Visual Fox Pro OLE DB as the provider, I cannot query the data source.  Can you provide the syntax needed to setup the new linked server (e.g., data source, product name, provider string, etc.).
0
Sparqware
Asked:
Sparqware
1 Solution
 
pcelbaCommented:
I've created linked server named fox, scripted it and the result works. Now it is possible to execute SQL commands on VFP tables in specified folder (e.g. select * from fox...TblName).

FYI, the linked server function failed if the datasrc path contained spaces.

What is your error message when testing the connection to linked server?

/****** Object:  LinkedServer [FOX]    Script Date: 05/14/2009 21:16:32 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'FOX', @srvproduct=N'', @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'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'
GO
 
EXEC master.dbo.sp_serveroption @server=N'FOX', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
 
-- some resources recommend to execute:
EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB', N'AllowInProcess', 1 
GO 

Open in new window

0
 
SparqwareAuthor Commented:
It turns out my problems were related to the AllowInProcess property.  Once I set that to yes, then it worked fine.  Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now