Practical T-SQL
Lesson 55. Sending Notification Emails With T-SQL Without Using Hardcoded Email Addresses
When you build a self-serve analytic environment, you want to build it so you do not have to make any changes to code as you promote things from dev to test to prod.
When you are setting up your notifications in SQL Server, you are going to want to use an Agent Operator so you can have different notification emails on different servers. For example, dev and test notifications only go to devs while the prod box sends notifications to a distro.


How To Get The Operator Email Address From The System
In order to send emails programmatically, you need to retrieve the email address from the system. Here is how you do that.
In [ ]:
USE demo
DECLARE @OperatorName sysname = N'YourOperatorName';
DECLARE @OperatorEmailAddress nvarchar(100) = (SELECT email_address FROM msdb.dbo.sysoperators WHERE [name] = @OperatorName);
PRINT @OperatorEmailAddress
Copy link