How to data of a table from one server in another in SQL Server?

Posted by Mahe under Sql Server on 6/18/2012 | Points: 10 | Views : 1104 | Status : [Member] | Replies : 10
Hi All,

How to fetch data of a table from one server in another in SQL Server ?

Regards,
Mahe




Responses

Posted by: CGN007 on: 6/18/2012 [Member] Silver | Points: 25

Up
0
Down
For that you will have to link the second server to your first current server using linked servers option in enterprise manager or using the stored procedure called sp_addlinkedserver.

Please see thishttp://stackoverflow.com/questions/720339/how-to-select-data-of-a-table-from-another-database-in-sql-server


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

Posted by: CGN007 on: 6/18/2012 [Member] Silver | Points: 25

Up
0
Down
Please also refer more details about sp_addlinkedserver in mSDN
http://msdn.microsoft.com/en-us/library/ms190479.aspx

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

Posted by: CGN007 on: 6/18/2012 [Member] Silver | Points: 25

Up
0
Down
Example:

exec sp_addlinkedserver @server = 'test'


then

select * from [server].[database].[schema].[table]


In your example:

select * from [test].[testdb].[dbo].[table]


Source :http://stackoverflow.com/questions/720339/how-to-select-data-of-a-table-from-another-database-in-sql-server

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

Posted by: CGN007 on: 6/18/2012 [Member] Silver | Points: 25
Posted by: CGN007 on: 6/23/2012 [Member] Silver | Points: 25

Up
0
Down
Please mark as answer,if it helps ...

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

Posted by: Kirthiga on: 6/25/2012 [Member] Starter | Points: 25

Up
0
Down
By using LinkedServer we can fetch data from one server to other

By Executing this query we made connection between two server
Exec sp_addlinkedserver [ServerName]


By using this query we can fetch data from other server
select * from [ServerName].dbo.[TableName]


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

Posted by: CGN007 on: 7/31/2012 [Member] Silver | Points: 25

Up
0
Down
please mark it as answer....That helps other who search the same...

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

Posted by: Ranjeet_8 on: 7/31/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
SELECT t1.Col1

, t2.ColA
FROM server1Table t1
INNER JOIN OPENROWSET('SQLNCLI', 'Server=Server2;Trusted_Connection=yes;',
'SELECT t2.ColA, t2.TheId FROM dbname.dbo.tableName') AS t2
ON t1.TheId = t2.TheId


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

Posted by: Ranjeet_8 on: 7/31/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
@Mahe

plz Mark As Answeer, if above post helps u.

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

Posted by: Pandians on: 7/31/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check It Out!

Try to learn about the following system procedures!
Sp_AddLinkedServer 'DestServerName'

Sp_AddLinkedSrvLogin
Sp_TestLinkedServer
Once you configured the Linked server to your destination server

You have to use Four part naming convention to access the objects from destination server
Select * from DestServerName.DatabaseName.Schema.ObjectName

Go


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Login to post response