Accessing Remote Data  «Prev  Next»

Lesson 6 Accessing remote data with a linked server
Objective Practice accessing remote data with a linked server.

Accessing Remote Data using Linked Server

In this lesson, assume that you are starting from ServerA and need to access a stored procedure that is located on ServerB. The specifications of this stored procedure are as follows:

Name usp_TrimSpace
Server ServerB
Database Accounting
Database owner dbo
Take the following steps to access and run this stored procedure:
  1. Add the link to ServerA from ServerB:
    sp_addlinkedserver ServerB
  2. Create your query from ServerA:
    EXECUTE ServerB.Accounting.dbo.usp_TrimSpace
The statement above will execute the usp_TrimSpace stored procedure from ServerA. The result will be exactly the same as running usp_TrimSpace from ServerB. Note that a fully qualified path was specified in the above example.

Joining remote tables

Now, Assume that you are on ServerA, and that you have already linked to ServerB. You need to join the sales table on ServerA with the customers table in the Accounting database on ServerB.

Joining remote tables

Now, Assume that you are on ServerA, and that you have already linked to ServerB. You need to join the sales table on ServerA with the customers table in the Accounting database on ServerB.

Transactions and stored procedures

You should also know that you can use transactions with your linked servers by using the BEGIN DISTRIBUTED TRANSACTION Transact-SQL statement. Before issuing this statement, you must have the MSDTC Service running.
In the next lesson, you will learn about using two special functions available in SQL Server 2012 for accessing remote data within your queries.

Linking to External Data Sources

SQL Server is also capable of establishing a link to any other data source that is ODBC- or OLE DBcompatible. The link can be created using Management Studio or T-SQL code.

Linking to SQL Server with Management Studio

A link to another SQL Server can be established by means of Management Studio or code. Within Management Studio's Object Explorer, linked servers are listed under the Server Objects node.
Selecting Linked Servers -> context menu -> New Linked Server opens the New Server Properties form (see Figure 6-6).

Selecting the server

In the General tab of the Linked Server Properties form, enter the name of the external SQL Server in the Linked Server field, and click the SQL Server button in the Server Type section. To link to a named instance of SQL Server, enter the instance name as server\instance without square brackets.
In Figure 6-6, the linked server is MAUI\COPENHAGEN. SQL Server 2008 can link to any other SQL Server 2000, 2005, or 2008 instance, or to a SQL Server 7 server, but SQL Server 2008 will not link to a SQL Server 6.5 server without going through an OBDC driver.

Figure 6-6: The Linked Server Properties form
Figure 6-6: The Linked Server Properties form

Accessing Remote - Data - Exercise

Click the Exercise link below to practice adding a linked server and accessing a remote stored procedure.
Accessing Remote - Data - Exercise