Quote of the Day

Wednesday, November 23, 2005

SQL @@SERVERNAME can sometimes return NULL

In our SQL Server applications we have the need of connecting to different servers (ie. development, test, production). In order to know which server/database we are connected to I have some code that executes at startup after connecting to append this information to the _vfp.caption.


IF FILE("DEBUG.TXT") OR FILE("DISPLAYDBINFO.TXT")
lnResult = SQLEXEC(lnHandle,"SELECT @@SERVERNAME AS servername","servernamecursor")
IF lnResult > 0
_vfp.Caption = _vfp.Caption + " - "+ALLTRIM(servernamecursor.servername)
USE IN servernamecursor
lnResult = SQLEXEC(lnHandle,"SELECT DB_NAME() AS db_name","dbnamecursor")
IF lnResult > 0
_vfp.Caption = _vfp.Caption + "/"+ ALLTRIM(dbnamecursor.db_name)
USE IN dbnamecursor
ENDIF
ENDIF
ENDIF
This worked great for years until.... Not sure what SP or update caused it but @@Servername started returning NULL!

We've found 2 possible solutions to this:

1. Try using SELECT ServerProperty('servername') instead of SELECT @@SERVERNAME
2. Perform the following in Query Analyzer:
sp_dropserver 'MYSERVER'
sp_addserver 'MYSERVER', 'local'
Note: this requires you to restart SQL Server before @@SERVERNAME is fixed.

Additional info: http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic4087.aspx

No comments: