MS Sql and ODBC, and linked servers

Posted on 2009-07-11
Last Modified: 2012-08-14
Im sorry to keep at this but i feel like im really stuck.
I asked about two different ms sql on different servers and a VPN that can only connect to one and was given a link on how to do linked servers, that even though i think is usefull i have really no idea how to apply it. Do I have to do that inside the Database tools?

Second, would that help me with ODBC? If server one is and server 2 is and I I have and ODBC on that Remote that needs acces to both IP's if I do the Linked Server procedure i was given then I can run both odbc's no problem at all? Im guessing i have to go to the ODBC that points to the second IP and change the IP, but the question is, will both ODBC's work?
Question by:erenta
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
  • 2
  • 2
LVL 60

Accepted Solution

Kevin Cross earned 84 total points
ID: 24976056

Hopefully being in the proper zone will help bring others with SQL knowledge to come help; however, I give my $0.02 for now.

Having a linked server from one SQL Server to the other doesn't expose it to ODBC (the outside world) as you are indicating.  What it does do is allow you to perform queries on the other server and/or both at the same time by connecting to the server with the linked server configured.

Therefore, you can use your VPN connection to server1 and then perform queries like this:

-- on server1
select column_name
from mydatabase.dbo.table_name

-- on server2 given a linked server called linkedserver2
select column_name
from linkedserver2.myotherdatabase.dbo.table_name

If you have an ODBC connection to SQL server one that you perform queries through, you can then conceivably get data back from both databases through the one ODBC connection.  Just note that the connection is to the server1 and you are passing results from server2 through the linked server connection and not establishing an ODBC connection directly with server2.

To make things easier, you can even create a view or procedures that does the select on the linked server and then through ODBC you are only ever selecting / executing objects that reside in server1.

Hopefully that was not confusing.

Best regards,

LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 83 total points
ID: 24983163
>>  Do I have to do that inside the Database tools?

Assuming that you have the SQL Server Management Studio (SSMS) that is the easiest way. In the SSMS go to the Server Objects --> Linked Servers and right-click the window and do a New Linked Server. It should be fairly straight forward.

How to Create a Linked Server

To do it via a query analyzer/ SQL command you would use the sp_addlinkedserver stored procedure.

sp_addlinkedserver (Transact-SQL)

>> Second, would that help me with ODBC?

One ODBC call can only link to one server at a time. But  as mwvisa1 pointed out, you can create views in server1 that connect to server2. As far as the ODBC call/ client would see it is pulling from server1.
LVL 70

Assisted Solution

Qlemo earned 83 total points
ID: 24987751
If you are with MSSQL 2005:
Instead of views, you can create synonyms. Views rely on the underlaying table structure, and do not change when you add columns to the base table (that is the intended behaviour). Synonyms are just placeholders for the original table or view, a shortcut, so to say.

create synonym tbl1 for linkedserver.db.schema.table;

Each access to tbl1 will use the linked server's table.

I recommend to do that trick in a separate DB, created only for synonyms or views to the linked server. If you need to hide that from the application, this is. If you have access to application logic, it might be better to use the linked server syntax directly, and use variables to parametrize your queries to fit into other environments without linked server.
LVL 70

Expert Comment

ID: 25616720
I think all answers given are valid, including mine.
LVL 60

Expert Comment

by:Kevin Cross
ID: 25617034
I agree with Qlemo.  Although I have not used the synonyms through ODBC to ensure that the drivers will recognize, I have no reason to doubt they would strengthened by Qlemo saying they would. :)

Therefore, I am good with 3-way split. ;)

Featured Post

Technology Partners: We Want Your Opinion!

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question