3

I'm doing some high level planning of an application at the moment. When a bcp import finishes, I'm going to send an email using Database Mail.

I'd like the email to have options or filters for the recipient. Their choices will determine which particular SQL scripts kick off.

For example:

from: [email protected]

to: [email protected]

Import has completed, would you like to:

+------------+---------+
|  Update    |   Find  |
+------------+---------+

My intuition was that I could make the choices mailto links, to populate the body of a response email. But I haven't been able to find any resources on triggering an alert when receiving an email. In fact, the diagram I found below suggests to me that you actually can't reply to Database Mail:

enter image description here

Has anyone ever worked on a project like this?

Improve this question

3 Answers 3

Reset to default
5

Database Mail in itself can only send emails, because it only 'talks' SMTP:

A Database Mail account contains the information that SQL Server uses to send e-mail messages to an SMTP server. Each account contains information for one e-mail server.

(from the documentation)

But (depending on the configuration of your SMTP server), you can use a valid email address within your organization (a user, or a group like [email protected]) as the sender of the email. Then, the receiver can just reply as they would to a normal email.

If you need to make some automated actions based on a reply (such as scheduling another task in the database), you're looking at a (much) more complicated integration, way beyond the scope of Database Mail.

Improve this answer
1
  • Gotcha, yeah I figured it was beyond the scope considering I googled for two hours and couldn't find a single person who even had the same question. I'm going to take Lowly's suggestion and build a GUI.
    – Henry Laney
    Oct 9, 2018 at 15:37
2

Database Mail is not meant for any sort of user or event driven stuff.

You can have a webservice to periodically check the bcp import status (log it to a queue table ) and based on that you can have have a success or failed status. If it is success then show the above fields that you want user to take action.

Remember : Event driven process is complicated, but if you build it right, it can free up a lot of manual hours.

You can use PowerShell or Python depending on what you are comfortable.

Improve this answer
1
  • Which webservices would you recommend for something like this? I've found different services that integrate SQL servers and Knack, such as Zapier, Integromat, and Workato.
    – Henry Laney
    Oct 9, 2018 at 15:39
1

As far as I know, you are correct that this is not possible with the built in mail system of SQL Server.

It sounds like you may want to build a GUI to facilitate the next steps for the user after the bulk import is finished and they are notified.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.