LinkedIn

Wednesday, 18 March 2015

Add SendGrid email to SQL database mail

How to configure database mail with SendGrid and use it for SSIS agent jobs.

Note: This is more for me as a reminder for the future.

USE master;  
EXECUTE sp_configure 'show advanced options',1;  
RECONFIGURE WITH OVERRIDE;  
sp_configure 'Database Mail XPs',1;  
RECONFIGURE;  

USE msdb;  
EXECUTE msdb.dbo.sysmail_add_profile_sp  
@profile_name = 'EmailAdmin',
@description = 'Profile for sending Automated DBA Notifications';

EXECUTE msdb.dbo.sysmail_add_account_sp  
@account_name = 'SendGridSQLAlerts',
@description = 'Account for Automated DBA Notifications',
@email_address = '',
@display_name = 'SendGrid SQL Alerts',
@mailserver_name = 'smtp.sendgrid.net',
@username = '',
@password = '',
@port = 25

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
@profile_name = 'EmailAdmin',
@account_name = 'SendGridSQLAlerts',
@sequence_number = 1

USE [msdb];  
EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=N'EmailAdmin';  

EXEC msdb.dbo.sp_add_operator @name=N'EmailOperator',  
    @enabled=1, 
    @pager_days=0, 
    @email_address=N''

thanks
Russ