A linked server configuration allows Microsoft® SQL Server to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:
- Remote server access.
- The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
- The ability to address diverse data sources similarly.
Linked Server Components
A linked server definition specifies an OLE DB provider and an OLE DB data source.
An OLE DB provider is a dynamic-link library (DLL) that manages and interacts with a specific data source. An OLE DB data source identifies the specific database accessible through OLE DB. Although data sources queried through linked server definitions are usually databases, OLE DB providers exist for a wide variety of files and file formats, including text files, spreadsheet data, and the results of full-text content searches. The following table shows examples of the most common OLE DB providers and data sources for SQL Server.
- You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.
- A local SQL Server login is required to be mapped to a login on the SQL Server you will link to. This local login should exist before you begin this procedure.
Click Start, click All Programs, > Microsoft SQL Server 2008 >, SQL Server Management Studio
connect to server as admin (sa) and Exapand Server Objects >Linked Servers
Right Click and Click on New Linked Server
In the dialog >General page,> enter the full network name of the SQL Server you want to link to. and server type= SQL Server
Select Security page
Map local server login to a remote server login. On the right side of the Security page, click the Add button.
impersonate means there is the same login of local server on remote server
Linked Server is now ready
Now to execute a Stored Procedure on remote Server
EXEC [RemoteServer] .DatabaseName.DatabaseOwner.StoredProcedureName]