0

I am in the process of integrating SendGrid APIs with Azure Managed SQL and I am facing the below issue.

I am required to send an email via the SendMail() API and get the response back to SQL Server. Since I am using SQL Managed instance, I cannot use the MSXML2.XMLHTTP to call the APIs.

Declare @Object as Int;
Declare @ResponseText as Varchar(8000);

--Code Snippet
Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post',  
             '<API URL HERE >', -- API URL
             'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
Select @ResponseText  --
Exec sp_OADestroy @Object

Above code snip returns

Msg 17750, Level 16, State 0, Procedure sp_OACreate, Line 1 [Batch Start Line 0] Could not load the DLL odsole70.dll, or one of the DLLs it references. Reason: 2(The system cannot find the file specified.). Msg 17750, Level 16, State 0, Procedure sp_OAMethod, Line 1 [Batch Start Line 0] Could not load the DLL odsole70.dll, or one of the DLLs it references. Reason: 2(The system cannot find the file specified.). Msg 17750, Level 16, State 0, Procedure sp_OAMethod, Line 1 [Batch Start Line 0] Could not load the DLL odsole70.dll, or one of the DLLs it references. Reason: 2(The system cannot find the file specified.). Msg 17750, Level 16, State 0, Procedure sp_OAMethod, Line 1 [Batch Start Line 0] Could not load the DLL odsole70.dll, or one of the DLLs it references. Reason: 2(The system cannot find the file specified.).

Is there any alternate way to achieve this task using SQL Managed Instance?

I can simply use the sp_send_dbmail utility, however, I cannot grab the returning MessageID from SendGrid if I use the Database Mail feature. (Or is there a way to get it?)

Thanks in advance.

Harsha W
  • 3,162
  • 5
  • 43
  • 77
  • Do you have to do this stuff from sql? I'm thinking perhaps there can be a middle layer which connects these two – siggemannen Feb 27 '23 at 08:55
  • @siggemannen I am about to apply this solution for an existing application that already uses dbmail functionality. I am trying to find whether there is an approach with minimal changes. If this is not possible, YES I have to go for a middle layer. – Harsha W Feb 27 '23 at 12:26

1 Answers1

0

OLE Automation stored procedures are not supported in Managed Instance. You can use CLR procedures, though. There's a sample for it on GitHub: https://github.com/microsoft/sql-server-samples/tree/master/samples/features/sql-clr/Curl

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67