CODEWITHSUNDEEP
sqllinqlinqpad
user802599
user802599

Reputation: 828

Linq or SQL join tables on different servers

I have just started working for a new company where I am regularly having to query 8 different servers (all Microsoft SQL 2005 servers). At the moment every morning I am connecting to each server individualy every morning to read the latest entry in a [application].[tmpLogs] table. I have to use different users names and password for each server. I want to write a Linq or SQL query that I can just run in LinqPad to get the last row entered in the tmpLogs table on each server. Does anyone know how I can connect to all the servers and query the tables in one query?

Upvotes: 0

Views: 260

Answers (2)

Joe Albahari
Joe Albahari

Reputation: 30934

Do the tmpLogs tables have the same columns on each server? If so, you can take advantage of the fact that LINQPad lets you create new typed DataContexts with different connection strings.

Just connect to just one of your databases, and do this:

// Dump the log for the current database:
TmpLogs.OrderByDescending (l => l.Date).First().Dump();

// Dump the logs for other databases:
string[] otherConnectionStrings = 
{
   "server=...database=....etc",
   "server=...database=....etc",
   ...
}

foreach (string cxString in otherConnectionStrings)
{
   var dc = new TypedDataContext (cxString);
   dc.TmpLogs.OrderByDescending (l => l.Date).First().Dump();
}

Upvotes: 1

Richthofen
Richthofen

Reputation: 2086

You could choose one SQL Server as your 'master' server. Then set up the other servers as LINKED Servers (see http://msdn.microsoft.com/en-us/library/ms188279.aspx). You could configure a LINQ to SQL object to connect to the LINKED servers via the 'master' server.

You could also just take this out of LINQ and set up scheduled tasks to push the data into a 'warehouse' table periodically. It's easier to communicate with LINKED servers via Stored Procedures than it is via LINQ. As far as I know, LINQ doesn't contain the concept of DB catalogs, only tables. The catalogs are abstracted out in the DataContext object, which if you're using Linqpad, doesn't exist.

Upvotes: 1

Related Questions