Linked Servers And String Exec

Hi.





I have been trying to find a solution to this for some time now. Was wondering if some1 had done is earlier and has a solution.





I have a 2 server machines.


Namely: ServerOne and ServerTwo





ServerOne (main server, On 1 machine.)


Table - Foofoo





ServerTwo (secondary server, on another machine)


Table - Booboo





I want to be able to link these two servers and work with them.





At the moment I do something like this.


NB. My Stored Procedure is on ServerOne





declare @server varchar(100)





Select @server=Servername from ServerOne.systemsettings where name='secondary'


-- @server is not equal to 'ServerTwo'


declare @str varchar(8000)


set @str = '


select *


from Foofoo f


join ' + @server + '.myDB.dbo.Booboo b on b.id = f.id '





exec(@str)





My problem is that this works fine but I do not like working with long strings and then executing them at the end.





I have also been told that SQL's performance on this is not entirely that well as normal select's would be.'





Another thing that could be used is SQl's own linked servers method but apparently out system was designed some time ago and a lot of things have been developed around the current technic.





Our server names also change quite frequently making hadcoding server names difficult.





Using the string exec convention also hides from sql when you do a dependency search of a particular table.





Is there a way I can save the server name on @server and then just add it to the select stmt without using the long stringing idea.





Any feedback with ideas and solutions will be greatly appreciated.





Bhit.