SQL Server 可以发送 Web 请求吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5983599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can SQL Server send a web request?
提问by Lehi Sanchez
I have an SQL Server database and an intranet web application on a local network. The intranet web application creates records and sends them to the database. I have an Internet web application that I would like to send new records to using the SQL Server database on the local network.
我在本地网络上有一个 SQL Server 数据库和一个 Intranet Web 应用程序。Intranet Web 应用程序创建记录并将它们发送到数据库。我有一个 Internet Web 应用程序，我想使用本地网络上的 SQL Server 数据库将新记录发送到该应用程序。
I can't change/modify the intranet web application for various reasons so the only option I have is to create a trigger on the local SQL Server that will send new records to the Internet web application using some sort of http post request or url call.
由于各种原因，我无法更改/修改 Intranet Web 应用程序，因此我唯一的选择是在本地 SQL Server 上创建一个触发器，该触发器将使用某种 http post 请求或 url 调用将新记录发送到 Internet Web 应用程序.
The INTERNET web application is set up with a RESTful api that can receive new records via form post to a publicly accessible url (e.g. http://www.example.com/records/new).
INTERNET Web 应用程序设置了 RESTful api，可以通过表单发布到可公开访问的 url（例如http://www.example.com/records/new）接收新记录。
Does anyone know if sending data (xml, json, plain variables in the url) via a url can be accomplished in SQL Server?
有谁知道通过 url 发送数据（xml、json、url 中的普通变量）是否可以在 SQL Server 中完成？
Thanks for any thoughts!
感谢您的任何想法！
回答by Remus Rusanu
It is possible, but in the real world is a little bit more complicated than the naive approach you envision. Primarily, it is unacceptable to have a trigger wait for a HTTP request:
这是可能的，但在现实世界中比您想象的幼稚方法要复杂一些。首先，让触发器等待 HTTP 请求是不可接受的：
- For one, your application will crawl to a screeching halt, because triggers will block resources (primarily locks) waiting for a response from some far and away WWW service.
- Second, more subtle but far worse, is the issue of correctness in presence of rollbacks. If the transaction that issued to HTTP requests rolls back, there is no way to 'undo' the HTTP request.
- 一方面，您的应用程序将爬行到突然停止，因为触发器将阻止资源（主要是锁）等待来自某个遥远的 WWW 服务的响应。
- 其次，更微妙但更糟糕的是存在回滚时的正确性问题。如果发出 HTTP 请求的事务回滚，则无法“撤消”HTTP 请求。
The solution is to decouple the trigger from the HTTP request via a queue. The trigger enqueues the request into a local queue and commits, while a separate piece of processing dequeues these requests and issues the HTTP request. This solves both problems pointed out above. You can use ordinary tables for queues (see Using Tables as Queues) or you can use Service Broker, both work well.
解决方案是通过队列将触发器与 HTTP 请求分离。触发器将请求排入本地队列并提交，而单独的处理部分将这些请求出列并发出 HTTP 请求。这解决了上面指出的两个问题。您可以将普通表用于队列（请参阅将表用作队列），也可以使用Service Broker，两者都可以很好地工作。
Now on how to dequeue this requests and actually place the HTTP call, I strongly recommend using a dedicated process (ie. an application dedicated for this purpose). While it is possible to use SQLCLR, it is a very bad choice. SQL Server resources (specifically workers) are far to precious to waste on waiting for Internet responses.
现在关于如何使该请求出列并实际发出 HTTP 调用，我强烈建议使用专用进程（即专用于此目的的应用程序）。虽然可以使用 SQLCLR，但这是一个非常糟糕的选择。SQL Server 资源（特别是worker）在等待 Internet 响应上非常宝贵。
回答by abatishchev
As a good (rather reliable and scalable) option, you can use SQL CLRto interact with web application / web service.
作为一个不错的（相当可靠和可扩展的）选项，您可以使用SQL CLR与 Web 应用程序/Web 服务进行交互。
For example, search for "SQL CLR WebRequest" or "SQL CLR WCF".
例如，搜索“ SQL CLR WebRequest”或“ SQL CLR WCF”。
回答by delphifive
First do The follow
先做以下
sp_configure 'show advanced options', 1;
go
RECONFIGURE;
go
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Then Create a function
然后创建一个函数
create function GetHttp
(
@url varchar(8000)
)
returns varchar(8000)
as
BEGIN
DECLARE @win int
DECLARE @hr int
DECLARE @text varchar(8000)
EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win
RETURN @text
END
use like:
使用像：
select dbo.GetHttp('http://127.0.0.1/mywebpage.php')
回答by Geraldo Diaz
You could try using this CLR Stored procedure SQL-APIConsumer.
您可以尝试使用此 CLR 存储过程SQL-APIConsumer。
It has multiple procedures that allows you calling simples API that only required a parameters or even passing multiples headers and tokens authentications.
它有多个过程，允许您调用只需要一个参数甚至传递多个标头和令牌身份验证的简单 API。
