cancel
Showing results for 
Search instead for 
Did you mean: 

Get Result from a SQL Query using ExecuteSQL DI Server

Former Member
0 Kudos

Hi,

I need to get some values out by parsing a query (based on a custom view with joints) to the ExecuteSQL DI server. But I'm getting the below error.

Request :

<?xml version="1.0" encoding="utf-16"?>

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">

  <env:Header>

    <SessionID>8B55C26B-18F1-4002-BBE9-5F635DD23FC4</SessionID>

  </env:Header>

  <env:Body>

    <dis:ExecuteSQL xmlns:dis="http://www.sap.com/SBO/DIS">

      <DoQuery>SELECT B.ItemCode, B.Quantity FROM ( SELECT RDR1.ItemCode,  MAX(ORD.DocEntry) AS DocEntry FROM RDR1 INNER JOIN (SELECT DocEntry FROM ORDR WHERE ORDR.ObjType = '17' AND ORDR.CANCELED='N' AND ORDR.CardCode ='C20000') AS ORD ON (ORD.DocEntry = RDR1.DocEntry) WHERE RDR1.LineStatus = 'C' AND RDR1.ItemCode IN ('P10002','A00005','LM4029D','I00005','LM4029SB','C00005','I00004','C00004','I00007','C00006') GROUP BY RDR1.ItemCode) AS A LEFT JOIN RDR1 B ON (A.DocEntry=B.DocEntry and A.ItemCode=B.ItemCode)  INNER JOIN ORDR O ON (O.DocEntry=B.DocEntry) WHERE O.ObjType = '17' AND O.CANCELED='N' AND O.CardCode ='C20000' ORDER BY B.ItemCode </DoQuery>

    </dis:ExecuteSQL>

  </env:Body>

</env:Envelope>

Response :

- <env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">

- <env:Body>

- <env:Fault>

- <env:Code>

<env:Value>env:Receiver</env:Value>

- <env:Subcode>

<env:Value>-1</env:Value>

</env:Subcode>

</env:Code>

- <env:Reason>

<env:Text xml:lang="en">Error in SOAP command 'ExecuteSQL'</env:Text>

</env:Reason>

- <env:Detail>

<Command>ExecuteSQL</Command>

<SessionID>8B55C26B-18F1-4002-BBE9-5F635DD23FC4</SessionID>

</env:Detail>

</env:Fault>

</env:Body>

</env:Envelope>

I can retrieve the results in SQL Server Management Studio with the same exact query that I've parsed in to this API.

Please HELP.

Thank You.

Accepted Solutions (1)

Accepted Solutions (1)

maik_delly
Active Contributor
0 Kudos

Hi Hashitha,

I don't know about your ExecuteSQL example, but using CompanyService is working for me:


<?xml version=""1.0"" ?>

<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">

    <soap:Header>

        <MsgHeader xmlns=""http://www.sap.com/SBO/DIS"">

            <SessionID>" + SessionID + @"</SessionID>

            <ServiceName>CompanyService</ServiceName>

        </MsgHeader>

    </soap:Header>

    <soap:Body>

        <Query xmlns=""CompanyService"">

            <RecordsetParams xmlns=""http://www.sap.com/SBO/DIS"">

                <Query>SELECT B.ItemCode, B.Quantity FROM ( SELECT RDR1.ItemCode,  MAX(ORD.DocEntry) AS DocEntry FROM RDR1 INNER JOIN (SELECT DocEntry FROM ORDR WHERE ORDR.ObjType = '17' AND ORDR.CANCELED='N' AND ORDR.CardCode ='C20000') AS ORD ON (ORD.DocEntry = RDR1.DocEntry) WHERE RDR1.LineStatus = 'C' AND RDR1.ItemCode IN ('P10002','A00005','LM4029D','I00005','LM4029SB','C00005','I00004','C00004','I00007','C00006') GROUP BY RDR1.ItemCode) AS A LEFT JOIN RDR1 B ON (A.DocEntry=B.DocEntry and A.ItemCode=B.ItemCode)  INNER JOIN ORDR O ON (O.DocEntry=B.DocEntry) WHERE O.ObjType = '17' AND O.CANCELED='N' AND O.CardCode ='C20000' ORDER BY B.ItemCode

                </Query>

            </RecordsetParams>

        </Query>

    </soap:Body>

</soap:Envelope>

regards,

Maik

Former Member
0 Kudos

Hi Maik,

I've tried your suggestion. That works perfectly.

Thank You.

Answers (0)