0

A vendor application has a routine that does some validation by using @@Servername in SQL. This returns the name of the instance but I would prefer it returned the name of the Alias (SQL defined) for that instance. Is it possible? Examples: Currently

DECLARE @ServerName SysName
select @ServerName = @@SERVERNAME
SELECT @ServerName as ServerName

returns the instance

ServerName
---------------------
MyDBServer\MyInstance

I would prefer that it could be configured somehow to return the SQL configured alias

ServerName
---------------------
customerAlias

Any help or insight would be appreciated.

Paul Wichtendahl
  • 117
  • 7
  • 1
    "the name of the Alias (SQL defined) for that instance." What alias are you referring to? If you mean this one https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/create-or-delete-a-server-alias-for-use-by-a-client?view=sql-server-ver16 it's a client-side-only thing, the client should know what they are using to connect – Charlieface Apr 26 '23 at 22:23
  • 1
    he probably means he wants to override the output of @@servername to be some custom string (for *whatever* reason) – siggemannen Apr 27 '23 at 07:42
  • Perhaps you can use sp_addserver to change the name, as stated in the @Charlieface 's link. But i wouldn't recommend it since something else probably breaks – siggemannen Apr 27 '23 at 07:44
  • First, thank you all for your comments. They lead me to pursue the question in a couple of different ways that helped me in the current situation and lay out a path forward. @Charlieface the reason I need this has to do with third party application support where the vendor has embedded calls to the @@SERVERNAME property. I have had the need to move databases to different servers several times and the result is that I have to reconfigure the servers/clients every time which is a disruption to the users and impedes progress. It would be best for all concerned if that was not the case. – Paul Wichtendahl Apr 27 '23 at 16:59
  • For anyone else exploring aliases and the SERVERNAME issue. There is a way to do this but I would not recommend it for a production environment. I will be exploring how the cliconfig utility works SQL2022 (usable in 2019 so far) and if that provides options. If it does I will post an update here. Marco Russo has a couple of nice early write-ups on this utility. – Paul Wichtendahl Apr 27 '23 at 17:03
  • I'm still confused: if the vendor is querying `@@SERVERNAME` then they will have the correct value. Perhaps you just need a network redirect (such as a load balancer) to redirect the connection to the correct server. – Charlieface Apr 27 '23 at 18:07
  • @Charlieface just to explain. The instance Name includes the reference to the *physical* server. we have had to, for several reasons, move the DB to a different server. An engineering product license is tied to the return of this call. when the server changes it requires us to go through re-licensing of the product which has historically been a major pain and takes days of delays. if @@servername could return the alias instead of the instance name moving the DB would take minutes. – Paul Wichtendahl May 22 '23 at 18:38
  • 1
    Sounds like you should virtualize the server and then you can move it wherever you like without changing any names. The DNS name as used in the connection string is separate, and if you virtualize it then you can use dynamic DNS to redirect it where you want. – Charlieface May 22 '23 at 19:14

0 Answers0