My client has a Visual FoxPro database that needs to co-mingle with SQL Server data (SELECT only, no updates or deletes).
After quite a bit of searching I found the VFP OLEDB provider doesn't work with 64-bit SQL, so I installed an instance of 32-bit SQL Express. I was able to successfully create linked server to VFP. The 32-bit instance is installed on a different server than the 64-bit, but I'm using SSMS on the server with the 32-bit instance and can connect via SSMS fine to the 64-bit instance.
Now I need to link my main 64-bit SQL instance to the 32-bit instance, but I've not had luck. I've tried both the SQL Native Client and the OLE DB Provider for SQL Server, but I keep getting "Server is not found or not accessible." Remote connections are enabled, I've enabled TCP/IP, and tried all manner of server name combinations (.\SQLEXPRESS32, localhost\SQLEXPRESS32, (local)\SQLEXPRESS32, MyServerName\SQLEXPRESS32).
At this point I'm not sure if this is a linked server configuration issue, or a new install of SQL Server configuration issue, so I'm not entirely sure if I'm asking how to link my server or how to ensure I can access my server. Or maybe I'm asking the best way for my 64-bit SQL Server to read VFP data. Any suggestions?