Solved

MS Sql and ODBC, and linked servers

Posted on 2009-07-11
8
357 Views
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 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?
0
Comment
Question by:erenta
  • 2
  • 2
8 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 84 total points
ID: 24976056
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
 
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
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
 
LVL 69

Assisted Solution

by:Qlemo
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.
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
 
LVL 69

Expert Comment

by:Qlemo
ID: 25616720
I think all answers given are valid, including mine.
0
 
LVL 59

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. ;)
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

777 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