asked on

How to change the name of the SQL Server.

Hi Experts,
We have recently changed the IP an existing SQL2005 server . Also we have chaged the name and the password. The server can be logged in with the new name and the password ; new IP and new Password. The problem is when I run the query 'Print @@Server', I still get the old server name. Can anyone tell me what the problem might be and possibly how to fix this.

Thanks.

ASKER CERTIFIED SOLUTION

Chris Mangus

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sarika_patalay

ASKER

cmangus,
I did what you suggested,but I get the following messages.

sp_dropserver 'SQL10'
go
message : SQL10 does not exist

sp_addserver 'SQL2', 'local'
go
message : SQL2 already exists.

and when I issue a print command @@servername
message :SQL10

In short, the task failed.

Regards,
Sarika

Chris Mangus

Have you restarted the SQL service yet? You have to restart the service after doing this procedure.

sarika_patalay

ASKER

I did restart the service, but no avail. I have afeeling that the problem lies in windows registries, but not sure.

any ideas?

Chris Mangus

The server name that SQL knows about is in sys.servers table. You could query that table to see what the entries are.

Are you performing this task while logged on as sa or equivalent?

Chris Mangus

You can also use SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName') as an alternative to SELECT @@ServerName.

You may want to restart the entire server to see if this will help the change to take effect.

sarika_patalay

ASKER

I did all you stated.

The name in sys.servers table is SQL2 , which is the name of the new table. I am logging in as 'sa'.

also : SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY ('InstanceName') yields null in both the cases.

I did restart the server to, but, that didnt make any difference.

Nevertheless,thanks for the input.

Chris Mangus

Is this a virtual or clustered server?

At this point, I would probably re-run:

sp_dropserver 'SQL2'
go
sp_addserver 'SQL2', 'local'
go

...and do another SQL service restart and see if you can get it to run again...

If sys.servers says SQL2, that's really what SQL Server thinks it is.

sarika_patalay

ASKER

I did all you asked, but still seems to make no difference. Finally, I have decided to un-installed and re-install the SQL server.

Thanks for the input. I'll award you points anyways..

Chris Mangus

That's very odd that it didn't work. Sometimes I've had to do it more than once to make it "stick" but I've never had it not totally work.

CodeManiac

I've had this problem before too, and I got the same errors. We eventually had to uninstall and reinstall SQL Server.