@@SERVERNAME is NULL problem 2

[StevenK]

I have SQL Server 7.0 installed on a Windows 2000 machine.
All tools, Service Manager, Enterprise Manager, etc. indicate that the server is called 'STEVE_SQL' however when I run the command 'SELECT @@SERVERNAME' from Query Analyser I get the answer NULL.
Can anyone explain this ?
And suggest how I can correct this ?
It's causing havoc with one of my application that relies on the correct return from 'SELECT @@SERVERNAME'.
Any help would be appreciated.
Thanks in advance.
Steve

 

[SteveMac32]

I had a problem like this last week, but it was to do with the user being NULL, I mention this because the answer might be the same..?
In Entrprise Manager right click your server and select 'Edit SQL Server Properties' and check the 'Use SQL Server authentication' not the NT one.

hope this is helpfull

 

[StevenK]

Thanks for the suggestion.
Looking at the 'Resgistered SQL Server Properties' panel against the SQL Server in question I see details as expected.
The Server displays 'STEVE_SQL'.
The Connection is set to 'Use SQL Server authentication' with a Login Name as 'sa'.
The SQL Server Enterprise Manager and the SQL Server Service Manager correctly show the name 'STEVE_SQL'.
When I run 'SELECT @@SERVERNAME' through Query Analyser (or application code) the returned answer is NULL.
Any thoughts ?
Thanks again.
Steve

 

[SteveMac32]

I can only asume that your server has not been issued with a name, which is why you are getting null, not to sure where to goto next except make sure your server has a name.

This is what BOL has to say about it:
The Setup program sets the server name to the computer name during installation. You can change @@SERVERNAME by using sp_addserver and then restarting SQL Server; although this method is not usually required.

 

[StevenK]

Thanks again for the response.
I've tried the command as suggested (following review of BOL) and set combinations of 'local', 'duplicate_OK'.
If I do not make use of 'duplicate_OK' I get the message 'STEVE_SQL' already exists - if I do use 'duplicate_OK' and restart the SQL Server I still get a NULL as the return for the call 'SELECT @@SERVERNAME'.
Any more thoughts ?
Thanks for your advice.
Steve

 

[arrowhouse]

If you do this remember to add the 'local' parameter - this is missing from BOL Rename server see Q303774 on technet: BUG Renaming a Server Topic in Books Online is Incomplete

 

[StevenK]

arrowhouse : Where can I find this 'Q303774 on technet: BUG Renaming a Server Topic ' ?
thanks in advance
Steve

 

[SteveMac32]

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q303774

Should take you there

 

[StevenK]

I've referred to this and cannot determine the steps I should take.
My current return for 'SELECT @@SERVERNAME' is NULL.
Do I need to use 'sp_dropserver NULL' (will this work ??)
and then 'sp_addserver 'STEVE_SQL', 'local' ?
Should it be as simple as this ?
Steve

 

[SteveMac32]

NULL is the returning value, the field in which the server name is stored is empty and so has no value.
This is because there is no name assigned to the server so 'sp_dropserver NULL' will do nothing, the server is not called NULL..
Try this

sp_dropserver 'STEVE_SQL'
go
sp_addserver 'STEVE_SQL', 'local'
go

so hopefully this will work for you.

 

[StevenK]

Thanks to both stevemcmenamin and arrowhouse for your advice.
Following the issuing of those two commands, and a restart of SQL Server I now get the correct answer from the call 'SELECT @@SERVERNAME'.
Thanks for your help.
Steve