How to get a value from a table in different server in SQL Server 2005? [Resolved]

Posted by Csmaniam under Sql Server on 8/26/2010 | Points: 10 | Views : 2642 | Status : [Member] | Replies : 2
Hi All,
I want to do a query in which one of the value is from a column which is in a different server. My query is executing in a server and one of the values to be fetched from a table in a different server.Can anyone suggest me some way to do this?

Thanks in advance.

Subramaniam.C


Responses

Posted by: PandianS on: 8/26/2010 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi

You can use Linked Server.

Steps:
1. Step into your server\Instance (Server1)
2. Define a Linked server for that server which you want to access (Server2)
EXEC SP_AddLinkedServer 'Server2'

Go

3. Provide login credential for the Linked server just now you created.
EXEC SP_AddLinkedSrvLogin 'Server2','False','Current_Server1_LoginID','Server2 Login ID','Server2 Password'

GO

4. Validate whether the Linked server properly defined / not
EXEC SP_TestLinkedServer N'Server2'

Go

It should return "Command(s) completed successfully."

5. Once you defined the Linked server. You can access the objects from Server2 by Four part naming convention. (ServerName.DatabaseName.SchemaName.ObjectName)
Select * From Server2.DatabaseName.Dbo.TableName WITH (NOLOCK)

Go

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Csmaniam, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Csmaniam on: 8/26/2010 [Member] Starter | Points: 25

Up
0
Down
Thank you Pandian.its really working fine..

Subramaniam.C

Csmaniam, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response