SQL Server - Database Mail

This post is about sending mail through SQL Server [Database Mail  feature in SSMS]

select the option to create profile & account for sending mail.

Add profile & account for sending mail

      gmail -> Server name - smtp.gmail.com  , Port number - 587
      live   -> Server name - smtp.live.com  , Port number - 25

Configure Maximum File Size,if your adding attachmments with mail

Management - > Database Mail - > Send Test E-mail...

Management - > Database Mail - > View Database Mail Log -> to check status of your sent mail

Log into your Gmail & check for mail...

Send Mail with attachment

USE msdb
EXEC sp_send_dbmail @profile_name='MSSQLNotification',
@subject='Mail with Attachment',
@body='Please find the attachmentc',

Below are the system tables related to database mail.
USE msdb
* FROM dbo.sysmail_account
SELECT * FROM dbo.sysmail_attachments
SELECT * FROM dbo.sysmail_attachments_transfer
SELECT * FROM dbo.sysmail_configuration
SELECT * FROM dbo.sysmail_log
SELECT * FROM dbo.sysmail_mailitems
SELECT * FROM dbo.sysmail_principalprofile
SELECT * FROM dbo.sysmail_profile
SELECT * FROM dbo.sysmail_profileaccount
SELECT * FROM dbo.sysmail_query_transfer
SELECT * FROM dbo.sysmail_send_retries
SELECT * FROM dbo.sysmail_server
SELECT * FROM dbo.sysmail_servertype

Reference http://msdn.microsoft.com/en-us/library/ms189635.aspx

No comments: