0

I need to be able to send emails from a stored procedure in Azure SQL which doesn't support typical SQL mail and cannot send mail directly. I don't want to use the hack that has been identified in an Italian post by Polizzi.

I've seen references to Logic Apps, worker roles, and connectors but nothing seems to fit what I want to do... or I don't understand how they fit my purpose. In the stored procedure, I will pull the email address and body from the database depending on different types of events: registration, new assignments, notifications, etc. and send them the appropriate email.

Does anyone have an example of a Azure SQL stored procedure sending email through SendGrid?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Velocedge
  • 1,222
  • 1
  • 11
  • 35
  • SMO support [Using Database Mail](https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/tasks/using-database-mail?view=sql-server-ver15) with Azure SQL database, can this be help? – Leon Yue Nov 19 '19 at 06:15
  • It appears it would allow you to create an account but still no way to send mail from sql because there is no msdb.dbo.sp_send_dbmail... in fact, Azure SQL has no msdb at all. – Velocedge Nov 19 '19 at 11:45
  • Does this answer your question? [How to configure mail on Azure SQL Database](https://stackoverflow.com/questions/54782922/how-to-configure-mail-on-azure-sql-database) – Michael Freidgeim Dec 01 '19 at 19:28

1 Answers1

0

Had looked at the answer but didn't pick up on the subtleties at first. The answer is in https://www.codeproject.com/Tips/1226872/Send-Email-from-Azure-SQL-using-Logic-App which uses an Azure Logic App. You have to create a table (with identity as primary key) that contains all the email fields. Then create an Logic App that triggers on an insert into that table. You can then connect the SendGrid module... but the trick is to use the "dynamic" fields (fields from the table) in your template. I also added a row delete module after SendGrid so I didn't have to delete the row after the email had been sent.

Velocedge
  • 1,222
  • 1
  • 11
  • 35
  • Congratulations, you can mark it as answer. This can be beneficial to other community members. Thank you. – Leon Yue Nov 20 '19 at 01:23