Gentlemen, Just a little tester for you at the end of the week.
I am trying to import data from a live VFP databases into some software written in C# which has worked fine using the VFPOLEDB interface. Now, if possible, I want to set up a linked database in M$SQL Server so I can access the data from the same database directly in a TSQL stored procedure and I have had partial success.
Using SQLManagement Studio I have set up the VFPOLEDB software on the SQL Server and under ServerObjects/LinkedServers/Providers can see the SQLOLEDB provider - so far so good.
I have also set up a linked server definition as follows:
EXEC master.dbo.sp_addlinkedserver @server = N'SQL2012_DEV_Flexispec', -- Your linked server name here @srvproduct=N'Visual FoxPro 9', -- can be anything @provider=N'VFPOLEDB', @datasrc=N'c:\flexispec_temp\data\flexdata.dbc', @provstr=N'VFPOLEDB.1'
This works and I can see above the linked server in the LinkedServers section but I can't link to it at all (Right click on Linked server and 'test connection' returns back SQL Error 7302 -> Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "SQL2012_DEV_Flexispec".
So, my hope of running the following statement: select * from SQL2012_DEV_Flexispec...Customers (yes you need 3 x full stops is what you have to format the code as) is obviously not working, returning back the same error about the creation of the OLEDB Instance.
I am beginning to doubt my sanity here as I have trawled all the net looking for a solution, to no avail. I have changed the options in the Windows Component services as per one post and allowed "In Process" creation against the Provider Properties for the OLEDB.
I am beginning to wonder if the 64bit SQL Server doesn't want to play nicely with the VFP OLEDB 32 bit drivers but can't prove it.
Any ideas before you go home?
Dave