We may come across the situations that to send mails from SQL server automatically or manually in-order to retrieve some status or information. For that we can setup the database mailing feature of sql and all through sql query itself. Hmmm..thats cool and straight forward, na?

CONFIGURING MAIL

use master
go
sp_configure ‘show advanced options’,1
go
reconfigure with override
go
sp_configure ‘Database Mail XPs’,1
go
sp_configure ‘SQL Mail XPs’,0
go
reconfigure
go

CREATING MAIL ACCOUNT

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘MyAccount’,
@description = ‘My Mail account for Database Mail’,
@email_address = ‘binukumar@MYWORLD.com’,
@display_name = ‘MyAccount’,
@username=’binukumar@MYWORLd.com’,
@password=’TESTPASS’,
@mailserver_name = ‘smtp.TESTWORLD.yahoo.com’

CREATING MAIL RPOFILE

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘My Profile’,
@description = ‘My Profile used for database mail’

 CONNECTING PROFILE WITH MAIL

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘MyProfile’,
@account_name = ‘MyAccount’,
@sequence_number = 1

SETTING UP THE ROLE AND MAKING THIS AS DEFAULT MAIL ACCOUNT

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘My Profile’,
@principal_name = ‘public’,
@is_default = 1 ;

-SENDING MAIL

declare @body1 varchar(100)
set @body1 = ‘Server :’+@@servername+ ‘ My First Database Email ‘
EXEC msdb.dbo.sp_send_dbmail
@profile_name=’My Profile’,
@recipients=’binukumar@myworld.com’,
@subject = ‘My Mail Test from SQL’,
@body = @body1,
@body_format = ‘HTML’ ;

Now go and check the account of the target person we intended to sent the mail. The mail will be there for sure.

One thought on “Emailing from SQL”

Leave a Reply