Send Email From SQL Database

In this article, I share the script for send the Email using SQL Server.

USE Master

GO

sp_configure 'show advanced options', 1

GO

reconfigure with override

GO

sp_configure 'Database Mail XPs', 1

GO

reconfigure

GO

sp_configure 'show advanced options', 0

GO

-- Create new profile--

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'EmailAlert',

@description = 'SQL EmailAlert '

-- Set the New Profile as the Default--

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'EmailAlert',

@principal_name = 'public',

@is_default = 1 ; -- Make this the default profile

GO

-- created the account--

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'EmailAlert',

@description = 'my email description',

@email_address = 'test@gmail.com',--Email address

@display_name = 'Email display name',--Email display name

@replyto_address = null,

@mailserver_name = 'smtp.gmail.com',-- mail server

@username = 'test@gmail.com',--Email address

@password = '****',--Password

@port = 587, -- Port

@enable_ssl = true

-- Added account to the profile--

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'EmailAlert',

@account_name = 'EmailAlert',

@sequence_number = 1

GO

 --Send Test mail--

EXECUTE msdb.dbo.sp_send_dbmail

@recipients='test@gmail.com',--recipients email address

@subject = 'Subject',

@body = 'This is a test message sent from the SQL',

@reply_to = 'test@gmail.com'

GO

-- check the database table records--

SELECT *

FROM sysmail_mailitems

GO

SELECT *

FROM sysmail_log

GO

Reference: http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/