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 great
--STEP 1 - 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
--STEP 2 - CREATING MAIL ACCOUNT
--*********************************
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'PITSAccount',
@description = 'PITS Mail account for Database Mail',
@email_address = 'binukumar@MYWORLD.com',
@display_name = 'PITS Account',
@username='binukumar@MYWORLd.com',
@password='TESTPASS',
@mailserver_name = 'smtp.TESTWORLD.yahoo.com'
--STEP 3 - CREATING MAIL PROFILE
--*********************************
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'PITS Profile',
@description = 'PITS Profile used for database mail'
--STEP 4 - Connecting PROFILE with MAIL
--***************************************
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'PITS Profile',
@account_name = 'PITSAccount',
@sequence_number = 1
--STEP 5 - setting up the role and making this defauüt mail account
--***********************************************************************
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'PITS Profile',
@principal_name = 'public',
@is_default = 1 ;
--STEP 6 - SENDING MAIL
--********************************
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail
@profile_name='PITS Profile',
@recipients='binukumar@myworld.com',
@subject = 'PITS 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. Please let me know if any troubles
Posts
