Query tables from selected server(s)

• bpullias

  Grasshopper

  Points: 16

  More actions

  February 8, 2007 at 3:05 pm

  #171343

  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

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  February 8, 2007 at 3:56 pm

  #688694

  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)

   

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• bpullias

  Grasshopper

  Points: 16

  More actions

  February 9, 2007 at 8:26 am

  #688864

  It works.  Thanks John for your help.