October 23, 2002 at 11:34 am
Hello,
I have an SQL server that is being moved in a day or so. Part of this move is to rename the PC that it is on. I've renamed a PC that had SQL server on it sometime ago and that resulted in me not being able to connect to it at all.
I had to reinstall SQL server on that machine. That was not a problem as there was no data on it. However on this machine that is being renamed it has lots of stuff. I could just detach and reattach the DB, no worries there but then I have DTS jobs and scheduled jobs as well.
Does anyone know how to rename a PC that SQL server is on that keeps the server accessible.
Cheers,
Chris
October 23, 2002 at 12:09 pm
I have done this with SQL Server 7.0. So I know it can be done. However it has been a while and so I don't remember all the details.
You might find something in BOL. I think you will need to rebuild the master database after renaming the server and then reattach all the databases.
FYI the jobs and DTS packages are stored in the msdb.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 23, 2002 at 12:58 pm
Hello,
Thanks for that. I was hoping for something a little less drawn out where by perhaps I could change the PC name in the master db and or the registry etc. I guess I'll have to do the old detach and attach. Should be good revision.
Thanks again,
Chris
October 23, 2002 at 2:44 pm
Look in BOL and then read the BUG article http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303774
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 23, 2002 at 2:48 pm
Also found this
Q. When I try to start SQL Server 7.0 on Windows NT or from a Windows 9x Command prompt, I receive the following error:
Your SQL Server installation is either corrupt or had been tampered with (unknown package id) Please rerun setup.
When I attempt to start SQL Server 7.0 on Windows 9x using the SQL Server Service Manager, nothing happens. What causes this and how can I resolve it?
A. This error message appears if you have renamed the Windows computer on which you installed SQL Server 7.0. SQL Server 7.0 uses the Windows computer name internally; if you change the Windows computer name, SQL Server detects a different name and generates the error message.
NOTE: Your data has not been corrupted or damaged in any way.
To resolve this error and start SQL Server again, do either of the following:
Run SQL Server 7.0 Setup from the original product CD. Doing so will not reinstall SQL Server or any components; however, it will update SQL Server 7.0 internally to reflect the new Windows computer name. You also need to update SQL Server's internal servername by running the following two stored procedures:
sp_dropserver <old_name>
go
sp_addserver <newname>, local
go
If SQL Server is configured to listen on either the Multiprotocol or NWLink IPX/SPX network libraries, you'll need to remove them, and then add them back using the SQL Server Network Utility.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 24, 2002 at 10:49 am
Just a note that the above is only relevent to SQL 7. In SQL 2000, you still need to do the removeserver/addserver step, but you do not need to rerun setup to fix a "corrupted" installation.
You will need to update the originating_server column in MSDB..sysjobs though. In 7.0, this was simply '(local)' whereas in 2000 it contains the actual NetNIOS name of the machine. Failing to update it will make SQL Agent think all your jobs are master/target server jobs.
February 11, 2003 at 9:42 pm
Bring up an old topic. I recently renamed a laptop which has a SQL Svr 2000 installed on it. I read this and the BOL topic on "Renaming a Server". It says I can still connect to the server w/ the new name, but I can't! I can't start SQL Query Analyzer, how can I run sp_dropserver and sp_addserver? Can someone clarify this for me? Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply