Complete SQL Server Mail Service

Introduction

In this article, you will see how to send an email with SQL Server Job Agent Steps with SQL profiler with an attachment.

From below, download and install the Microsoft.ACE.OLEDB.12.0 OLE DB driver. Depending on which version of SQL Server (32-bit or 64-bit) is used, there are two versions of the Microsoft.ACE.OLEDB.12.0 OLE DB driver that can be installed:

• https://www.microsoft.com/en-us/download/details.aspx?id=13255

When the appropriate AccessDatabaseEngine executable file is installed, the Microsoft.ACE.OLEDB.12.0 OLE DB driver will appear in the list of the available drivers under the Providers folder as shown below:

Then open SQL Server 2019 Configuration Manager and set SQL Server Agent is running as automatic and rest mode as given in the image below:

So upto now, OLEDB is installed and SQL Server Agent is kept in running mode.

Next, we have to Configure Database Mail. To get Database Email Profiler, we have to configure Database mail.

Configure Database Mail

Step 1

Right click on Database Mail and select Configure Database Email.

In the above image, select Set Up Database email and select Next.

Step 2

Profile Name is required, its name must match with sending email.

In the above image, write Profiler name which will be used later while sending email.

Then click on Add for SMTP account. And then choose New account.

Profiler Name: Retention Metrics POC

Step 3

Email Account setting. From the below configuration, Email will be sent to specific domain like if your company has specific to company then network team will provide it you like Testsolution.com or you can use gmail or any domain where you have an account.

Here, Email address can be any of your gmail email-ids and if it's gmail, then in Server name, it will be smtp.gmail.command port number is 25 default. And if it's company specific, then you can get email address and Server name from the network team of your department.

Email address: abc@gmail.com
Display Name can be any name :Mail Test
Server: smtp.gmail.com
Port: 25

For more details regarding email configuration, refer to this link.

So here, you have created SMTP account, click on Next button.

Step 4

Email Profile Security:

Here, you can set your profile accessibility as per your requirement. But include it as public while for testing.

Click Next.

Step 5

Configuration of Profiler if there is some failure:

Then set Account retry attempt, its delay retry and rest property.

Then click Finish => Then Profiler is created successful. So, finally profile is set.

Now, we are going to create SQL Server Job Agent.

SQL Server Job Agent Steps

Step 1

Right click on Job and select New Job.

Enter the Job name as per your requirement.

And description as per your requirement for your SQL admin. And click Next.

Step 2

Creating SQL Server Job Steps:

Select Steps tab and click on New button.

Step 3

The first step is create copy of original Excel by Excel sheet name + current date. As original copy of Excel will remain as is.

Fill Step name and command.

Step Name: As per your requirement, write step name.

In command, you can write as per your logic.

For example, my requirement is to fire a mail with Excel sheet as attachment which is dynamically calculating revenue, so I have written logic like that.

I have written logic:

Quote:

^{EXEC sp_configure 'show advanced options', 1}

^RECONFIGURE

^{EXEC sp_configure 'xp_cmdshell', 1}

^RECONFIGURE

^{DECLARE @cmdstring varchar(1000)}

^{set @cmdstring = 'copy C:\Test\Retention_metrics_for_Caseworker_Activity.xlsx C:\Test\Retention_metrics_for_Caseworker_Activity'+replace(convert(varchar, getdate(),101),'/','')+'.xlsx'}

^{exec master..xp_cmdshell @cmdstring}

In the above, I have enabled cmd shell and as in my case, I have pivot of Excel. So I am just duplicating the same Excel sheet.

Step 4

Advanced step for Step 1 means if this step is successful (means copy of Excel is created, then what to do). If fail retry twice, if then fails too, then quit job and manage log in table.

Then in Advanced tab, set action on successfully completed: GO to next step/Or on failure: Quit the job. Click on OK.

Step 5

As Step 1 is created, click new to create step 2.

As marked with arrow, Step 1 is created. Now again click on New button to create Step 2.

Step 6

This Step 2 is to run stored procedure and export data in newly created Excel sheet.

Again write down Step Name and command in section to execute stored procedure:

Step Name: Run stored procedure and fill Excel sheet with data for Retention metrics POC.

Command: Exec EXEC stored_procedure_name;

Step 7

Again, set Go to next step on success of Sep 2 and on failure retry, if then again fails then quit job and maintain log.

Click on Advanced tab and set above property value as I marked.

And click on OK.

Step 8

As two steps are created, now final step is remaining to send Email with attachment. Click on New button.

Again, click on New button.

Step 9

Again, write step name and command below image to send email.

Step Name: SQL Job Agent to send Email with attachment of Retention Metrics

Command

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Retention Metrics POC',
  @recipients = 'teste@gmail.com',
  @subject = 'POC Weekly Report',
  @body = 'Metrics Query Report for testing',
  @importance = 'HIGH',
  @file_attachments = 'C:\Test\Retention_metrics_for_Caseworker_Activity'+
                       replace(convert(varchar, getdate(),101),'/','')+'.xlsx'

Here, above in command in profile name, write profile name which we have created earlier in profiler name (i.e., in Step 2, we have created profile name, same profile name will be used here) and rest of field as mail property like recipient can be set dynamically from table or we can put some fixed too.

And in subject, what we mentioned here that will be mail subject, similarly body property.

And as my email contains attachment, so I'm adding recently generated Excel in email.

Step 10

Again set success and failure steps.

Set the following property as marked.

And click on OK.

Step 11

All three steps are created. Now schedule job to run.

SO as three steps are created, click on Schedule tab.

Step 12

Here in Schedule tab, click on New button to schedule time to run job automatically.

In Schedule tab, click on New button.

Step 13

Job timing property as what I have set marked with yellow.

As marked with yellow for the property value to set.

Click on OK after all value is set.

Step 14

To set notification after Job ran successfully /failed:

In Notification tab, you can set value of action to perform after job successful/failure status.

And click on OK.

Conclusion

SQL Server Job agent is ready, and scheduled to run.

History

• 26^th September, 2020: Initial version