How to Select data from Table from a DSN in T-SQL?

Question

How can I get data from a database that I can only access through an ODBC Data Source (DSN) in T-SQL MS SQL Server, any version?

Answer 1

The selecting is the easy part, but the real question is how do you connect to the datasource.

First, where are you selecting data from? Are you writing code, or just using the Query analyzer tool (2000) or Management Studio (2005)?

You'll need to setup your connection to the database regardless. If you need help with the connectionstrings to a DSN, the site www.connectionstrings.com is very helpful. Otherwise, from a query tool there will be dialog boxes or wizards to help you.

Answer 2

You need to add a Linked Server to the source, then you can query it as usual.

Answer 3

After reading the following, I decided to create SSIS packages to get data from another data source via DSN.

With Linked Servers, you have a little more flexibility, but also some additional security concerns. You are linking to the other data source as a specific user, with that user's authority to the linked server. If all you need is Read-only access to a few views, that user should only be granted that authority to those objects. This isn't so bad, you have total control of the authority you grant to that user, but you have to manage it and realize that granting too much authority could be a security concern.

Consider using a linked server with read-only rights to copy data from specific tables/view into permanent tables in the other database in lieu of an SSIS package. You can execute a job periodically to copy the appropriate data from the linked server to the local database and reference the local tables within your application. It will likely be easier to implement and maintain than an SSIS package, and could potentially be updated later to make it a live solution.

Source