Sunday, September 3, 2006

Configuring SQL Database Mail via TSQL

I personally find this quicker than using management studio...

USE msdb
GO
DECLARE @mailprofilename VARCHAR(100)
DECLARE @mailaccountname VARCHAR(100)
DECLARE @mailaccountdescription VARCHAR(100)
DECLARE @smtpserver VARCHAR(100)
DECLARE @emailaddress VARCHAR(100)
DECLARE @from VARCHAR(100)
DECLARE @to VARCHAR(100)

/*
Declare everything as variables (makes it nice and easy to reuse script)
*/

SET @mailprofilename = 'DBMail Profile';
SET @mailaccountname = 'SQL Administrator';
SET @mailaccountdescription = 'for sql generated email notifications';
SET @smtpserver = '192.168.0.20';
SET @emailaddress = 'SQLAdmin@mydomain.net';
SET @from = 'SQL Administrator';
SET @to = 'recipient@mydomain.net';

-- add mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @mailaccountname,
@description = @mailaccountdescription,
@email_address = @emailaddress,
@display_name = @from,
@mailserver_name = @smtpserver

-- add mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @mailprofilename 

-- associate mail account & mail profile together
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @mailprofilename,
@account_name = @mailaccountname,
@sequence_number = 1 ;

-- send test email
EXEC msdb.dbo.sp_send_dbmail
@recipients = @to,
@subject = 'rabbit, rabbit, rabbit, rabbit',
@body = 'bunny, bunny, bunny, bunny', 
@profile_name = @mailprofilename

No comments: