Calling an SAP web service from SQL server

[murtuzakhanbsa]

Hello!
How do I call an SAP web service from an SQL server?

I have tried a couple different ways in SQL to call the service, but either get NULL as a response, or an ‘Unsupported xstream found’ error.

Below is one of the procedures I tried calling the web service with: I’m just learning this aspect with SQL and web services so any help, or pointing in the right direction is very appreciated.

The @authHeader confuses me on passing the user:password. Any idea on the proper way to pass the user:password?

DECLARE @authHeader NVARCHAR(64);

DECLARE @contentType NVARCHAR(64);

DECLARE @postData NVARCHAR(2000);

DECLARE @responseText NVARCHAR(2000);

DECLARE @responseXML NVARCHAR(2000);

DECLARE @ret INT;

DECLARE @status NVARCHAR(32);

DECLARE @statusText NVARCHAR(32);

DECLARE @token INT;

DECLARE @url NVARCHAR(256);

--SET @authHeader = 'BASIC password???'; -- not sure if a key, or password goes here, if it is even needed

SET @contentType = 'application/x-www-form-urlencoded';

SET @postData = '<soapenv:Body>

<urn:ZppShopflrGetPlannedOrders>

<AlternativeBom>99</AlternativeBom>

<BomCategory>Z</BomCategory>

<Language>E</Language>

<PartNumber>S999999999</PartNumber>

<Plant>9999</Plant>

</urn:ZppShopflrGetPlannedOrders>

</soapenv:Body>'

SET @url = 'http://frsl09v005.dp.net:8020/sap/bc/srt/rfc/sap/zpp_ws_get_planned_orders_df/100/get_planned_orders/get_planned_orders'

-- Open the connection.

EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;

IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.

EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';

--EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authentication', @authHeader;

EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;

EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;

-- Handle the response.

EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;

EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;

EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.

PRINT 'Status: ' + @status + ' (' + @statusText + ')';

PRINT 'Response text: ' + @responseText;

-- Close the connection.

EXEC @ret = sp_OADestroy @token;

IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);

Error Returned;

Status: 500 (Internal Server Error)