I want to create link with 2 servers

I want to create link with 2 servers is that possible to do that. for example is like this,

-

SELECT Products.ProductID,

Products.ProductName,

Products.CategoryID,

Categories.CategoryName

FROM [SERVER1].[Northwind].[dbo].[Products]

INNER JOIN

[SERVER2].[Northwind].[dbo].[Categories]

ON [Products].[CategoryID] = [Categories].[CategoryID]

--

Products table has SERVER1 and Categories table has SERVER2

Yes, this is possible but not very performant, because you have to retrieve all the data on the local server / query engine for doing your query stuff like joining, this can be vry expensive.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thank you for your post. Any better ways 2 do that I want create view using 3 tables in 2 servers.|||

You can Create a Linked Server and Use it like the way u have written the Query.

-Sivaraman Latchapathi

|||But that SQL query doesn’t work. Please can u post sample code?|||OK, why not using one server as therefore there would be one server with local data then. From that server you could pull out your joined data. The query would work if you prior would have setup the linked server. Either do that inthe graphical use interface, or use the procedure sp_addlinkedserver. For more information and examples, have a look in the BOL.

HTH, Jens Suessmeyer-

http://www.sqlserver2005.de