Send mail as html format from SQL Server DB with attached file and/or embedded picture/video

Sometimes we need to send task/operation status report as a well-formatted mail, known as HTML formatted mail. So that, we need to setup a DB mail environment to provide mail from DB end.

Also, below description can be used to send mail as plain text format from DB end... 

To send a mail from DB end, we have to follow below 3 simple steps...

NOTE: make sure login user have granted on the role “DatabaseMailUser” and have permission to execute MSDB procedure “sp_send_dbmail”  ... always "Admin" role is great :)

1.       Create a Database Mail Profile

2.       Send a test mail to test Mail Profile is working

3.   Execute Send Mail Command to Send a Mail (HTML and/or plain text) Using above DB Mail Profile.

See below detail (step-by-step) description of above points...

Connect to your SQL Server database. You can use any user, ie SQL user and/or Windows authentication.

Step - 1. Create a Database Mail Profile:

After login to DB look at the option "Database Mail" under "Management" of Object Explorer window [normally Object Explorer located on the left side of MSSM] section... See Figure1:"DB Mail-1"

All we have to create a DB Mail profile using "Database Mail" option. We can do this by using Wizard Guide and SQL command.

1.1: Wizard Guide: steps are as follows. You have to follow the on screen instruction.

A.    right click on the "Database Mail" option then click "Configure Database Mail". A new window will open and Click next if you are in the welcome page.

B.    Check the option “set up database mail by performing the following tasks:”, Then click next. See “Figure 3: Select Conf Task”

C.    Fill-up the “New Profile” page by providing required information.

Give a name for this new profile: Test_Profile_Name

Write a short description for this profile (its optional) : Test_Profile_Desc

 

D.    Now click on “Add…” under the SMTP section. See Figure 3: New Profile

E.     New window will come named “Add Account to Profile”, see figure4- add account to profile. In this section you have to create a new account, if you don’t have one, to send the mail.

F.     Now you have to fill-up “New Database Mail Account” form.

Give a name: Test_Mail_Account

Give a desc : Test_Mail_Desc

Give an Email address to send a mail from this address: test@test.com

Provide display name to show in the recipient inbox: Test_Display_Name

Provide a reply address to use when recipient like to reply the mail: I leave it blank because of this is a system generated mail.

Provide Mail Server Name from which server will send the mail: testMailServer.com

Provide server port number: 25 (this is default to 25, if not provide the actual port for the provided mail server)

If encryption required please check the option SSL required.

G.    Now provide the SMTP Authentication information:

There are three options: you have to choose one.

·            Windows autho: by this DBMailAccount will use the user, who run the DB Engine service.

·            Basic Autho: it’s a SQL Server user and password.

·            Anonymous Autho: if DB Engine allow guest user to login.

H.    Click “Finish”. Ok, you are done to setup a Mail Profile named : Test_Profile_Name

Step - 2. Send a test mail to test the Mail Profile, Test_Profile_Name,  is working : go to “Send Test E-Mail…”

Now check test result: go to “Database Mail Log”

 

Step - 3. Execute Send Mail Command to Send a Mail (HTML and/or plain text) Using above DB Mail Profile.

As “HTML” format:

-----------------------------------------

EXEC msdb..sp_send_dbmail

 @profile_name = 'GILEAD_Mail'

 ,@recipients = 'Saleh.Faize@bd.imshealth.com;saiful.azam@bd.imshealth.com'

 ,@copy_recipients = 'RSaha@bd.imshealth.com'

 ,@subject = 'A Message From GILEAD DB ADMIN!'

 ,@body = '<b>TEST</b> <i>Mail</i> <font color="FF0000">Body</font> from <font color="00FF00"><b>DataBase End</b></font> J'

 @body_format = 'HTML';

-----------------------------------------

Received mail would be like below image---

As “Plain Text” format:

-----------------------------------------

EXEC msdb..sp_send_dbmail

 @profile_name = 'GILEAD_Mail'

 ,@recipients = 'Saleh.Faize@bd.imshealth.com;saiful.azam@bd.imshealth.com'

 ,@copy_recipients = 'RSaha@bd.imshealth.com'

 ,@subject = 'A Message From GILEAD DB ADMIN!'

 ,@body = 'Mail body as plain text J'

 @body_format = 'Text';

-----------------------------------------

SQL Script/tSql to backup SQL Server Database

This script is a parameterized stored procedure, [dbo].[DB_BACKUP_TO_DISK], to backup SQL Server Database (.BAK). Everything is to call this procedure with all parameters. See below table to understand parameters.

Parameter Name	Type	Description
@DBName	VARCHAR(500)	Put Database name
@SaveAs	VARCHAR(500)	Put output name, is a bak file name. i.e. TEST_BACKUP.BAK
@SaveTo	VARCHAR(500)	Put full path where BAK file will be created

See below how to call this procedure…

EXEC  [dbo].[DB_BACKUP_TO_DISK]             @DBName = N'TEST_DATABASE',             @SaveAs = N'TEST_BACKUP',             @SaveTo = N'D:\TEST\'

Please find the below script of the described stored procedure …

CREATE PROCEDURE [dbo].[DB_BACKUP_TO_DISK] @DBName VARCHAR(500)       , @SaveAs VARCHAR(500)       , @SaveTo VARCHAR(500) AS BEGIN       SET @DBName = LTRIM(RTRIM(@DBName))       SET @SaveAs = LTRIM(RTRIM(@SaveAs))       SET @SaveTo = LTRIM(RTRIM(@SaveTo))       DECLARE @fileName VARCHAR(4000) = @SaveTo + CASE                   WHEN right(@SaveTo, 1) <> '\\'                         THEN '\\'                   ELSE ''                   END + @SaveAs + + CASE                   WHEN right(@SaveAs, 4) <> '.BAK'                         THEN '.BAK'                   ELSE ''                   END       BACKUP DATABASE @DBName TO DISK = @fileName END

see also -- Use Side-Effecting Operator /DML within a User Defined Function of SQL Server

           -- SQL Script to split string using SQL Server

Use Side-Effecting Operator /DML within a User Defined Function of SQL Server

SQL server’s function doesn't support DML (Insert, Update & Delete)/EXEC () procedure. Shows “Invalid use of a side-effecting operator 'EXECUTE STRING' within a function” as execution result.

If you want to insert/ update (DML) into table data or want to execute dynamic SQL statement within a function, for that you have to use sqlcmd utility. This is a SQL Server utility to execute a query/ script file immediately. To use this, sqlcmd, utility you have to enable xp_cmdshell to pass windows command.

Impotent: Object name should be fully qualified name means with database and schema name, i.e. TEST_DB1.dbo.TEST_TABLE1

Let’s execute the below INSERT command in a function

INSERT INTO [TEST_DB1].[dbo].[TEST_TABLE1] (NAME) VALUES ('TEST_NAME')

Here is the function to execute DML

CREATE FUNCTION dbo.EXECUTE_DML_IN_FUNCTION ()

RETURNS BIT

AS

BEGIN

                DECLARE @cmd VARCHAR(8000)

                DECLARE @tSql VARCHAR(8000) = 'INSERT INTO [TEST_DB1].[dbo].[TEST_TABLE1] (NAME) VALUES (''TEST_NAME'')'

                SELECT @cmd = 'sqlcmd -S ' + @@servername + ' -U TEST_USER -P TEST_PASS ' + ' -Q "' + @tSql + '"'

                EXEC master..xp_cmdshell @cmd

                                , 'no_output'

                RETURN 'True'

END

Now call the function and a record will be inserted on the table [TEST_DB1].[dbo].[TEST_TABLE1]

SELECT dbo.EXECUTE_DML_IN_FUNCTION ()

NOTE: if you want to use Windows Authentication to connect to the server, ignore -U & -P options, use -E instead.

Enable master..xp_cmdshell extended stored procedure of SQL Server

master..xp_cmdshell used to execute windows command like cmd/PowerShell.

Execute below commands under master database to enable xp_cmdshell extended procedure

EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

You don’t have to do anything else to enable xp_cmdshell extended stored procedure.

SQL Script to split string using SQL Server

NOTE: In 2016 version of SQL Server, a new function STRING_SPLIT (GivenString, Separator) has been implemented but for below 2016 version has no any string function like this one.

So that, I am sharing a user defined table valued function, named SplitString(GivenString, Separator, ReturnSeperator), by which you can Split any string by using any separator. Also, this function has another parameter by which you can return the separator also.

See below parameter description

SplitString(givenString , separator, returnSeperator) -- Download the sql script or see below for the coding

Returns a Table with two columns – OrdinalPosition & Value

Param Name	Type	Direction	Description
givenString	VARCHAR (8000)	IN	A String, which one you want to parse/split
separator	VARCHAR (8000)	IN	A String, which is used as a separator for concatenated strings.
returnSeperator	BIT	IN	Default is False. For True/1 value, return String will have the separator at the end For False/0, No separator will be returned.

Return Column	Type	Description
OrdinalPosition	INT	This column will represent the position of the value string in GivenString
Value	VARCHAR	String value as varchar(max length of value) which has been split from GivenString

Uses:

Select * from SplitString (‘abc,xyz,pqr’ , ‘,’, False) Select * from SplitString (‘abc,xyz,pqr’ , ‘,’, default)	OrdinalPosition Value 1 abc 2 xyz 3 pqr
Select * from SplitString (‘abc,xyz,pqr’ , ‘,’, true)	OrdinalPosition Value 1 abc, 2 xyz, 3 pqr,

Here is the Table Valued Function script

SplitString: download as file

-----------------------------------------------------------------------

CREATE FUNCTION [dbo].[SplitString] (   @givenString varchar(8000) , @separator varchar(100) , @returnSeparator bit = 0) RETURNS TABLE AS   RETURN (   WITH data ([start], [end])   AS   (       SELECT         0 AS [start],         CHARINDEX(@separator, @givenString) AS [end]          UNION ALL          SELECT         [end] + 1,         CHARINDEX(@separator, @givenString, [end] + 1)         FROM           data         WHERE [end] > 0   )   SELECT     ROW_NUMBER() OVER (       ORDER BY OrdinalPosition     ) OrdinalPosition,     RTRIM(LTRIM(Value)) + (       CASE         WHEN @returnSeparator = 1           THEN             @separator         ELSE           ''       END     ) Value   FROM     (       SELECT         ROW_NUMBER() OVER (         ORDER BY [start]         ) OrdinalPosition,         SUBSTRING(@givenString, [start], COALESCE(NULLIF([end], 0), LEN(@givenString) + 1) - [start]) Value         FROM           data     ) r   WHERE RTRIM(Value) <> ''     AND Value IS NOT NULL   )