MS Sql and ODBC, and linked servers

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?
Who is Participating?
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:

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,

Jim P.Connect With a Mentor Commented:
>>  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.
QlemoConnect With a Mentor Batchelor and DeveloperCommented:
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.
QlemoBatchelor and DeveloperCommented:
I think all answers given are valid, including mine.
Kevin CrossChief Technology OfficerCommented:
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. ;)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.