Thursday, June 19, 2008

Script to setup Database Mail


--MAKE SURE TO STOP SQL SERVER AGENT BEFORE RUNNING THIS SCRIPT!!!!!!!
USE msdb
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
ALTER DATABASE [msdb] SET ENABLE_BROKER

DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)

SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'pop.xxx.com';
SET @EmailAddress = 'email@adress.com';
SET @DisplayUser = (Select @@Servername);

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;

EXEC msdb.dbo.sp_add_operator @name=N'DBAOperater',
@enabled=1,
@pager_days=0,
@email_address=N'email@adress.com'

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'email@adress.com',
@body= 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = @ProfileName

SELECT * FROM sysmail_allitems
-----------------------------Email Samples------------------------------------
--Code to send an attachment
--EXEC msdb.dbo.sp_send_dbmail
--@recipients=N'email@adress.com', --[ ; ...n ]
--@body='Message Body',
--@subject ='Message Subject',
--@profile_name ='DBMailProfile',
--@file_attachments ='C:\FileAttachment.txt';

--Code to send query results
--EXEC msdb.dbo.sp_send_dbmail
--@recipients=N'email@adress.com', --[ ; ...n ]
--@body='Message Body',
--@subject ='Message Subject',
--@profile_name ='DBMailProfile',
--@query ='SELECT Product FROM sb2..SalesHistory GROUP BY Product HAVING COUNT(*) > 3',
--@attach_query_result_as_file = 1,
--@query_attachment_filename ='Results.txt'


0 comments: