@@servername ????????? empty output

Question

Post reply

@@servername ????????? empty output

fasttrack

SSCertifiable

Points: 6071
More actions
October 13, 2005 at 2:46 pm

#165654

Strange thing happen....
I have been using an sql code that include @@servername to retrieve name of local server.
I tried this on several SQL server and it works, but after others test I have found out an error during batch process......the error occur because in an sql server the system variable @@servername was empty.
I run the statement print @@servername and the output was empty ....

anyone knows why in some sqlservers @@servername is an empty string ??????

thank a lot

Viewing 14 posts - 1 through 13 (of 13 total)

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

ND-215452 SSCrazy Points: 2037 More actions · Answer 1

possible that master db is corrupted.

if you have a backup of master db , restore it.

mark baekdal-145375 SSCrazy Points: 2200 More actions · Answer 2

try using:

select serverproperty('servername')

if this is different to @@servername you need to use sp_addserver to make the output the same.

regards,

Mark Baekdal

MSN m_baekdal@hotmail.com

+44 (0)141 416 1490

+44 (0)208 241 1762

http://www.dbghost.com

http://www.innovartis.co.uk

Build, Comparison and Synchronization from Source Control = Database change management for SQL Server

vadba SSChampion Points: 11133 More actions · Answer 3

After using sp_addserver, you'll have to stop, then restart, SQL Server.

EXEC sp_dropserver 'yourServerName'

EXEC sp_addserver 'yourServerName', 'local'

Stop SQL Server.

Restart SQL Server.

Also, try either or both of these:

EXEC sp_helpserver

and

SELECT srvid

, srvname

, datasource

, srvnetname

FROM master..sysservers

barsuk SSChampion Points: 12280 More actions · Answer 4

I would suggest to replace

EXEC sp_dropserver 'yourServerName'

with

EXEC sp_dropserver 'yourServerName,'droplogins'

noeld SSC Guru Points: 96590 More actions · Answer 5

anyone knows why in some sqlservers @@servername is an empty string ??????

This happens when the server is renamed

Read previous post for the solution!

* Noel

Tahir-191992 Mr or Mrs. 500 Points: 584 More actions · Answer 6

On named instance

SELECT @@servername

gives NULL

Kindest Regards,

Tahir

Tahir-191992 Mr or Mrs. 500 Points: 584 More actions · Answer 7

On named instance

SELECT @@servername

gives NULL

Kindest Regards,

Tahir

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

After executing sp_adderver ...,local you need toi stop/restart SQL Server.

Just a little 'undocumented feature' that's been around since v4.21 ...

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

noeld SSC Guru Points: 96590 More actions · Answer 9

That is not correct! I have a named instance in my development computer an it gives me : COMPUTERNAME\INSTANCENAME

* Noel

fasttrack SSCertifiable Points: 6071 More actions · Answer 10

Thank to everybody,

added local server and stop/restart server .......OK

Tahir-191992 Mr or Mrs. 500 Points: 584 More actions · Answer 11

Thanks dear

EXEC sp_addserver 'servername', 'local'

works just fine.

Kindest Regards,

Tahir

fasttrack SSCertifiable Points: 6071 More actions · Answer 12

fasttrack

SSCertifiable

Points: 6071

October 19, 2005 at 2:24 pm

#598478

Thank to everybody.......

DB_Newbie2007 SSCarpal Tunnel Points: 4448 More actions · Answer 13

Thanks for the suggestion about using EXEC sp_dropserver 'yourServerName,'droplogins'.

We were just trying to use EXEC sp_dropserver 'yourServerName without the droplogins directive and received errors about a linked server (another developer was using the system with permission).

Dropping the server, re-adding it (sp_addserver...), stopping and re-starting the service corrected the issue.

Now we also get the correct server_id = 0 for the SQL instance in sys.servers (or srvid = 0 in master.dbo.sysservers).

Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)