Distributed Query: using machine name in a variable in sql statement

• munzer haque

  Ten Centuries

  Points: 1187

  More actions

  January 13, 2005 at 11:15 pm

  #86588

  Distributed Linked Query: Passing and using machine name in a variable in a Stored Procedure

   

  What is the correct syntax to use the machine name in a stored procedure that is passed to it as a parameter?

   

  Example:

  I have 3 machines, Alpha, Beta, and Gamma. SQL server 2000 are properly installed, linked,

  and tested in this 3-machine based distributed db, distributed query system.

  Following Test1 stored procedure works just fine when executed remotely (from any of the 3 linked server).

   

  CREATE PROCEDURE Test1

  AS

  select * from Alpha.Northwind.dbo.employees

  GO

   

  So far so goo.

   

  My application doesn't know ahead of time which machine to use in the above SELECT query.

  Therefore, my application dynamically, during run time, figures out what machine need to be used.

   

  Hence:

   

  CREATE PROCEDURE Test2

  @ThisMachine varchar(10)

  AS

  select * from @ThisMachine.Northwind.dbo.employees

  GO

   

  Test2 is giving me compilation error.

   

  I also tried putting square bracket around @ThisMachine but it did not help.

   

  I will greatly appreciate if you could tell me the correct syntax for using machine name in a variable.

   

  Similarly, I would also like to know how can one use database name, table name,

  column name in a variable in a SQL statement.

   

  Thanks.

   

  Munzer Haque

  mhaque4@comcast.net

• babuganesh2000

  Hall of Fame

  Points: 3123

  More actions

  January 14, 2005 at 1:43 pm

  #537538

  CREATE PROCEDURE Test2

  @ThisMachine varchar(10)

  AS

  Declare @sql varchar(1000)

  SELECT @sql = 'select * from   '+ @ThisMachine + '.Northwind.dbo.employees'

   

  exec (@sql)

  GO

   

  I hope this will do