Best method for setting up Linked Server for SQL Server in Windows 7 64-bit
Posted on 2015-01-28
I want to set up a linked server to a folder containing text files so Microsoft SQL Server 2012 can access the data in them. The database is installed locally on a 64-bit Windows 7 machine, which has Office 2003 installed. As I try various things, I'm noticing that much of the documentation online involves old, deprecated drivers/providers, and many of the online forum discussions involve people who want to make it work with a particular driver/provider, where there seem to be many issues getting this done on a 64-bit machine. I don't care what driver I use. If I can just download a driver that works and has the fewest setup problems, that would be ideal.
Based on reading about the errors I've gotten, I've tried setting up different DSNs for 32- and 64-bit, downloading new drivers (but since Office 2003 is installed, only 32-bit drivers could be downloaded), giving all users access to the directory that has the files, giving the SQL Server access to a certain temp directory, etc.
My question, though, is what is the best approach to do this? Is there some best practice or way to set up linked servers that bypasses the incompatibility between 64- and 32-bit, doesn't make use of deprecated features, and doesn't require wide-open permissions in various temp directories, etc.? In other words, rather than chasing down a solution to work with a particular driver, is there a better way to do this that's maybe more straightforward and bypasses most incompatibility issues?