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 192.160.130.100 and server 2 is 192.168.130.136 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?
erentaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
erenta,

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,

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.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
http://msdn.microsoft.com/en-us/library/aa560998%28BTS.10%29.aspx

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

sp_addlinkedserver (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms190479%28SQL.90%29.aspx

>> 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.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
E.g.:

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.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I think all answers given are valid, including mine.
0
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. ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.