@@Servername, Developer Edition, XP Pro

Question

@@Servername, Developer Edition, XP Pro

Ronzo

Hall of Fame

Points: 3917
More actions
May 17, 2005 at 3:56 pm

#88502

We have an application that relies on the value of @@Servername, but on all three instances of developer edition SQL Server installed on our XP Pro test machine, this returns NULL. The default instance is 7.0, the two named instances are 2000 and 2005 (April).
Tried running sp_addserver, rebooting the instances (and even XP), but to no avail. Is this a known "feature", or am I missing something? Is there a system table I can update directly to fix this?
Thanks!
Have Fun!
Ronzo

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Hmmm, I'm running Developer (2000) as a named instance on an XP Pro machine (SP 1) and @@Servername returns the name of the instance.

Have you tried through query analyser, or just through your app?

Is the server perhaps case-sensitive (don't know if that would make a difference)

What's the patch level of the 7.0 and 2000 databases?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

What does SELECT SERVERPROPERTY('Servername') return?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Steven. SSCrazy Eights Points: 9806 More actions · Answer 3

The only time I have seen this is when certain registry keys have been deleted. ie the \CurrentVersion and \Parameters ones

from

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\

Steven

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 4

If I remember correctly @@servername relies on master..sysservers table. Can you examine its contents? There should be at least one row holding info about local server (isremote column=0)

Steven. SSCrazy Eights Points: 9806 More actions · Answer 5

I was thinking of the version info going, with the reg keys above.

To get rid of the null I usually use the following, code

exec sp_dropserver '<oldname>'

exec sp_addserver '<newname>', 'local'

Steven

deucellion Say Hey Kid Points: 680 More actions · Answer 6

Check to see that SELECT SERVERPROPERTY('Servername') and @@Servername return the same instance name. If they don't then drop the server and re add it as above.

Jason Hall Hall of Fame Points: 3906 More actions · Answer 7

@@servername will return null if you run sp_addserver 'servername' without the additional 'local' parameter. Conveniently enought Books online tells you to do this wrong. Always make sure that you add the 'local' parameter to sp_addserver and everything should be gravy.

Ronzo Hall of Fame Points: 3917 More actions · Answer 8

Thanks for all the fish!

The trick was dropping the server before trying to add it as "local". I had been trying to add it as local without first dropping it, using the "duplicate_OK" flag. Once I successfully dropped the server name (after taking care of some interfering replication), I was able to add it locally and then restart the instance.

Thanks again for all the input!

Have Fun!
Ronzo

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 9

Also, you will have to stop/restart the SQL Server service.

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