SQL Server 2000 connection to multiple databases using variables

120 pts.
Tags:
Multiple databases
SQL Server 2000
Variables
I have a fairly simple select statement that joins three tables from two SQL Server 2000 databases. It works fine, but now I need to run the stored procedure against different servers, so my hardcoded reference to the second database (currently coded as "secondDB.tableName.columnName") needs to be changed to "@Variable.tableName.columnName" except SQL Server doesn't like that. Is there a way to reference a remote server/database using a variable?

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think you will need to use dynamic sql to achieve this.

Something like this:

<pre>create procedure sp1
@db_name varchar(50)
AS
BEGIN
DECLARE @sqlCommand varchar(1000);
SET @sqlCommand = ‘SELECT ‘ + @db_name + ‘.tableName.columnName FROM ‘ + @db_name + ‘.tableName, table2, table3 WHERE fieldX = 5′
EXEC (@sqlCommand)
END</pre>

Or I guess you can change the select statement to use an alias for the table, and use the variable just in the where clause:

<pre> SET @sqlCommand = ‘SELECT ALIAS.columnName FROM ‘ + @db_name + ‘.tableName ALIAS, table2, table3 WHERE fieldX = 5′</pre>

Just remember that dynamic sql pose some security issues, that need to be taken into consideration.

Hope this helps.

Discuss This Question:  

 
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 members answer or reply to this question.

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:

To follow this tag...

There was an error processing your information. Please try again later.

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

Thanks! We'll email you when relevant content is added and updated.

Following