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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: