Sometimes, we are requested to provide certain information from a database via e-mail. If the request is repeated frequently, it is difficult to repeat this annoying similar process. For this reason, you should regularly send emails with predefined content.
Database Mail, as you would expect from its name, is a solution for sending e-mail messages from the SQL Server Database Engine to users. Using Database Mail, database applications can send e-mail messages that can, for example, contain query results or simply alert a user about an event that occurred in the database.
The process of Database Mail configuration has three main steps. In order to complete this successfully, we need to:
- Enable Database Mail
- Create a Database Mail profile and account,
- Configure those two to work together
Enabling Database Mail in SQL Server
We’ll use T-SQL to allow Database Mail. To enable Database Mail, run the following code:
|
1 2 3 4 5 6 |
sp_configure 'Database Mail XPs', 1; GO RECONFIGURE GO |
If it really works, you’re good to go. In this case, you might hit an error informing you that ‘Database Mail XPs don’t exist. This is going to happen from time to time because this is an advanced feature. To fix this, we need to change the show advanced options default value from 0 to 1.
|
1 2 3 4 5 6 7 8 9 10 11 |
SP_CONFIGURE 'present superior choices', 1; GO RECONFIGURE; GO SP_CONFIGURE 'Database Mail XPs', 1; GO RECONFIGURE GO |
Configuring Database Mail Profile and Account
Now we have to set up the e-mail profile and add an e-mail account. To do this we will use some stored procedures in msdb database. Here, we create a new Database Mail profile named ‘Notifications’. we will use the sysmail_add_profile_sp stored procedure and the following code:
To grant permission for a database user or role to use this Database Mail profile, we will use the sysmail_add_principalprofile_sp stored procedure and the following code:
|
1 2 3 4 5 6 7 8 |
-- Grant access to the profile to the DBMailUsers role EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'Notifications', @principal_name = 'public', @is_default = 1 ; GO |
To create a new Database Mail account holding information about an SMTP account, we will use the sysmail_add_account_sp stored procedure and the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Create a Database Mail account EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Gmail', @description = 'Mail account for sending outgoing notifications.', @email_address = 'Use a valid e-mail address', @display_name = 'Automated Mailer', @mailserver_name = 'smtp.gmail.com', @port = 465, @enable_ssl = 1, @username = 'Use a valid e-mail address', @password = 'Use the password for the e-mail account above' ; GO |
To add the Database Mail account to the Database Mail profile, we will use the sysmail_add_profileaccount_sp stored procedure and the following code:
|
1 2 3 4 5 6 7 8 |
-- Add the account to the profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Notifications', @account_name = 'Gmail', @sequence_number =1 ; GO |
Use the Database Mail Configuration Wizard
You can configure Database email very easily by using a wizard. To use wizard, please follow the below steps:
- In Object Explorer, expand the node for the instance where you want to configure Database Mail.
- Expand the Management
- Right-click Database Mail, and then select Configure Database Mail.
- Account name: Type the name of the new account.
- Description: Type a description of the account. The description is optional.
- E-mail address: Type the name of the e-mail address for the account. This is the e-mail address that the e-mail is sent from. For example, an account for SQL Server Agent may send e-mail from the address [email protected].
- Display name: Type the name to show on e-mail messages sent from this account. The display name is optional. This is the name displayed on messages sent from this account. For example, an account for SQL Server Agent may display the name “SQL Server Agent Automated Mailer” on e-mail messages.
- Reply e-mail: Type the e-mail address that will be used for replies to e-mail messages sent from this account. The reply e-mail is optional. For example, replies to an account for SQL Server Agent may go to the database administrator, [email protected].
- Server name: Type the name or IP address of the SMTP server the account uses to send e-mail. Typically this is in a format similar to SMTP.<your_company>.com. For help with this, consult your mail administrator.
- Port number: Type the port number of the SMTP server for this account. Most SMTP servers use port 25 or 587, or port 465 for SSL connections.
- This server requires a secure connection (SSL): Encrypts communication using Secure Sockets Layer.
- Windows Authentication using Database Engine service credentials: Connection is made to the SMTP server using the credentials configured for the SQL Server Database Engine service.
- Basic Authentication: Specify the user name and password required by the SMTP server.
- User name: Type the user name that Database Mail uses to sign in to the SMTP server. The user name is required if the SMTP server requires basic authentication.
- Password: Type the password that Database Mail uses to log in to the SMTP server. The password is required if the SMTP server requires basic authentication.
- Confirm password: Type the password again to confirm the password. The password is required if the SMTP server requires basic authentication.
- Anonymous authentication: Mail is sent to the SMTP server without login credentials. Use this option when the SMTP server does not require authentication.
- Complete the wizard dialogs.
Test Database Mail configuration
Okay, now we’ve set up an email account, so what’s next? Well, let’s send a test e-mail and see if it works. As we mentioned before, we could send an e-mail to alert a user about an event that occurred in the database and this is exactly what we are going to do later on using a simple DML trigger. For now, let’s just send an e-mail to the specified recipient using the sp_send_dbmail stored procedure.
|
1 2 3 4 5 6 7 8 |
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @body = 'The database mail configuration was completed successfully.', @subject = 'Automated Success Message'; GO |
Troubleshooting Database Mail
In this case, the e-mail message was successfully queued, but the message was not delivered. First things first, check if Database Mail is enabled by executing the following code:
|
1 2 3 4 5 6 7 8 |
sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO |
In the Results grid, make sure that the ‘run_value’ column for Database Mail XPs is set to 1:
Note that, To send e-mails, the user must also be a member of the DatabaseMailUserRole server role. Members of the sysadmin fixed server role and msdb db_owner role are members automatically. This can be easily checked by going to Security > Logins, right-click the current user and select Properties. In the Login Properties dialog click the ‘Server Roles’ page and make sure that the ‘sysadmin’ server role is checked:
The Database Mail system logs e-mail activity in the ‘msdb’ database. To view the error messages returned by Database Mail, execute the following code:
|
1 2 3 |
SELECT * FROM msdb.dbo.sysmail_event_log; |
At this point, let’s do a quick lookup, just to make sure that the e-mail profile is configured the same as it is for Outlook. Execute the code below:
|
1 2 3 4 5 6 7 8 9 |
SELECT [sysmail_server].[account_id], [sysmail_account].[name] AS [Account Name], [servertype], [servername] AS [SMTP Server Address], [Port] FROM [msdb].[dbo].[sysmail_server] INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id]; |
We will use the sysmail_update_account_sp stored procedure to change the port number in the existing Database Mail account. Execute the following code:
|
1 2 3 4 5 6 |
EXECUTE msdb.dbo.sysmail_update_account_sp @account_name = 'Gmail', @port = 587; GO |
Sending query results e-mail message
Another example could be sending an e-mail message that contains query results. Execute the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Notifications', @recipients = 'Use a valid e-mail address, @query = 'USE AdventureWorks2014; GO SELECT Name, ProductNumber, ListPrice AS Price FROM Production.Product WHERE ProductLine = ''R'' AND DaysToManufacture < 4 ORDER BY Name ASC; GO', @subject = 'Product list', @attach_query_result_as_file = 1; |
Sending emails with SQL
We use an extra standard method for sending emails with a system-saved process referred to as sp_send_dbmail. In this manner, you’ll be able to send emails to specified recipients and embody them within the e-mail query, attachments, or each. Please follow the instructions from the previous paragraph.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
USE [NOTIFICATION] GO /*==================================================================== * --Mission Identify : NOTIFICATION * --Description : Auto Email For Worker Affirmation * --Final Modify : Selim Ahmed * --Begin Date(MM/DD/YY) : 18/05/2022 * ====================================================================*/ ALTER PROCEDURE [dbo].[Email_Confirmation] AS BEGIN DECLARE @xml NVARCHAR(MAX), @physique NVARCHAR(MAX), @RowCount INT SELECT @RowCount=COUNT(EmployeeId) FROM Employment EM LEFT JOIN Grade G on EM.GradeId = G.GradeId WHERE ISNULL(CONVERT(NVARCHAR(MAX),DATEADD(DAY,-15,(DATEADD(MONTH,(CASE WHEN G.GradeCode IN ('SVP','VP','AVP','D-1','D-2','D-3','D-4','D-4','D-5','M-1','M-2','M-3','M-4','M-5') THEN 3 ELSE 6 END),EM.EmploymentDate))),105),'')=CONVERT(NVARCHAR(MAX),GETDATE(), 105) --PRINT @RowCount SET @xml = CAST(( SELECT EmployeeId AS 'td','', FullName AS 'td','', DESIGNATIONNAME AS 'td','', GradeName AS 'td','', DepartmentName AS 'td','', CompanyName AS 'td','', EmploymentDate AS 'td','', CONVERT(NVARCHAR(MAX),ConfirmationDate, 105) AS 'td','', ApproverName AS 'td','', EmergencyContactNo AS 'td','' FROM ( SELECT Employment.EmployeeId AS EmployeeId,Worker.FullName AS FullName,REPLACE(REPLACE(REPLACE(Employment.EmergencyContactNo,'+',''),'-',''),' ','') AS EmergencyContactNo, DG.DESIGNATIONNAME AS DESIGNATIONNAME,G.GradeName AS GradeName,D.DepartmentName AS DepartmentName,C.CompanyName AS CompanyName, ISNULL(CONVERT(NVARCHAR(MAX),Employment.EmploymentDate, 105),'') AS EmploymentDate, ISNULL(DATEADD(MONTH,(CASE WHEN G.GradeCode IN ('SVP','VP','AVP','D-1','D-2','D-3','D-4','D-4','D-5','M-1','M-2','M-3','M-4','M-5') THEN 3 ELSE 6 END),Employment.EmploymentDate),'') AS ConfirmationDate, ISNULL((SELECT FullName+', '+DG.DESIGNATIONNAME FROM Worker E LEFT JOIN Employment EM ON EM.HRRecordId = E.HRRecordId LEFT JOIN DESIGNATION DG ON EM.DESIGNATIONID = DG.DESIGNATIONID WHERE E.HRRecordId=( SELECT TOP 1 A.ApproverId FROM AssignApprover A WHERE A.HrRecordId=Employment.HRRecordId ORDER BY A.AssignApproverId DESC)),'N/A') AS ApproverName FROM Employment LEFT JOIN Worker on Employment.HRRecordId = Worker.HRRecordId LEFT JOIN Firm C on Employment.CompanyId = C.CompanyId LEFT JOIN Division D on Employment.DepartmentId = D.DepartmentId LEFT JOIN DESIGNATION DG on Employment.DESIGNATIONID = DG.DESIGNATIONID LEFT JOIN Grade G on Employment.GradeId = G.GradeId )A WHERE CONVERT(NVARCHAR(MAX),DATEADD(DAY,-15,A.ConfirmationDate),105)=CONVERT(NVARCHAR(MAX),GETDATE(),105) FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @physique ='<html><head><fashion>desk,td,th{border: 1px strong #ddd;text-align: left;} desk{border-collapse: collapse;width: 100%;} th,td{padding:5px;} </fashion></head><physique> <desk> <tr> <th>ID</th> <th>Identify</th> <th>Designation</th> <th>Grade</th> <th>Division</th> <th>Firm</th> <th>Becoming a member of Date</th> <th>Affirmation Date</th> <th>Supervisor Identify</th> <th>Cellular No</th> </tr>' SET @physique = @physique + @xml +'</desk> <p>N.B: This can be a system generated e mail. Please don't reply to this message.</p></physique></html>' PRINT @physique IF(@RowCount>0) BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMail' ,@recipients = 'admin@techaid24.com' ,@copy_recipients = 'ahmed@techaid24.com' ,@blind_copy_recipients = 'noreply@techaid24.com' ,@topic = 'Worker Affirmation Notification' ,@physique = @physique ,@body_format ='HTML' --,@significance ='HIGH' END ELSE PRINT 'No worker discovered' END --GO --EXEC Email_Confirmation |
If you happen to encounter any issues when executing an SQL process, the first issue first, to verify whether or not the SMTP server is ready correctly. Confirm your credentials and take a look at sending a take a look at e-mail with one other account.
Send Email from MS SQL Server
The article was published on July 24, 2022 @ 4:09 PM
Leave a Comment