Wiki Home

Visual FoxPro Data From SQL Server


Namespace: VFP
You can query VFP data in SQL Server using the Ole DB driver

Example 1: Querying VFP free tables from SQL Server. Run the following code in Query Analyzer / SSMS.
-- 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


Example 2: Querying a VFP DBC from SQL Server. Run the following code in Query Analyzer / SSMS.
-- Example of using tables in a VFP DBC in SQL Server
sp_addlinkedserver @server = 'VFPoledbDBC',
@srvproduct = 'VFP',
@provider = 'VFPOLEDB.1',
@datasrc = 'C:\VFP9\Samples\Data\testdata.dbc'
GO
SELECT * FROM VFPoledbDBC...Products
GO
sp_dropserver @server = 'VFPoledbDBC'
GO


Example 3: Querying a VFP free table from SQL Server without using a linked server. Run the following code in Query Analyzer / SSMS.
select * from openrowset ('VFPOLEDB.1', 'c:\vfp9\samples\data'; ' '; ' ','select * from customer')


Example 4: Querying a VFP DBC from SQL Server without using a linked server. Run the following code in Query Analyzer / SSMS.
select * from openrowset ('VFPOLEDB.1', 'c:\vfp9\samples\data\testdata.dbc'; ' '; ' ','select * from products')


If SQL 2005 Express Provider not work correctly:
EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB.1', N'AllowInProcess', 1


IN SQL 2008 (10.0.5500.0) I needed to use VFPOLEDB instead of VFPOLEDB.1 as provider name.
Referring To http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/e54d20dd-b65b-4cff-9349-6499e6e069e2
use this to enable in process usage of the provider to SQL Server:
USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB', N'AllowInProcess', 1 
GO

Or go into Server Objects > Linked Servers > Providers > VFPOLEDB and check the property Allow InProcess.

Category Data Category SQL
( Topic last updated: 2012.04.12 02:37:02 AM )