我构建了一个存储过程来调用 Web 服务并从端点检索响应。一切都很好，除了一些例外。我正在使用sp_OACreate、sp_OAMethod和sp_OAGetProperty来请求和获取回复。我已经用不同的数据测试了 686 次，其中 9 次因此错误而失败：
对象 ID 上的响应失败：ODSOLE 扩展过程。说明：为此字符串或二进制列或参数指定的源数据太长
我还有一个状态文本响应：
对于输入字符串："D"
and a status of "400'
根据我的日志，我的请求"发送"成功，消息已成功接收。这是没有成功恢复的响应。
奇怪的是，我联系了请求响应方的人，他们正在发回一条消息，我已成功从其他请求中收到
，如下所示：<responses success="true">
<response key="quoteRequest" service="quoteRequest" success="false"
message="Processing error received for quote request. Support has been notified and is looking into the error.">
</response>
</responses>
我知道这个SQL函数的最大长度是4000，即使请求成功，响应也永远不会那么长。
这是我用来执行的 SQL。我会包括 URL，但我觉得响应公司不会喜欢这样......
DECLARE
--this message is successful
@in_vchXMLMessage NVARCHAR(MAX) = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><requests username="WENG02importcreate" password="" id="69" token="db0bb761-88ea-11e7-95ef-0050560903bc"><request service="quoteRequest"><QuoteRequest><Header><Action>Add</Action><Date type="generation">2017-08-25T14:09:06.360</Date></Header><Shipment><Status>Pending</Status><Enterprise customerAcctNum="DSI-142319-110469" name="CHRIS STORJOHANN"/><ReferenceNumbers><ReferenceNumber type="Quote Number" isPrimary="true">1630178.000000000*</ReferenceNumber></ReferenceNumbers><Comments><Comment/></Comments><EquipmentList><EquipmentCode desc="" qty=""/></EquipmentList><Dates><Pickup><Date type="earliest">2017/08/14 00:00</Date><Date type="latest">2017/08/14 00:00</Date></Pickup><Drop><Date type="earliest">2017/08/14 00:00</Date><Date type="latest">2017/08/14 00:00</Date></Drop></Dates><Shipper><Address isResidential="false"><Alias/><Name>WENGER CORPORATION</Name><AddrLine1>555 PARK DRIVE</AddrLine1><City>OWATONNA</City><StateProvince>MN</StateProvince><PostalCode>55060</PostalCode><CountryCode>USA</CountryCode><Contacts><Contact><Name/><ContactMethods><ContactMethod type="" sequenceNum=""/></ContactMethods></Contact></Contacts><Comments/></Address></Shipper><Consignee><Address isResidential="false"><Alias/><Name>Tufts University</Name><AddrLine1>20 Talbot Ave</AddrLine1><City>Medford</City><StateProvince>MA</StateProvince><PostalCode>02155-5813</PostalCode><CountryCode>USA</CountryCode><Contacts><Contact><Name/><ContactMethods><ContactMethod type="" sequenceNum=""/></ContactMethods></Contact></Contacts><Comments/></Address></Consignee><HandlingUnits><HandlingUnit stackable="false" sequence="1"><Quantity units="Pallet">7.0</Quantity><Weight units="lb">147.0</Weight><Dimensions height="14.0" units="in" width="21.0" length="85.0"/><Items><Item sequence="1"><Description>RISER,3 STEP,6-18,GREY/GREY</Description><FreightClass>085</FreightClass><HazardousMaterial>false</HazardousMaterial><Weights><Weight units="lbs" type="planned">147.0</Weight></Weights><Quantities><Quantity type="planned">7.0</Quantity></Quantities><MonetaryValue units="USD">0.0</MonetaryValue></Item></Items></HandlingUnit></HandlingUnits><Payment><Method>Third Party</Method><BillTo thirdParty="true"><Address isresidential="false"><Alias/><Name>Diversified</Name><AddrLine1>37 Market St</AddrLine1><City>Kenilworth</City><StateProvince>NJ</StateProvince><PostalCode>07033</PostalCode><Contacts><Contact><Name/><ContactMethods><ContactMethod type="" sequenceNum=""/></ContactMethods></Contact></Contacts></Address></BillTo></Payment></Shipment></QuoteRequest></request></requests>',
-- this message is unsuccessful
-- @in_vchXMLMessage NVARCHAR(MAX) = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><requests username="WENG02importcreate" password="" id="69" token="db0bb761-88ea-11e7-95ef-0050560903bc"><request service="quoteRequest"><QuoteRequest><Header><Action>Add</Action><Date type="generation">2017-08-28T10:20:27.767</Date></Header><Shipment><Status>Pending</Status><Enterprise customerAcctNum="CK COMING- 100% DOWN" name="SHERRY ROOT"/><ReferenceNumbers><ReferenceNumber type="Quote Number" isPrimary="true">1633560.3910</ReferenceNumber></ReferenceNumbers><Comments><Comment/></Comments><EquipmentList><EquipmentCode desc="" qty=""/></EquipmentList><Dates><Pickup><Date type="earliest">2017/08/21 00:00</Date><Date type="latest">2017/08/21 00:00</Date></Pickup><Drop><Date type="earliest">2017/08/21 00:00</Date><Date type="latest">2017/08/21 00:00</Date></Drop></Dates><Shipper><Address isResidential="false"><Alias/><Name>WENGER CORPORATION</Name><AddrLine1>555 PARK DRIVE</AddrLine1><City>OWATONNA</City><StateProvince>MN</StateProvince><PostalCode>55060</PostalCode><CountryCode>USA</CountryCode><Contacts><Contact><Name/><ContactMethods><ContactMethod type="" sequenceNum=""/></ContactMethods></Contact></Contacts><Comments/></Address></Shipper><Consignee><Address isResidential="false"><Alias/><Name>Russell Elementary School</Name><AddrLine1>195 School St</AddrLine1><City>Rumney</City><StateProvince>NH</StateProvince><PostalCode>03266-3413</PostalCode><CountryCode>USA</CountryCode><Contacts><Contact><Name/><ContactMethods><ContactMethod type="" sequenceNum=""/></ContactMethods></Contact></Contacts><Comments/></Address></Consignee><HandlingUnits><HandlingUnit stackable="false" sequence="1"><Quantity units="Pallet">2.0</Quantity><Weight units="lb">1.0</Weight><Dimensions height="10.0" units="in" width="10.0" length="10.0"/><Items><Item sequence="1"><Description>RISER,SIGNATURE,3 STEP</Description><FreightClass>065</FreightClass><HazardousMaterial>false</HazardousMaterial><Weights><Weight units="lbs" type="planned">1.0</Weight></Weights><Quantities><Quantity type="planned">2.0</Quantity></Quantities><MonetaryValue units="USD">0.0</MonetaryValue></Item></Items></HandlingUnit><HandlingUnit stackable="false" sequence="1"><Quantity units="Pallet">2.0</Quantity><Weight units="lb">129.0</Weight><Dimensions height="105.0" units="in" width="186.0" length="725.0"/><Items><Item sequence="1"><Description>STEP/BACKRAIL PACK,3 STEP</Description><FreightClass>065</FreightClass><HazardousMaterial>false</HazardousMaterial><Weights><Weight units="lbs" type="planned">129.0</Weight></Weights><Quantities><Quantity type="planned">2.0</Quantity></Quantities><MonetaryValue units="USD">0.0</MonetaryValue></Item></Items></HandlingUnit><HandlingUnit stackable="false" sequence="1"><Quantity units="Pallet">2.0</Quantity><Weight units="lb">98.0</Weight><Dimensions height="95.0" units="in" width="330.0" length="485.0"/><Items><Item sequence="1"><Description>CARRIAGE/STRINGER PK,3 STEP</Description><FreightClass>065</FreightClass><HazardousMaterial>false</HazardousMaterial><Weights><Weight units="lbs" type="planned">98.0</Weight></Weights><Quantities><Quantity type="planned">2.0</Quantity></Quantities><MonetaryValue units="USD">0.0</MonetaryValue></Item></Items></HandlingUnit></HandlingUnits><Payment><Method>Third Party</Method><BillTo thirdParty="true"><Address isresidential="false"><Alias/><Name>Russell Elementary School</Name><AddrLine1>195 School St</AddrLine1><City>Rumney</City><StateProvince>NH</StateProvince><PostalCode>03266-3413</PostalCode><Contacts><Contact><Name/><ContactMethods><ContactMethod type="" sequenceNum=""/></ContactMethods></Contact></Contacts></Address></BillTo></Payment></Shipment></QuoteRequest></request></requests>',
-- Error handling variables
@v_nSysErrorNum INTEGER,
@v_nRowCount INTEGER,
@v_nReturn INTEGER,
@v_vchCode NVARCHAR(20),
@v_vchMsg NVARCHAR(200),
@c_vchObjName NVARCHAR(50),
-- Local Variables
@v_nCount INTEGER,
@v_nObject INTEGER,
@v_vchURL NVARCHAR(2000),
@v_vchHost NVARCHAR(100),
@v_vchUser NVARCHAR(100),
@v_vchPswrd NVARCHAR(100),
@v_nResult INTEGER,
@v_vchSource NVARCHAR(500),
@v_vchDesc NVARCHAR(500),
@v_vchStatusText NVARCHAR(1000),
@v_vchStatus NVARCHAR(1000),
@v_vchResponseXML NVARCHAR(2000)
-- Set Constants
SET @c_vchObjName = N'usp_web_service_call';
SET @v_vchCode = N'SUCCESS';
SET @v_vchMsg = N'NONE';
SELECT @v_vchURL = c1
FROM t_control
WHERE control_type = 'TMS_WEB_SERV_URL'
-----------------------------------------------------------------------------------
-- Create Web Service Call
-----------------------------------------------------------------------------------
EXEC @v_nResult = sp_OACreate 'MSXML2.ServerXMLHttp', @v_nObject OUT
IF @v_nResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @v_nObject, @v_vchSource OUT, @v_vchDesc OUT
SET @v_vchCode = N'-20001';
SET @v_vchMsg = N'Create Failed on Object ID ' + ISNULL(@v_vchSource, '') + ' description ' + ISNULL(@v_vchDesc , '');
GOTO DESTROY;
END
-----------------------------------------------------------------------------------
-- Open Web Service Call
-----------------------------------------------------------------------------------
EXEC @v_nResult = sp_OAMethod @v_nObject, 'Open', NULL, 'POST', @v_vchURL, 'false'
IF @v_nResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @v_nObject, @v_vchSource OUT, @v_vchDesc OUT
SET @v_vchCode = N'-20002';
SET @v_vchMsg = N'Open Failed on Object ID ' + ISNULL(@v_vchSource, '') + ' description ' + ISNULL(@v_vchDesc , '');
GOTO DESTROY;
END
-----------------------------------------------------------------------------------
-- Request Header Info
-----------------------------------------------------------------------------------
EXEC @v_nResult = sp_OAMethod @v_nObject, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
IF @v_nResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @v_nObject, @v_vchSource OUT, @v_vchDesc OUT
SET @v_vchCode = N'-20003';
SET @v_vchMsg = N'Header Failed on Object ID ' + ISNULL(@v_vchSource, '') + ' description ' + ISNULL(@v_vchDesc , '')
GOTO DESTROY;
END
SELECT 'req hdr', @v_nResult
-----------------------------------------------------------------------------------
-- Send XML Web Service Message
-----------------------------------------------------------------------------------
EXEC @v_nResult = sp_OAMethod @v_nObject, 'Send', null, @in_vchXMLMessage
IF @v_nResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @v_nObject, @v_vchSource OUT, @v_vchDesc OUT
SET @v_vchCode = N'-20004';
SET @v_vchMsg = N'Send Failed on Object ID ' + ISNULL(@v_vchSource, '') + ' description ' + ISNULL(@v_vchDesc , '')
GOTO DESTROY;
END
SELECT 'send xml', @v_nResult
-----------------------------------------------------------------------------------
-- Get Status Text
-----------------------------------------------------------------------------------
EXEC @v_nResult = sp_OAGetProperty @v_nObject, 'StatusText', @v_vchStatusText OUT
IF @v_nResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @v_nObject, @v_vchSource OUT, @v_vchDesc OUT
SET @v_vchCode = N'-20004';
SET @v_vchMsg = N'Send Failed on Object ID ' + ISNULL(@v_vchSource, '') + ' description ' + ISNULL(@v_vchDesc , '')
GOTO DESTROY;
END
sELECT 'statustext', @v_vchStatusText
-----------------------------------------------------------------------------------
-- Get Status
-----------------------------------------------------------------------------------
EXEC @v_nResult = sp_OAGetProperty @v_nObject, 'Status', @v_vchStatus OUT
IF @v_nResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @v_nObject, @v_vchSource OUT, @v_vchDesc OUT
SET @v_vchCode = N'-20004';
SET @v_vchMsg = N'Send Failed on Object ID ' + ISNULL(@v_vchSource, '') + ' description ' + ISNULL(@v_vchDesc , '')
GOTO DESTROY;
END
sELECT 'status', @v_vchStatus
-----------------------------------------------------------------------------------
-- Get response text
-----------------------------------------------------------------------------------
EXEC @v_nResult = sp_OAGetProperty @v_nObject, 'responseText', @v_vchResponseXML OUTPUT
sELECT @v_vchResponseXML
IF @v_nResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @v_nObject, @v_vchSource OUT, @v_vchDesc OUT
SET @v_vchCode = N'-20005';
SET @v_vchMsg = N'Response Failed on Object ID: ' + ISNULL(@v_vchSource, '') + '. Description: ' + ISNULL(@v_vchDesc , '')
GOTO DESTROY;
END
sELECT @v_vchResponseXML
DESTROY:
EXEC sp_OADestroy @v_nObject
SELECT @v_nObject, @v_vchCode, @v_vchMsg
更新 10-26-2017： 下面是 Web 服务发送中使用的字符串。
<?xml version="1.0" encoding="UTF-8"?>
<requests username="WENG02importcreate" password="" id="69" token="db0bb761-88ea-11e7-95ef-0050560903bc">
<request service="quoteRequest">
<QuoteRequest>
<Header>
<Action>Add</Action>
<Date type="generation">10/25/2017 15:29:54</Date>
</Header>
<Shipment>
<Status>Pending</Status>
<Enterprise customerAcctNum="WENG02EXT" name="WENGER CORPORATION EXECUTION" />
<ReferenceNumbers>
<ReferenceNumber type="ShipmentID" isPrimary="true">1638351.0000193682</ReferenceNumber>
<ReferenceNumber type="OrderNumber" isPrimary="false">1638351</ReferenceNumber>
<ReferenceNumber type="WengerInstall" isPrimary="false">false</ReferenceNumber>
<ReferenceNumber type="Cube" isPrimary="false">105.4</ReferenceNumber>
<ReferenceNumber type="Customer PO" isPrimary="false">50% CHECK RECEIVED #5104</ReferenceNumber>
<ReferenceNumber type="ShipCondition" isPrimary="false">PPB</ReferenceNumber>
</ReferenceNumbers>
<Comments>
<Comment type="SpecialInstructions" />
</Comments>
<Dates>
<Pickup>
<Date type="earliest">11/06/2017 00:00</Date>
<Date type="latest">11/06/2017 00:00</Date>
</Pickup>
<Drop>
<Date type="earliest">11/06/2017 00:00</Date>
<Date type="latest">11/06/2017 00:00</Date>
</Drop>
</Dates>
<Shipper>
<Address isResidential="false">
<Alias />
<Name>WENGER CORPORATION</Name>
<AddrLine1>555 PARK DRIVE</AddrLine1>
<City>OWATONNA</City>
<StateProvince>MN</StateProvince>
<PostalCode>55060</PostalCode>
<CountryCode>USA</CountryCode>
<Contacts>
<Contact>
<Name />
<ContactMethods>
<ContactMethod type="" sequenceNum="" />
</ContactMethods>
</Contact>
</Contacts>
<Comments>
<Comment>50% DOWNPAYMENT CHECK RECEIVED</Comment>
</Comments>
</Address>
</Shipper>
<Consignee>
<Address isResidential="false">
<Alias />
<Name>Immanuel Lutheran Church</Name>
<AddrLine1>N554 County Road R</AddrLine1>
<City>Watertown</City>
<StateProvince>WI</StateProvince>
<PostalCode>53098-4805</PostalCode>
<CountryCode>USA</CountryCode>
<Contacts>
<Contact>
<Name>Att: Lynn Baumann</Name>
<ContactMethods>
<ContactMethod type="phone" sequenceNum="1">920-925-3540</ContactMethod>
</ContactMethods>
</Contact>
</Contacts>
<Comments />
</Address>
</Consignee>
<HandlingUnits>
<HandlingUnit stackable="false" sequence="1">
<Quantity units="Pallet">9.0</Quantity>
<Weight units="lb">804.0</Weight>
<Dimensions height="23.0" units="in" width="143.0" length="257.0" />
<Items>
<Item sequence="1" id="024F017">
<Description>CLEACHERS OR GRANDSTANDS METAL OR WOOD, KD, TELESCOPED OR COLLAPSED, NOI; OR PARTS, NOI</Description>
<FreightClass>085</FreightClass>
<HazardousMaterial>false</HazardousMaterial>
<Weights>
<Weight units="lbs" type="planned">804.0</Weight>
</Weights>
<NMFC>89803</NMFC>
<Quantities>
<Quantity type="planned">9.0</Quantity>
</Quantities>
<MonetaryValue units="USD">0.0</MonetaryValue>
</Item>
</Items>
</HandlingUnit>
</HandlingUnits>
<Payment>
<Method>Prepaid</Method>
<BillTo thirdParty="false">
<Address isresidential="false">
<Alias />
<Name>WENGER CORPORATION</Name>
<AddrLine1>PO BOX 23000</AddrLine1>
<AddrLine2 />
<City>Hickory</City>
<StateProvince>NC</StateProvince>
<PostalCode>28603</PostalCode>
<CountryCode>USA</CountryCode>
<Contacts>
<Contact>
<Name />
<ContactMethods>
<ContactMethod type="" sequenceNum="" />
</ContactMethods>
</Contact>
</Contacts>
</Address>
</BillTo>
</Payment>
</Shipment>
</QuoteRequest>
</request>
</requests>
当响应没有接近最大长度时，有没有人遇到过这个问题？感谢您的任何帮助！
您应该将内容长度发送到 Web 服务器。 在您的代码中，这可以按以下方式完成：
SET @len = LEN(@in_vchXMLMessage);
EXEC @v_nResult = sp_OAMethod @v_nObject,'setRequestHeader',NULL,'Content-Length',@len;
IF @v_nResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @v_nObject, @v_vchSource OUT, @v_vchDesc OUT
SET @v_vchCode = N'-20004';
SET @v_vchMsg = N'Lengh Failed on Object ID ' + ISNULL(@v_vchSource, '') + ' description ' + ISNULL(@v_vchDesc , '')
GOTO DESTROY;
END
SELECT 'set len', @v_nResult
您应该在发送主消息之前执行此操作。 希望这有帮助
使用表格类型在其中获得无限的响应。
替换代码中的行，如下所示：
DECLARE @ResponseTable as table(Json_Table nvarchar(max))
EXEC sp_OAMethod @token, 'ResponseText', @ResponseTable OUTPUT
INSERT into @ResponseTable(Json_Table) EXEC sp_OAGetProperty @token, 'ResponseText'
SELECT * from @ResponseTable