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:
Take the following steps to access and run this stored procedure:
Add the link to ServerA from ServerB: sp_addlinkedserver ServerB
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.