0

I have copied the code from setting soap request properties from T-sql and make some litle changes. If soap version 1.2 the code works fine. My problem is that soap version 1.1 wants to load the PELTT01.wsdl file to work.

declare 
@Url varchar(1024),
@HttpMethod varchar(10),
@ParamsValues varchar(1024), 
@SoapAction varchar(8000), 
@datefrom varchar(30),
@dateto varchar(30),
@sessionid as varchar(3000),
@xmlOut1 varchar(8000),
@RequestText as varchar(8000),
@wsdl as varchar(8000), 
@responseHeader varchar(4000)

DECLARE @t table (ID int, strxml xml)

set @Url = 'http://212.205.47.226:9003'
set @HttpMethod = 'soap'
set @SoapAction = '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pel="/PELTT01">
   <soapenv:Header/>
   <soapenv:Body>
      <pel:READ>
         <pel:wpel_code>999999999</pel:wpel_code>
         <pel:wpel_user>9999999</pel:wpel_user>
         <pel:wpel_pass>9999999</pel:wpel_pass>
         <pel:wpel_vg>ZK000000000GR</pel:wpel_vg>
         <pel:wpel_ref>?</pel:wpel_ref>
         <pel:wpel_flag>1</pel:wpel_flag>
      </pel:READ>
   </soapenv:Body>
</soapenv:Envelope>'

declare @obj int
    ,@response varchar(8000)
    ,@responseText varchar(8000)
    ,@status varchar(50)
    ,@statusText varchar(1024)
    ,@method varchar(10) = (case when @HttpMethod in ('soap','SOAP') then 'POST' else @HttpMethod end)

exec sp_OACreate 'MSXML2.ServerXMLHTTP', @obj out
exec sp_OAMethod @obj, 'Open', null, @method, @Url, 'false'
exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'text/xml; charset=utf-8'
exec sp_OAMethod @obj, 'setRequestHeader', null, 'SOAPAction', @SoapAction
exec sp_OAMethod @obj, 'send', null, @SoapAction

exec sp_OAGetProperty @obj, 'responseText', @responseText out
EXECUTE sp_OAMethod @Obj, 'getAllResponseHeaders', @responseHeader OUT
exec sp_OAGetProperty @obj, 'Status', @status out
exec sp_OADestroy @obj

SELECT @status 'status', @responseText 'responsetext', @responseHeader 'Response Header'
chris
  • 1
  • 2
  • 2
    Best thing, don't do this inside sql server - it's totally the wrong place to do it. – Dale K Aug 20 '22 at 10:55
  • Agree with @DaleK. Also the [`SOAPAction` header](https://www.w3.org/TR/2000/NOTE-SOAP-20000508/#_Toc478383528) isn't meant to receive the XML payload like that. Your source also did that incorrectly. – AlwaysLearning Aug 20 '22 at 11:11
  • You can use SQL Server Integration Services (SSIS) it has a built-in Task for that: https://www.sqlshack.com/ssis-web-service-tasks/ – Yitzhak Khabinsky Aug 21 '22 at 00:52

0 Answers0