Post reply

Getting @@ServerName into a variable or table

  • September 18, 2014 at 8:46 am

    #397367

    Hi,

    I am trying to store the server name from a query into a variable or table.

    Declare @ServerName Varchar(30)=cast(@@SERVERNAME as varchar(30))

    Print @servername

    It keeps returning null. Any suggestions?

    thanks

  • September 18, 2014 at 8:50 am

    #1746430

    Try:

    Declare @ServerName sysname

    select @ServerName = @@servername

    Print @ServerName

  • September 18, 2014 at 9:00 am

    #1746436

    Thanks for the response. Still "NULL"

    richard.bowles (9/18/2014)

    Try:

    Declare @ServerName sysname

    select @ServerName = @@servername

    Print @ServerName

  • September 18, 2014 at 9:01 am

    #1746437

    What does

    select @@servernamereturn?

  • September 18, 2014 at 9:05 am

    #1746441

    Now we're onto something.

    On my local instance (2012) :

    SELECT @@SERVERNAME

    returns the name of my local server

    On the DEV team's server (2008R2) :

    SELECT @@SERVERNAME

    returns NULL

    richard.bowles (9/18/2014)

    What does

    select @@servernamereturn?

  • September 18, 2014 at 9:08 am

    #1746442

    What output do you get On the DEV team's server (2008R2) for:

    select * from sys.servers

  • September 18, 2014 at 9:12 am

    #1746447

    I get a list of about 9 servers

    Server_ID, Name, Product, Provider.

    But NOT the name of the Dev server that I run the : select * from sys.servers from

    richard.bowles (9/18/2014)

    What output do you get On the DEV team's server (2008R2) for:

    select * from sys.servers

  • September 18, 2014 at 9:17 am

    #1746450

    You need to add the server name (the DEV team's server) to sys.servers (I am not sure how it is NOT there, unless the server has been renamed):

    sp_addserver [new_name\instancename], local;

  • September 18, 2014 at 9:23 am

    #1746454

    Thanks. It now shows up in select * from sys.servers

    However select @@ServerName still returns null (curious)

    I can use this workaround if needed :

    select * from sys.servers where is_linked=0

    richard.bowles (9/18/2014)

    You need to add the server name (the DEV team's server) to sys.servers (I am not sure how it is NOT there, unless the server has been renamed):

    sp_addserver [new_name\instancename], local;

  • September 18, 2014 at 9:24 am

    #1746456

    The variable @@servername may get empty when you rename the windows machine.

    I've also seen it happening in other situations but don't remember how.

    However it's safer to use SERVERPROPERTY('Servername') rather than @@SERVERNAME for this reason.

    -- Gianluca Sartori

  • September 18, 2014 at 9:27 am

    #1746457

    thank you all. both marked as sol'n

    Cheers!

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy