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',
@recipients='sql@mssql.com',
@subject='Mail with Attachment',
@body='Please find the attachmentc',
@file_attachments='C:\temp\SQL.jpg'

Below are the system tables related to database mail.
USE msdb
GO
SELECT
* 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: