That works OK when you have one single query you wish to run against one linked server.

However the situation I have is that I have two environments each with several linked servers.

Depending on which environment calls the sproc, I need each query to use the relevant linked server.

Additional issue is that I need to JOIN data from all of them.

Eg - (and this is a dumb example, but it illustrates what I'm trying to do)

environment1:- [SQLSRv1].[DBOrders_EMEA] and [SQLSRV2].[DBCustomers_EMEA] and [localhost].[DBHome]

environment2:- [SQLSRV3].[DBOrders_USA] and [SQLSRV4].[DBCustomers_USA] and [localhost].[DBHome]

with the query being run from [localhost].[dbhome].

So based on what params are passed into the sproc, determine which environment to query against.

Ideally (although I know this isn't possible), something like this is what I was aiming for.

DECLARE @OrdersDB VARCHAR(100)

DECLARE @CustomersDB VARCHAR (100)

IF BLAH BLAH /* and equates to it being in EMEA*/

BGEIN

SELECT @ordersDB = '[SQLSRv1].[DBOrders_EMEA]'

SELECT @customersDB = ' [SQLSRV2].[DBCustomers_EMEA] '

END

SELECT TOP 1 foo.ID, bar.blah, O.lastorder, C.age

FROM Foo

INNER JOIN bar on bar.ID = foo.barID

LEFT OUTER JOIN @OrdersDB.dbo.OrderTable O ON foo.orderID = O.OrderID

INNER JOIN @CustomersDB.dbo.CustomersTable C ON Bar.CustomerID = C.CustomerID

ORDER By O.DateProcessed DESC

The joins are a lot more complex and lengthy than that and the data sets are of the order of a couple of millions rows.

I cant seem to ram this into the querytype specified in the http://support.microsoft.com/kb/314520/en-us KB, mainly I guess as I'm joining on > 1 server. Any ideas? Thanks.