Get local server's alias in T-SQL

• schleep

  SSChampion

  Points: 12582

  More actions

  January 19, 2021 at 5:12 pm

  Go to Answer

  #3835132

  Hello,

  I'm trying to determine if there's a way to get a server's alias programmatically?

  Scenario: Using SQL Configuration Manager, I've created an alias for the server FQDN\InstanceName as "Murphy". All well and good.

  Now, let's say I'm connected to Murphy. When I select @@SERVERNAME and SERVERPROPERTY('ServerName'), I get FQDN\InstanceName

  I tried

  exec sp_dropserver " FQDN\InstanceName"
  go
  sp_addserver "Murphy", 'local'
  go?

  But @@SERVERNAME and SERVERPROPERTY('ServerName') both still return FQDN\InstanceName

  I need to do stuff like IF <localservername> = "MURPHY" ...

  We're about to get a whole bunch of new servers, and I've realized that I want to never again have to go through all the code and replace the hard coded machine / FQDNs with new ones, when all I should have to do is keep the aliases on the new machines.

  Thanks!!

   

• Site Owners

  SSC Guru

  Points: 80305

  More actions

  January 20, 2021 at 6:10 pm

  #3835615

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• schleep

  SSChampion

  Points: 12582

  More actions

  January 25, 2021 at 11:44 am

  Answer

  #3837097

  Hmmm... my OP had appeared to time-out without posting, which is why I reposted the question.

   

  The solution was to restart the SQL Server service.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 730761

  More actions

  January 25, 2021 at 4:26 pm

  #3837233

  This is a change that requires a restart for the service. Glad you got it working.

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com