Saturday, March 24, 2012

Adding a Linked Server in SQL Server 2008

You can do the following in order to add a linked server to another SQL Server Instance

Step 1: Run the Add Linked Server Stored Procedure
EXEC sp_addlinkedserver
@server=N'Server1_Instance1', --Give a name to the linked server
@srvproduct=N'',
@provider=N'SQLNCLI', --Provider name for SQL Server
@datasrc=N'Server1\Instance1';

Step 2: Add credentials to access the linked server
If the linked server can be accessed via Windows Authentication this step is not required. But if you need to access is via SQL Server authentication you need to run the following procedure by replacing the text in bold appropriately.

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname= N'Server1_Instance1',@useself=N'False',@locallogin=NULL,@rmtuser=N'user_id',@rmtpassword='password'
GO

Step 3: Access the linked server
You can access the linked server in the following format
linked_server.database_name.schema_name.table_name
Example: Select * from Server1_Instance1.Database1.dbo.Table1

Digg It! Add to del.icio.us Stumble This

0 Comments: