SQL Server 2000 connection to multiple databases using 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?
Software/Hardware used:
Software/Hardware used:
ASKED:
October 9, 2008 11:20 PM
UPDATED:
October 9, 2008 11:50 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
