The Multifunctioning DBA

Sep 19 2012   9:24PM GMT

Using a Variable in a Linked Server

Colin Smith Colin Smith Profile: Colin Smith


The Problem.

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.

The solution.

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.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: