Click here to Skip to main content
14,877,073 members
Articles / Database Development / SQL Server
Article
Posted 26 Sep 2020

Tagged as

Stats

5.3K views
12 bookmarked

Complete SQL Server Mail Service

Rate me:
Please Sign up or sign in to vote.
3.00/5 (3 votes)
11 Oct 2020Public Domain5 min read
SQL Server Job Agent for SQL mail service with attachment of Excel file
This is a mail service which is fixed to fire automatically on some day of month, with some Excel attachment. So I have created a mail service which will be completely dynamic to run 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:

Image 1

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:

Image 2

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:

Image 3

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.

Image 4

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.

Image 5

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.

Image 6

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.

Image 7

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

Step 4

Email Profile Security:

Image 8

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:

Image 9

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.

Image 10

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:

Image 11

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.

Image 12

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.

Image 13

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.

Image 14

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.

Image 15

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.

Image 16

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.

Image 17

Again, click on New button.

Step 9

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

Image 18

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

Command

SQL
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.

Image 19

Set the following property as marked.

And click on OK.

Step 11

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

Image 20

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.

Image 21

In Schedule tab, click on New button.

Step 13

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

Image 22

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:

Image 23

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

  • 26th September, 2020: Initial version

License

This article, along with any associated source code and files, is licensed under A Public Domain dedication

Share

About the Author

No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --