Querying multiple databases on SQL Server

Question

Hi

is it possible to create a view that selects from

1. multiple tables
2. in different databases
3. on different servers
4. using different login credentials

Can someone point to any online examples.

Or is there any way I can do it using Linq if I cant create a view

Answer 1

Yes, you'll want to use something called a linked server:

http://www.databasejournal.com/features/mssql/article.php/3085211/Linked-Servers-on-MS-SQL-Part-1.htm

You'll need to be database admin in order to set it up, though.

If you can't create a view, you will have to perform two separate LINQ to SQL queries using two separate DataContexts, and then call .ToList() or. ToArray() on the results, and then join them in a third LINQ query. This will work fine as long as you can limit each query to have a relatively small number of elements (under, say, 1000 or so from each LINQ query).

Answer 2

This type of implemenation/technique is also used in Federated Database Servers to distribute the processing load of large scale SQL Server database systems.

See the following Books Online reference for more details:

http://msdn.microsoft.com/en-us/library/ms190381.aspx

As per the prior posters instruction regarding setting up Linked Servers, you will subsequently need to create Partitioned Views, more details can be found in Books Online under there sub heading 'Partitioned Views':

http://msdn.microsoft.com/en-us/library/ms187956.aspx