For starters I'm using SQL 2005 and can't figure out if this is even possible. What I have is a number of linked servers that are offsite. I want to write a query that will iterate through each linked server and copy various bits of information to a local table. I'm pretty new to sql so if there's an easier way of doing this I'm open to anything.

dbo.STORE_REFERENCE contains the network name on the WAN, the store ID and a sequential number:

store_id   network_addy   cntr
0001       store0001      1
0002       store0002      2

What I want to do is reference the network_addy for use when querying the linked tables. This is what I have so far:

declare @cntr nchar(4)
set @cntr = 1
declare @address nchar(9)
set @address = (select network_addy from store_reference where cnrt = @cntr)

This returns what I want it to, but I can't get past this part. I don't know how to integrate that into the network name of the linked server. If i insert this statement to the end of it

Select complete from @address.store.dbo.history

I get an incorrect syntax. I tried it with @address being the full network address and it asks me to declare the variable. At this point im thinking the engine doesnt support a variable for the network address and i'm going to have to think of a different way to run through the linked servers. There are about 4 different variables I need to get from the reference table that are unique to each store. Aside from writing a query for each site, I'm stuck.