时间戳

首页 > 解决方案 > 从 SQL-Server (T-SQL) 调用 Web 服务

json - 从 SQL-Server (T-SQL) 调用 Web 服务

问题描述

我尝试调用 Web 服务以从 SQL-Server 获取数据。

如何将此 SOAPUI 请求传输到从 sql-server 发起的请求，例如http://harshilnshah.blogspot.com/2013/03/call-webservice-from-stored-procedure.html

通过使用 SOAPUI 生成请求是有效的！

请求（正在工作，使用 SOAPUI 生成）：

GET https://one.server.com/api/export?count=2500&skip=0 HTTP/1.1
Accept-Encoding: gzip,deflate
Authorization: auth dVh4NmpBSFhpT01YMnRtWlhMd2d6
companyId: f5fbb815-51515
Host: one.server.com
Connection: Keep-Alive
User-Agent: Apache-HttpClient/4.1.1 (java 1.5)

从网络服务器（json）（SOAPUI）返回数据：

{
   "TotalResults": 10,
   "StartIndex": 0,
   "Count": 50,
   "List":    [
            {
         "Email": "email1@email.com",
         "FirstName": "Werner",
         "LastName": "Maier",
         "DisplayName": "Werner Maier",
         "Language": "de",
         "TimeZone": "W. Europe Standard Time",
         "Theme": null,
         "Groups": "admin,HR"
      },
      ...

谢谢并恭祝安康！

我试过这个：

    -- Enable Ole Automation
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO


BEGIN

    SET NOCOUNT ON;


DECLARE @Object INT;
DECLARE @Status INT;


DECLARE @requestBody NVARCHAR(MAX) = '{
"companyId": "f5fbb815-51515",
"Authorization": "auth dVh4NmpBSFhpT01YMnRtWlhMd2d6"
}'




EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @Object OUT;
EXEC sp_OAMethod @Object, 'Open', NULL, 'GET',  'https://one.server.com/api/export?count=2500&skip=0', 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
DECLARE @len INT = len(@requestBody) 
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Length', @len
EXEC sp_OAMethod @Object, 'send', null, @requestBody
EXEC sp_OAGetProperty @Object, 'Status', @Status OUT
EXEC sp_OADestroy @Object

END

标签： jsonsql-servertsqlsoapui

解决方案

推荐阅读