February 8, 2007 at 3:05 pm
I have 30+ servers(SQL2000) linked to SERVER_A (SQL2000). My goal is to run a query from SERVER_A that will hit SERVER_1, or SERVER_2,or any number of servers, whatever I select when inputing server name(s). My initial step gives the correct results
DECLARE @sqlstring nvarchar(100),@property char(8)
set @property = 'SERVER_1'
set @sqlstring =(@property+'pms.'+@property+'.dbo.trancode')
print @sqlstring
Returning a value of:
SERVER_1pms.SERVER_1.dbo.trancode
I add the following statement:
select * from @sqlstring
Giving me the statement:
DECLARE @sqlstring nvarchar(100),@property char(8)
set @property = 'SERVER_1'
set @sqlstring =(@property+'pms.'+@property+'.dbo.trancode')
print @sqlstring
select * from @sqlstring
It returns the following error message:
Server: Msg 137, Level 15, State 2, Line 9
Must declare the variable '@sqlstring'.
I'm assuming it thinks @sqlstring is now a table variable.
How can I run a query against servername that will change, depending on a variable?
SELECT * FROM variablepms.variable.dbo.trancode
February 8, 2007 at 3:56 pm
You cannot have a variable of any data type except type TABLE in a FROM clause in a DML statement. A table variable is not a variable that hold the table name. Read up on table variables in BOL. For what you are trying to do, you will have to use dynamic SQL and execute a prepared string of SQL using EXEC or sp_executesql. For you example, this should work:
DECLARE @sqlstring nvarchar(100),
@property char(8),
@CMD nvarchar(4000)
set @property = 'SERVER_1'
set @sqlstring =(@property+'pms.'+@property+'.dbo.trancode')
--print @sqlstring
SET @CMD = 'select * from ' + @sqlstring
EXEC(@CMD)
February 9, 2007 at 8:26 am
It works. Thanks John for your help.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply