Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

[SQL] Question regarding Variables and Linked Servers

TK-42-1TK-42-1 Registered User regular
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.

TK-42-1 on
sig.jpgsmugriders.gif

Posts

Sign In or Register to comment.