As is generally recommended to do, I have Alerts configured for errors of severity 19 and greater that occur on my SQL Servers.
However, as part of an email notification standardization process, I'm wondering if there is any way to modify the contents of an Alert, including the email title and the body of the message.
From looking around both the options provided by SSMS and through some searching on the Internet, I'm being led to believe that this might not be the case. I am aware that an additional message can be added to the end of a system-generated alert, but I haven't found anything suggesting the system alert itself can be modified.
My other immediate idea to achieve the general goal here would be to not have the alerts send notifications directly, but instead to trigger a job that triggers Powershell that ultimately sends the email. Would this be the only realistic way to do this, or are there easier ways to make an Alert generate a customized notification?
Best Answer
There is no way to directly modify the
subjectandbodyfields of the email alerts generated by SQL Server Agent.Having the alert run a job instead of directly generating an alert email would be the correct way of getting to your desired state. Having said that, it's not necessary to use PowerShell for that if you don't want to.
Unfortunately, there does not appear to be a way to pass parameters into the job from the alert. This means you'd need to define a separate job for each alert you have configured.