Click here to Skip to main content
15,071,928 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
Hi All,

I need to write a query on Mutlple servers.

like Server1 contains Database1 which contains Table1
in the same way another server instace is Server2 contains Database2 which contains Table2

i have to create a join between the above specified tables.

i tried with 'Central management servers' but that doesn't helps me.


Thanks in advance.
Posted

SQL
select    *
from    [OtherServerName].[OtherDB].[dbo].[OtherTable]



 SELECT        *
FROM        [SERVER2NAME].[THEDB].[THEOWNER].[THETABLE]




Hope it will helpful. If helped Vote
   
You should link one server to another (please refer the following link for details: http://msdn.microsoft.com/en-us/library/ms188279.aspx[^]) and then you be able to create queries for example for query on server1
SQL
SELECT a.*, b.* FROM [Server2].[Database2].[YourSchema2].[Table2] AS a
  JOIN [Database1].[YourSchema1].[Table1] AS b
    ON a.SomeColumn2 = b.SomeColumn1
   
Comments
naveenvenkanna 7-Nov-12 4:42am
   
getting error like
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
skydger 7-Nov-12 4:56am
   
You need to set user mappings for the second server. This article could be useful: http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900