How to add Linked Server in SQL Server

I use SQL Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) in this case.

In some condition we need to access different server and different database. Example we need to see data attendance in server A, and compared with server B (server or pc) with the same database type (in this case SQL Server)

Wondering how to do it ?, here the steps :
1. Check server that already linked in your pc

use master
select * from sysservers

2. Set linked server that want to add

-- add linked server
EXEC master.dbo.sp_addlinkedserver @server = N'156.17.22.100', @srvproduct=N'SQL Server'

-- set password to access database
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'156.17.22.100'
, @locallogin = NULL 
, @useself = N'False'
, @rmtuser = N'sa'
, @rmtpassword = N'passwordvalue'

3. Testing linked server connection

EXEC sp_testlinkedserver N'156.17.22.100'

if connection success then information displayed like this :

Then you can connect with the linked server.


You can try this :

-- example
select * from [Servername].[databasename].[ownername].[tablename]
select * from [156.17.22.100].[dbTest].[dbo].[Personal]

Technorati Tags: How to add Linked Server in SQL Server,linked server in sql server,view linked server,test linked server