·
To create a linked server (SQL Server 2005 and SQL Server 2008)
- Click Start, click All Programs, click Microsoft SQL Server 2005 or Microsoft SQL Server 2008, and then click SQL Server Management Studio.
- In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.
- In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.
- In the New Linked Server dialog box, on the General page, in Linked server, enter the full network name of the SQL Serveryou want to link to.
|
Note |
|
This procedure often refers to the server you are linking to as the remote server. This is for convenience only, to indicate the relationship of the linked (“remote”) server to the local server. Do not confuse this usage with the obsolete remote server functionality in SQL Server. |
- Under Server type, click SQL Server.
- In the left pane of the New Linked Server dialog, under Select a page, choose Security.
- You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.
- Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.
|
Note |
|
To use impersonation, your SQL Server configuration and login accounts must meet the requirements for delegation. For more information about impersonation and delegation, see http://go.microsoft.com/fwlink/?LinkID=132854. |
- In the left pane of the New Linked Server dialog, under Select a page, choose Server Options. Set the Rpc and Rpc Out parameters to True, and then click OK.
+++++++
A. Use the Microsoft OLE DB Provider for SQL Server
- Creating a linked server using OLE DB for SQL Server
This example creates a linked server named SEATTLESales that uses the Microsoft OLE DB Provider for SQL Server.
USE master
GO
EXEC sp_addlinkedserver
'SEATTLESales',
N'SQL Server'
GO
- Creating a linked server on an instance of SQL Server
This example creates a linked server S1_instance1 on an instance of SQL Server, using the OLE DB Provider for SQL Server.
EXEC sp_addlinkedserver @server='S1_instance1', @srvproduct='',
@provider='SQLOLEDB', @datasrc='S1\instance1'
B. Use the Microsoft OLE DB Provider for Jet
This example creates a linked server named SEATTLE Mktg.
Note This example assumes that both Microsoft Access and the sample Northwind database are installed and that the Northwind database resides in C:\Msoffice\Access\Samples.
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'SEATTLE Mktg',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
'SEATTLE Mktg',
'OLE DB Provider for Jet',
'Microsoft.Jet.OLEDB.4.0',
'C:\MSOffice\Access\Samples\Northwind.mdb'
