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.

Examples

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.

USE demo

DECLARE @OperatorName sysname = N'YourOperatorName';

DECLARE @OperatorEmailAddress nvarchar(100) = (SELECT email_address FROM msdb.dbo.sysoperators WHERE [name] = @OperatorName);

PRINT @OperatorEmailAddress