英文:
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 '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.
答案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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论