Accessing Remote Data  «Prev  Next»

Lesson 3 Adding a linked server
ObjectiveAdd a linked server with Enterprise Manager or Transact-SQL

Enterprise Manager Linked Server

You can use Transact-SQL to add a linked server. Some points to remember when adding linked servers:
  1. With one exception (the OPENROWSET function that you will see later in this module), you need to add a linked server whenever you wish to access data that is on a remote server.
  2. A linked server must be able to communicate with an OLE DB data source, such as SQL Server 2012.
  3. You need to add a linked server only once. From then on, you simple write your queries against the linked server.
  4. Both servers need to set up the lines of communication, and therefore must add each other to their list of linked servers.

To use Enterprise Manager to add a linked server, follow the steps in the simulation below: < Remember that, to complete the connection, you will need to perform these steps on the server that you are you linking to.

Using Transact-SQL

The second way to add a linked server is by using Transact-SQL code. To use Transact-SQL code, use the sp_addlinkedserver system stored procedure. To add a SQL Server 2012 server, simply use this syntax:

Using Transact-SQL 
sp_addlinkedserver server_name

Using Transact-SQL

Dropping a linked server

If you wish to remove the linked server, use the sp_dropserver system stored procedure, passing it the name of the server you wish to drop.
You must use an extended syntax if you wish to use an OLE DB provider for any data source other than SQL Server. Refer to SQL Server Books Online for more information about this syntax.

Accessing Local SQL Server Database

When you access a second database on a single server, the same SQL Server engine processes the data. Therefore, although the data is outside the local database, the query is not actually a distributed query.
A SQL Server query may access another database on the same server by referring to the remote stored procedure, table, or view using the three parts of the four-part name. The complete four-part name is the full address of the object: 

Server.Database.Schema.Object

Because the database is on the same server, the server name is optional. Typically, the tables are in the database owner schema (dbo). If that’s the case, then dbo can be assumed: 
USE CHA2;
SELECT LastName, FirstName
FROM OBXKites.dbo.Contact;

Result (abbreviated): 
LastName      FirstName
------------ ------------
Adams        Terri
Andrews      Ed

The schema can be assumed to be dbo and ignored by just leaving the schema empty. The following query is functionally equivalent to the previous query, but does not specify the schema: In the next lesson, you will learn how to log on to the server using a remote login.