On one of the Production instances a job which sends the details of blocking sessions was failing. This is the same instance which was rebuilt on Tuesday. On checking the job history, it was found that the job was failing while trying to send the email.
Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @user, which was not supplied.
The error message states that the parameter @user was not supplied. I was surprised to see this message since xp_sendmail extended stored procedure does not have a parameter named @user. Since all the databases were migrated by moving the LUNs from an old server, I wondered if the sqlmap70.dll which xp_sendmail uses was of older version.
When I compared the version of this dll with that of a dll on a *working* instance, both were similar. Hence the dll issue was ruled out. Since all the databases were moved “as it is”, I planned to stop and start the SQLMail session.
EXEC xp_stopmail go EXEC xp_startmail go
SQLMail stopped without any errors but while starting the SQLMail session the same error was thrown. This confirmed that there is an issue with the way the Mail Profile was setup for the SQL Server service account. The Profile Name drop down box did not have any values in it.
Also in the Control Panel the Mail applet was missing which appears only when a profile has been created for the first time.
This clearly explains why xp_sendmail was failing. I wish the error message was more descriptive. Now the Mail Profile has to be created for the SQL Server service account by following the below steps.
- Logon to the server using the credentials of SQL Server service account
- Open MS Outlook and configure the profile using the Mailbox details of the SQL Server service account
- In the SQLMail configuration (Enterprise Manager –> Support Services –>SQL Mail window select the newly created Mail Profile and click Ok.
Since my posts are always “blah blah in text” today I tried my hand at creating 2 videos and embed them here. Don’t expect much from them, they are just pictorial representation of the bullet points mentioned above.