Recently we had a need to start looking at other solutions for the SQLMail service in SQL Server 2000. The service is unreliable, depends on Microsoft Outlook (2000 before SR1 preferable), and an email server. Corporate suggested a new solution called SqlAnswersMail. It is a drop in replacement for SQLMail service, and they even have an example of how to replace the xp_sendmail command for better flexibility. It has a small fee behind it, but then again, so does running Outlook.
When I decided to deploy the demo version on one of our QA boxes, and all was going well… At least the setup was. When I went to test the command inside SQL, it generated the following error:
Server: Msg 50000, Level 16, State 1, Procedure sp_sendSAM, Line 305 Parameter PrimaryMailServer is required. Section SQL:. Please check configuration settings for SqlAnswersMail. Source: SqlAnswersMail.DLL, INIFile, LoadSQLIni
This is weird, I know I set one, and I had even tested it from the config client, so I decided to take a look around. I ended up hitting the SQLAnswers forum, and stumbling on this error. One of the answers hit the nail right on the head. When I executed select @@ServerName, it returned a different name from the name that SQL was running on. I should have known this, as the server was a vmware clone of another box. I did some looking around, and came across an MS article. It basically says:
sp_dropserver <old_servername>
go
sp_addserver <new_servername> , local
go
Then you need to reset the SQL service. Testing afterwards was successful, and all is now working. This is a great step in replacement for SQLMail, and it is not dependent on MAPI, or login accounts.