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 10 (of 10 total)

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