We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
6 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
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?
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
3
11 Comments
I'm talking about setting up a Linked Server in SQL Server 2012. Once you set one up, you can query the data in the external source (in this case a text file), without using an ad-hoc solution such as openrowset in your query. In either case (linked server or openrowset), you have to specify a provider. When I do, it tends to say that it's not registered, etc. I'm trying to understand what's the best provider to use these days, with the system configuration mentioned above.
Active today
I would use SSIS in the first place. Using OPENROWSET for bulk-imports is ok. I would imagine more problems with a linked server in this case.
Active 1 day ago
I think you are confusing Linked Server with ODBC and DTEXEC. The last two requires 32b and 64b versions because they are executables (.EXE files).
Linked Server is an object inside the SQL Server so I don't think it has this 32b and 64b issue.
Also, what you really want to achieve here so maybe we can recommend you another solution.
Linked Server is an object inside the SQL Server so I don't think it has this 32b and 64b issue.
Also, what you really want to achieve here so maybe we can recommend you another solution.
Well, after discussing this with my manager, it looks as if I'll be using SSIS, more based on the fact that that's what they're more comfortable with in terms of supporting. The reason why I was thinking of Linked Server is that it seemed from my perspective to be easier to support, before I learned that they're more used to SSIS. SQL Server's documentation recommends Linked Server over bulk import / openrowset for operations that will be performed regularly; those other two options also require ad hoc distributed queries permission, and I want to keep permissions as limited as possible.
This is for a one-off intranet utility that will interact with a SQL Server database that needs to be loaded with new data from text files in a directory every time someone starts the utility. So looks as if I will have my .NET code kick off the SSIS package as soon as the request comes in.
When I was trying to do it with a Linked Server or openrowset, when calling sp_addlinkedserver or the wizard, I tried some examples using Jet, or MSDASQL and Microsoft Text Driver, or using ODBC, or various other drivers, and it seemed that rather than debug each one I should try to find which parameters would most likely work best.
So in any case, it looks as if this is lower priority now since we're going to SSIS, but if someone does have an idea of what to use with sp_addlinkedserver that actually works on this particular machine, I'd be interested. Thanks.
This is for a one-off intranet utility that will interact with a SQL Server database that needs to be loaded with new data from text files in a directory every time someone starts the utility. So looks as if I will have my .NET code kick off the SSIS package as soon as the request comes in.
When I was trying to do it with a Linked Server or openrowset, when calling sp_addlinkedserver or the wizard, I tried some examples using Jet, or MSDASQL and Microsoft Text Driver, or using ODBC, or various other drivers, and it seemed that rather than debug each one I should try to find which parameters would most likely work best.
So in any case, it looks as if this is lower priority now since we're going to SSIS, but if someone does have an idea of what to use with sp_addlinkedserver that actually works on this particular machine, I'd be interested. Thanks.
Active 1 day ago
I need to see how are you creating the linked server.
bcp utility should be more easy for importing data from text files.
bcp utility should be more easy for importing data from text files.
Active today
This is for a one-off intranet utility that will interact with a SQL Server database that needs to be loaded with new data from text files in a directory every time someone starts the utility. So looks as if I will have my .NET code kick off the SSIS package as soon as the request comes in.
It still depends on your concrete needs.
I can imagine doing this entirely in the tool and using the SqlBulkCopy Class. Another possibility could be filling an EF object graph and pushing the data to the server.
@ste5an:
Thanks for the recommendation on SqlBulkCopy Class. I like that idea, and might consider it if I have trouble with other ways. I don't think Entity Framework to connect with the text files would be good, as it seems like too much overhead just to get the data into SQL Server. But I'm leaning towards SSIS now because then if there's a data problem down the road, they can troubleshoot the package instead of getting into the .NET code.
Thanks for the recommendation on SqlBulkCopy Class. I like that idea, and might consider it if I have trouble with other ways. I don't think Entity Framework to connect with the text files would be good, as it seems like too much overhead just to get the data into SQL Server. But I'm leaning towards SSIS now because then if there's a data problem down the road, they can troubleshoot the package instead of getting into the .NET code.
Active 1 day ago
Just for information, bcp stands for Bulk Copy Program. It's an .exe file that receives parameters and depending on that will do the job for you so no need for coding.
I ended up using SqlBulkCopy class and it worked like charm. Earlier, I had tried SSIS but I got another weird installation error and figured I should be getting this written rather than debugging weird errors with my development machine. They're okay with that too...at least it keeps everything within the same technology and is also relatively low-level vs. doing some sort of EF solution.
Featured Post
PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article summaries thoughts and ideas from two years of sustained use. It provides good reasoning to make the jump to Windows 10.
This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
- Storage Software
- Windows OS
- Mac OS X
- Storage Hardware
- Hardware
- Android, *Memory
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…
- Windows 10
- Windows Vista
- Windows 8
- Windows OS
- Windows 7
- Microsoft Legacy OS, Windows XP, *Scripts, *task scheduler
Suggested Courses
Course of the Month6 days, 20 hours left to enroll
Earn Certification
Managing Projects with Microsoft Project 2013 (Exam 74-343)
$49.00$44.10
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.