I'm trying to call a SOAP web service from SQL Server using OLE Automation stored procedures, and I've got troubles with the response which is Content-Encoding gzip.
When test the method using SOAP UI works like a charm.
Soap UI shows response like xml. Http log shows response is gzip encoded
When I make the call from SQL Server, I get an unexpected response like this
Status return is ok 200.
GetAllResponseHeaders doesn't include 'Content-Encoding: gzip', Soap UI does!
I included all request headers showed on SOAP UI.
Here is my code (I omitted error handling for the sake of readability)
DECLARE
@URI varchar(2000) = 'http://foo/foo.wsdl',
@methodName varchar(50) = 'post',
@requestBody varchar(8000) = '<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:CCTwebservices"><soapenv:Header/><soapenv:Body><urn:recepcionLiberacionTatc soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><user xsi:type="xsd:string">foo</user><password xsi:type="xsd:string">foo</password><dataIn xsi:type="xsd:string"><![CDATA[foo]]></dataIn></urn:recepcionLiberacionTatc></soapenv:Body></soapenv:Envelope>',
@SoapAction varchar(255) = 'urn:CCTwebservices#recepcionLiberacionTatc',
@responseText varchar(8000);
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
--create object
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
-- open the destination URI with Specified method
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', null, null
-- set request headers
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Accept-Encoding', 'gzip,deflate'
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Host', 'ws.foo`enter code here`.cl'
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Connection', 'Keep-Alive'
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction
declare @len int
set @len = len(@requestBody)
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len
-- send the request
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
EXEC @hResult = sp_OAMethod @objectID, 'getResponseHeader', null, 'Content-Encoding' -- Return null
EXEC @hResult = sp_OAMethod @objectID, 'GetAllResponseHeaders' -- Response doesn't incluye 'Content-Encoding: gzip', Soap UI does!.
exec sp_OAGetProperty @objectID, 'StatusText'
exec sp_OAGetProperty @objectID, 'Status'
exec sp_OAGetProperty @objectID, 'responseText' -- return the xml show previosly
exec sp_OADestroy @objectID
Any help to make this work?