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 : 1158 | Status : [Member] | Replies : 10

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi All,

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

Regards,
Mahe

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	Also check thishttp://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server Mahe, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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

0	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

0	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

0	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

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

0	@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

0	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

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

Latest Posts