Post reply

server name

  • September 20, 2006 at 9:51 am

    #114581

    I have several servers. When I run select @@servername , on some of them I get result with proper server name, however on some others I get Null. Should I change anything in setup, registry, etc, in order to get server name displayed for this function ? Re-installation of SQL Server is not an option.

    Thanks.

      

  • September 20, 2006 at 10:41 am

    #661548

    Have u tried:

    sp_dropserver 'ServerName'

    sp_addserver 'ServerName', 'local'

    Thanks

    Sreejith

  • September 20, 2006 at 11:42 am

    #661562

    I tried it but got a message:

    Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89

    The server 'server_name_here' already exists.

    But @@servername still returns Null

  • September 20, 2006 at 12:14 pm

    #661568

    Has some one messed with sysservers table?

    Try the code below that might help u.

    select * from master.dbo.sysservers

  • September 21, 2006 at 7:56 am

    #661739

    Thanks Sreejith, now I have some clue about this, but not a solution yet. I compared sysservers tables on my servers and found that those servers which return @@servername have srvid = 0 in sysservers, while servers which return null have srvid <> 0 in their respective system tables (or sys views which are actually the same).

    Since direct updating to system tables is very dangerous, I will have to find out some system sp_... to fix this problem.

     

     

  • September 25, 2006 at 11:28 am

    #662321

    @@servername is set on SQL Server startup. Even though you execute the sp_dropserver/sp_addserver the value is still null. You need to restart SQL Server.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • April 9, 2008 at 2:00 am

    #800203

    What ONE has to do after changing the Server name is that

    issue this SQL Statement to verify the name

    select @@servername <== This will return the oldName

    SELECT CONVERT(char(20), SERVERPROPERTY('servername')); <== This will return the NewName <== Because this shows the

    Networkname of the computer, and it is changed now.

    Select * from sys.servers

    it will give the list of Servers that are linked to the current servers via Network or Locally.

    Now issue this command

    sp_dropserver 'Oldservername'

    this will remove the oldservername

    sp_addserver 'Newservername' , 'Local'

    this will add the newservername to the sysserver catalog.

    now when we issue this again

    select @@servername <== This will return the oldName

    SELECT CONVERT(char(20), SERVERPROPERTY('servername')); <== This will return the NewName

    it will again showing the old and new name respectively.

    now what one has to do is simply restart the sql service, it will reconfigure it with the new name.

    select @@servername

    SELECT CONVERT(char(20), SERVERPROPERTY('servername'));

    Now both these statements shows the Same New Name

  • April 10, 2008 at 6:47 am

    #800937

    I had something similar happen a while back. We had a vendor product that created a linked server during installation. As such, the process altered the srvid so that select @@servername returned a null. As a workaround, we had a DNS entry created that pointed back to the server, tied the name of a linked server to that DNS entry, and had them rerun the install. Since then, we have had no issues.

    Not sure, but it sounds like your issue is more widespread than just one vendor install.

    ----------------------
    https://thomaslarock.com

  • June 19, 2008 at 3:07 pm

    #831435

    I had similar problem. When I checked for @@servername , the output was null.

    Turns out, the problem was, though the sql server name was same as machine name, the case did not match. In my case, the machine name was 'LV-SQL2' and the server name was 'Lv-SQL2'.

    I did the following :

    exec sp_dropserver 'Lv-SQL2'

    go

    exec sp_addserver 'LV-SQL2', 'local'

    go

    This fixed it.

  • June 19, 2008 at 3:10 pm

    #831436

    Collations and character sets ...

    They are so necessary and can cause so much ado ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • March 18, 2009 at 4:13 pm

    #962326

    Rudy,

    I have a VM SQL Server. When I copied the VM and created a new server, the SQL server in SELECT @@SERVERNAME is the original server.

    I followed the suggestions:

    exec sp_dropserver 'OLDSERVERNAME'

    go

    exec sp_addserver 'NEWSERVERNAME','local'

    go

    and then restarted the SQL Server instance.

    Now, SELECT *

    FROM sys.sysservers give me the NEW servername

    BUT the SELECT @@SERVERNAME is NULL.

    HELP!

    BTW the new servername is also a named instance....

  • March 19, 2009 at 7:44 am

    #962692

    Did you include the named instance portion (\whateverinstance) in the servername ?

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • March 19, 2009 at 7:45 am

    #962695

    Yes, I did include the named instance. (NEWSERVERNAME\Instance)

  • March 19, 2009 at 7:49 am

    #962699

    I'm now scratching my head ... going to grasp at a straw now ...

    was the server\instancename string single quoted or square bracketed ?

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • March 19, 2009 at 7:51 am

    #962701

    Here is what I ran:

    This will

    exec sp_dropserver 'hqsq_GOLD\NP' --the incorrect name

    go

    exec sp_addserver 'HQSQLTST01\NP','local' -- the correct name

    go

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

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