Go Premium for a chance to win a PS4. Enter to Win


MS Sql and ODBC, and linked servers

Posted on 2009-07-11
Medium Priority
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
  • 2
  • 2
LVL 60

Accepted Solution

Kevin Cross earned 336 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 332 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 71

Assisted Solution

Qlemo earned 332 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 71

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

963 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