Dynamically creating synonyms

• Clint-525719

  SSCrazy

  Points: 2924

  More actions

  September 9, 2009 at 5:03 pm

  #137997

  I need to read some server names from a table, and dynamically assign them to a synonym, then have that synonym be used in the following SQL statements. Basically, I'm trying to loop through a bunch of linked servers and retrieve certain data from them. I know the syntaxt for creating synonyms, but how can I drop then re-create with a new value inside a while loop?

  Any ideas would be appreciated. Thanks!

• Marcin Gol

  Hall of Fame

  Points: 3314

  More actions

  September 9, 2009 at 5:54 pm

  #1051506

  why you can't use dynamic sql + linked servers ? it looks much easier..

  1) select from table: my_magic_value; linked_server_name

  2) use proper linked server during building query

  3) fire it!

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  September 9, 2009 at 9:14 pm

  #1051541

  I got to admit this is cleaner that your solution. But I would do it ENTIRELY different. I would use SSIS to loop through the list. It is a lot more tolerant to errors too..

  CEWII