Error and handling of SQL Server hostname change

Source: Internet
Author: User

After SQL Server changes the hostname, you need to do something, otherwise the maintenance plan and subscription publishing will have problems, the following steps:


Sometimes after the computer name is modified, the execution of the SELECT @ @servername still returns the original computer name, indicating that the "instance" has not been modified and needs to be performed manually:

Sp_dropserver ' original host name '

And then execute
sp_addserver ' new host name ', ' local '
Then, restart the database service, then execute SELECT @ @servername will return the modified computer name, indicating that "instance" has been modified.



Verified:
Select CONVERT (varchar (+), serverproperty (' ServerName '))
Select CONVERT (varchar (+), @ @servername)


Specific as follows:

1. Disable Publishing
2, exec sp_dropserver ' original host name ', ' Droplogins '
3, sp_addserver ' new host name ', ' local '

Note the unity of case and select CONVERT (varchar (+), serverproperty (' ServerName '))


You can also execute the following script:

--Execute the following statement, and then restart the SQL service after completion
If SERVERPROPERTY (' servername ') <> @ @servername
Begin
declare @server sysname
Set @server = @ @servername
exec sp_dropserver @server = @server
Set @server = Cast (serverproperty (' servername ') as sysname)
exec sp_addserver @server = @server, @local = ' local '
End


Do the above to avoid the following errors



Following are changes to the computer name after the maintenance plan has changed how to proceed

[298] SQL Server error: 15404, unable to get information about Windows NT Group/user ' Hrb-lg\administrator ', error code 0x534. [SQLSTATE 42000] (connisloginsysadmin)

I have not encountered this problem before, but look at this error message: Unable to get information about Windows NT Group/user ' Win-i556ub3odg2\administrator '. This is mostly because of the owner relationship, the default owner is the current user when the job is established, but the user of the computer does not necessarily match the owner of the SQL.

Open the general interface of the job and change the "Win-i556ub3odg2\administrator" to: "sa".

SQL Server error: 15404, unable to get information about Windows NT Group/user Mypc\administrator ', error code 0x534. [SQLSTATE 42000] (connisloginsysadmin)

When our system user name changes, we will find that the previous well-run maintenance plan in SQL has suddenly failed all of a sudden and the above error has occurred. The workaround is to change the original system user name in SQL security >> logins to the user name now used, and then delete the existing maintenance plan to re-establish the maintenance plan.

Error and handling of SQL Server hostname change

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.