Click here to Skip to main content
14,327,936 members
Rate this:
Please Sign up or sign in to vote.
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
Rate this:
Please Sign up or sign in to vote.

Solution 1

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



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




Hope it will helpful. If helped Vote
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100