How to RENAME the SQL server if the Work Station name is changed after the SQL Server installation. I have seen ppl. saying that we should use sp_dropserver and sp_addserver, but if the SQL server is not running how can u run these SP's ???
Verbatim from the Holy Book (SQL Server Books Online)
Renaming a Server
When you change the name of the computer that is running Microsoft® SQL Server™ 2000, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name.
You can connect to SQL Server using the new computer name after you have restarted the server. However, to correct the sysservers system table, you should manually run these procedures:
sp_addserver new_name, local
One other issue that I ran into when doing this (dunno if the sp_dropserver fixes it). All the jobs that I had set up under the old server name became Target Server jobs (MSX/TSX) and I could not modify them. I think I managed to change them back to "normal" jobs by editing the sysjobs table (originating_server) directly.
It was an unpleasant surprise in an otherwise smooth migration.