Recently we have encountered the following error message while configuring Database Mail in SQL server 2005.
Create new account 'XXXX' for SMTP server 'XXXX' (Error)
Unable to update account XXXX for SMTP server Microsoft.SqlServer.Management.SqlManagerUI.SQLiMailServer.
Set mail server login password failed for MailServer 'XXXX'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4262.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Set+mail+server+login+password+MailServer&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
@no_credential_change is not a parameter for procedure sysmail_update_account_sp. (Microsoft SQL Server, Error: 8145)
System procedures like sysmail_update_account_sp resides in system database. In our case, system databases and SQL Engine were not at same build. Possible cause of this could be that recently patch applied on SQL Server failed.
1. Please check the version of SQL server by running below command
In our case output was 9.00.4262
2. Please check the version of the resource database by running below command
In our case it returned 9.00.4035
As we see that there is a version mismatch. This means that the procedure definition in system database may not match with the one sent by client tools (DatabaseEngine.exe).
The parameter "@no_credential_change" gets added during the installation of the patch KB970894 (9.00.4262) and since there is a version mismatch we have encountered this issue.
To fix this issue reapply the patch (in our case it was KB970894) once again or uninstall and reinstall the patch and make sure that the resource database shows you the correct version.
Ravi SE, Microsoft SQL Server
Balmukund Lakhani Senior SE, Microsoft SQL Server
What if you have this error and their is no version mismatch?
I have same version i.e 9.00.5000, however still getting same issue.