Today I got requirement like after my ETL
packages execution I need to send one email to team regarding what is status of
package execution and how many records got success and fail . To do that I
decided to use database mail component in SQL Server. We can configure database
mail by two ways
1. Using GUI
2. Using SQL Script
In Previous post Configure DB
Mail using GUI, we have seen how to configure DB Mail using GUI. Now
we see how to configure using T-SQL
DB Mail
configuration:
We need to
configure the sp_send_dbmail stored prcedure for respected server , generally
this is disabled mode due to security issue.
USE MASTER
GO
SP_CONFIGURE 'show
advanced options ' ,1
RECONFIGURE WITH
OVERRIDE
GO
SP_CONFIGURE 'Database
Mail XPs' ,1
RECONFIGURE WITH
OVERRIDE
GO
SP_CONFIGURE 'show
advanced options' ,0
RECONFIGURE WITH
OVERRIDE
To configure database mail, we need to create
minimum one profile and one Account . To do that by using system procedure sysmail_add_account_sp we can configure
database mail.
Please find below custom stored procedure and
pass all required parameters
Create PROCEDURE
[dbo].[usp_DB_MailConfiguration]
(
@profile_name sysname,
@account_name sysname,
@SMTP_servername sysname,
@email_address NVARCHAR(128),
@display_name NVARCHAR(128),
@replyto_address NVARCHAR(128),
@description NVARCHAR(256),
@mailserver_name
sysname,
@mailserver_type sysname,
@port
int,
@username nvarchar(128),
@password nvarchar(128),
@use_default_credentials bit,
@enable_ssl bit
)
AS
BEGIN
SET NOCOUNT ON ;
BEGIN TRY
--BEGIN TRANSACTION
T1 --Comment it if SP contains only select state
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
RAISERROR('The specified Database Mail
profile (<profile_name,sysname,SampleProfile>) already exists.', 16, 1);
GOTO done;
END;
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
RAISERROR('The specified Database Mail
account (<account_name,sysname,SampleAccount>) already exists.', 16, 1) ;
GOTO done;
END;
-- Start a transaction before adding the account and the
profile
DECLARE @rv INT;
-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp @account_name ,
@email_address
,
@display_name ,
@replyto_address ,
@description ,
@mailserver_name
,
@mailserver_type
,
@port,
@username,
@password,
@use_default_credentials,
@enable_ssl
IF (@rv<>0)
BEGIN
RAISERROR('Failed to create the specified Database Mail account
(<account_name,sysname,SampleAccount>).',
16, 1) ;
GOTO done;
END
-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
@profile_name
= @profile_name
;
IF @rv<>0
BEGIN
RAISERROR('Failed to create the specified Database Mail profile
(<profile_name,sysname,SampleProfile>).',
16, 1);
--ROLLBACK TRANSACTION;
GOTO done;
END;
-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
@profile_name
= @profile_name,
@account_name = @account_name,
@sequence_number = 1 ;
IF @rv<>0
BEGIN
RAISERROR('Failed to associate the speficied profile with the specified
account (<account_name,sysname,SampleAccount>).', 16, 1) ;
--ROLLBACK TRANSACTION;
GOTO done;
END;
DONE:
--COMMIT TRANSACTION
T1 --Comment it if SP contains only
select statement
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
-- IF (XACT_STATE())
= -1
--- ROLLBACK
TRANSACTION --Comment it if SP contains only select statement
END CATCH
END
To create one profile and Account , we need to execute below script . Then it will automatically creates profile and Account
EXECUTE [dbo].[usp_DB_MailConfiguration]
@profile_name ='KiranTest',
@account_name ='KiranTest',
@SMTP_servername= 'smtp.gmail.com',
@email_address ='kiran.allam@test.com',
@display_name ='Admin',
@replyto_address='kiran.allam@test.com',
@description ='this mail created through script kiran',
@mailserver_name ='smtp.gmail.com',
@mailserver_type ='SMTP',
@port =587,
@username ='kiran.allam@test.com',
@password ='password',
@use_default_credentials =0,
@enable_ssl =1
Next we need to send mail by using above created
profile and account . We can send mail in different formats , in this case i am
sending mail with attachment of .csv file which is out put of one SQL query
EXEC MSDB.DBO.SP_SEND_DBMAIL
@PROFILE_NAME =
'KIRANMAIL',
@RECIPIENTS =
'KIRAN.ALLAM@FORTEST.COM',
@QUERY = 'SELECT * FROM ADVENTUREWORKSDW2012.SYS.TABLES',
@SUBJECT = 'LIST OF TABLES IN ADVENTUREWORKSDW2012',
@QUERY_RESULT_HEADER =
1,
@QUERY_RESULT_WIDTH =
256,
@QUERY_ATTACHMENT_FILENAME ='TEST1.CSV',
@QUERY_RESULT_SEPARATOR =
' ', ----Please consider
this as Tab space
@ATTACH_QUERY_RESULT_AS_FILE =
1,
@QUERY_RESULT_NO_PADDING =1 ;
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
