调用 Azure 托管 SQL 中的 HTTP API 端点

huangapple go评论58阅读模式
英文:

Calling HTTP API endpoint in Azure Managed SQL

问题

我正在将SendGrid APIs与Azure托管SQL集成,并遇到以下问题。

我需要通过SendMail() API发送电子邮件并将响应返回给SQL Server。
由于我正在使用SQL托管实例,无法使用MSXML2.XMLHTTP调用API。

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

-- 代码片段
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

上面的代码段返回以下错误信息:

Msg 17750, Level 16, State 0, Procedure sp_OACreate, Line 1 [Batch Start Line 0]
无法加载DLL odsole70.dll,或其引用的DLL之一。原因:2(系统找不到指定的文件)。
Msg 17750, Level 16, State 0, Procedure sp_OAMethod, Line 1 [Batch Start Line 0]
无法加载DLL odsole70.dll,或其引用的DLL之一。原因:2(系统找不到指定的文件)。
Msg 17750, Level 16, State 0, Procedure sp_OAMethod, Line 1 [Batch Start Line 0]
无法加载DLL odsole70.dll,或其引用的DLL之一。原因:2(系统找不到指定的文件)。
Msg 17750, Level 16, State 0, Procedure sp_OAMethod, Line 1 [Batch Start Line 0]
无法加载DLL odsole70.dll,或其引用的DLL之一。原因:2(系统找不到指定的文件)。

是否有其他方法在SQL托管实例中实现此任务?

我可以简单地使用sp_send_dbmail实用程序,但如果我使用数据库邮件功能,则无法获取来自SendGrid的返回的MessageID。 (或者有获取它的方法吗?)

提前感谢。

英文:

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 &#39;MSXML2.XMLHTTP&#39;, @Object OUT;
Exec sp_OAMethod @Object, &#39;open&#39;, NULL, &#39;post&#39;,  
             &#39;&lt;API URL HERE &gt;&#39;, -- API URL
             &#39;false&#39;
Exec sp_OAMethod @Object, &#39;send&#39;
Exec sp_OAMethod @Object, &#39;responseText&#39;, @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.

答案1

得分: 0

OLE自动化存储过程不受托管实例支持。不过,您可以使用CLR存储过程。GitHub上有一个示例:https://github.com/microsoft/sql-server-samples/tree/master/samples/features/sql-clr/Curl

英文:

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

huangapple
  • 本文由 发表于 2023年2月27日 12:16:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/75576732.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定