instance alias

• smknox

  Hall of Fame

  Points: 3275

  More actions

  March 13, 2010 at 7:17 am

  #217790

  Is there a way to alias an instance within a stored procedure? Such as:

  [server2].GEMedical.dbo.TableName

  I am running sql 2000 and 2008 side by side on a single server, and thus [server2] is a linked server connection to access a db and table in the 2000 instance from within sql 2008. Ideally, I would like to pass in everything to the left of TableName, or at least use an alias.

• Jeffrey Williams

  SSC Guru

  Points: 89773

  More actions

  March 13, 2010 at 3:11 pm

  #1133256

  You can create a SYNONYM for that object - as in:

  CREATE SYNONYM dbo.MyTableName FOR [linked server].db.dbo.TableName;

  Then, in your code you reference your synonym:

  SELECT {columns} FROM dbo.MyTableName;

  If the linked server changes, or the database is moved to the local instance, you just drop the synonym and recreate it with the new values.

  Of course, if you want something that is dynamically defined by a parameter or something like that then you need use dynamic SQL.

  Jeffrey Williams
  Problems are opportunities brilliantly disguised as insurmountable obstacles.

  How to post questions to get better answers faster
  Managing Transaction Logs