Which version of SQL Server?
For 7:
1. Change the name of the computer
2. Run the SQL Server setup off the CD. It will detect a SQL Server install and want to do an upgrade. All the upgrade will do is correct registry entries to the new computer name.
3. To be safe, reinstall any service packs
4. Use sp_dropserver @server=old_server_name to remove the old server name from the sysservers table
5. Use sp_addserver @server=new_server_name, @local = 'local' to add the new server name to the sysservers table.
-Mike