I am using SQL Server 2008 on Windows Server 2003. I want to use a linked server to open a Visual Foxpro DBF file, using driver Microsoft OLD DB Provider for Visual Foxpro from SQL Server 2008 linked server feature.
When I establish linked server connection by using Microsoft OLD DB Provider for Visual Foxpro, to open a Foxpro DBF file, I meet with the following error, any ideas what is wrong?
Can not retrieve required data from this request (Microsoft.SqlServer.Management.Sdk.Src)
Meeting exception when executing Transact-SQL or batch (Microsoft.SqlServer.ConnectionInfo)
Error from Microsoft OLD DB Provider interface "VFPOLEDB" of linked server "DBFServer", access is defined.
Can not retrieved required interface "IID_IDBSchemaRowset" from OLE DB interface "VFPOLEDB" of linked server "DBFServer"
(Microsoft SQL Server error 7399)
regards, George
Check 'Allow Inprocess' on the VFPOLEDB provider:
See Cindy Winegarden's answer on http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/e54d20dd-b65b-4cff-9349-6499e6e069e2 for how to do it.
Edit: this is the relevant part of the answer:
Here's what Stepahnie posted to her thread in the microsoft.public.data.oledb NNTP newsgroup on April 10:
"Finally, I found an option ' Allow inprocess' in linked server -> providers -> VFPOLEDB in MSSQL2005. With enable this option, I can connect to vfp with oledb. While diabling this option, it works only 50% with successful connection....
And I have another MSSQL2005 with the same setup (except the option 'allow inprocess' disabled), all the connection make to vfp is 100% successful... "
I looked and found that, as she said, in Server Objects > Linked Servers > Providers > VFPOLEDB > General tab > Provider options > Allow inprocess. Also, you can change the InProcess setting with the following code:
USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB', N'AllowInProcess', 1
GO