I have a need to have a linked server that can connect to multiple catalogs, but I need a proc to determine what catalog to connect to. So I need to be able to do something like this.
set @SQL = 'select * from linkname.' + @RemoteDBName + '.dbo.table' exec (@SQL)
I do not want to do this with dynamic sql though. It works great as long as you define @RemoteDBName. The issue with doing it this way is that SQL Server can not re-use a plan so you incur overhead of generating a plan each time the query runs. In my case it runs hundreds of times a minute. So that overhead adds up quickly.
After some searching on Google I found a few sites that mention using synonyms. So I created a synonym for every table that all of my queries hit for each of my linked servers. A bit tedious but the payoff was good.