1

I am new to SOAP.

I am trying to get the Send Object information and I am able to pull all the data I need except
<Properties>SendID</Properties>

This is the request I make in postman

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Header>
        <fueloauth>{{sf_ps_access_token}}</fueloauth>
    </soapenv:Header>
    <soapenv:Body>
        <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
            <RetrieveRequest>
                <ObjectType>Send</ObjectType>
                <Properties>ID</Properties>
                <Properties>BCCEmail</Properties>
                <Properties>SendID</Properties>
                <Properties>Client.PartnerClientKey</Properties>
                <Properties>PartnerKey</Properties>
                <Properties>CreatedDate</Properties>
                <Properties>ModifiedDate</Properties>
                <Properties>Client.ID</Properties>
                <Properties>Email.ID</Properties>
                <Properties>Email.PartnerKey</Properties>
                <Properties>SendDate</Properties>
                <Properties>FromAddress</Properties>
                <Properties>FromName</Properties>
                <Properties>Duplicates</Properties>
                <Properties>InvalidAddresses</Properties>
                <Properties>HardBounces</Properties>
                <Properties>SoftBounces</Properties>
                <Properties>OtherBounces</Properties>
                <Properties>ForwardedEmails</Properties>
                <Properties>UniqueClicks</Properties>
                <Properties>UniqueOpens</Properties>
                <Properties>NumberSent</Properties>
                <Properties>NumberDelivered</Properties>
                <Properties>NumberTargeted</Properties>
                <Properties>NumberErrored</Properties>
                <Properties>NumberExcluded</Properties>
                <Properties>Unsubscribes</Properties>
                <Properties>MissingAddresses</Properties>
                <Properties>Subject</Properties>
                <Properties>PreviewURL</Properties>
                <Properties>SentDate</Properties>
                <Properties>EmailName</Properties>
                <Properties>Status</Properties>
                <Properties>EmailSendDefinition.ObjectID</Properties>
                <Properties>EmailSendDefinition.CustomerKey</Properties>
                <Properties>Client.PartnerClientKey</Properties>
                <Properties>Email.PartnerKey</Properties>
                <Filter xsi:type="ns1:SimpleFilterPart" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
                    <Property>CreatedDate</Property>
                    <SimpleOperator>between</SimpleOperator>
                    <DateValue>2018-01-01</DateValue>
                    <DateValue>2021-12-09</DateValue>
                </Filter>
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </soapenv:Body>
</soapenv:Envelope>

This is the error I am getting:

<OverallStatus>Error: Column 'dbo.tblJobs.SendID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</OverallStatus>

How do I write SQL inside the xml SOAP request?

1 Answer 1

1

So, the reason you are getting this error is because SendID is NOT part of the SOAP Send Object. Now, that it is returning a SQL error is very odd (and also very telling that the SOAP object is just a wrapper for a SQL call internally in SFMC to gather this information and pass it back) and likely some sort of 'glitch' on SFMC end of error handling. It usually gives an error more along the lines of 'SendID is not a retrievable property for this object' or something like that.

And finally, the value in ID for the send Object is likely what you are looking for to match with JobID.

5
  • Thank you for your answer I`ll try to take a look
    – Eli Sigal
    Dec 15, 2021 at 21:31
  • I get the same error when I try to get ListSend Object data and add CreatedDate prop, where as, it is part of the Object.... This is weird
    – Eli Sigal
    Dec 16, 2021 at 8:30
  • I am trying to combine data between Send, ListSend, ClickEvent and Subscribers Objects Subscribers and ClickEvent I use SubscriberKey, ClickEvent and ListSend I use SendID So I am trying to figure out the correct key to combine the data between those and Send
    – Eli Sigal
    Dec 16, 2021 at 8:54
  • @EliSigal - this would be another question you would need to create as that is well beyond the original scope of your question. But a word of advice is to use the DESCRIBE SOAP action on those objects to get details on all the properties in the objects as well if they are retrievable. Dec 16, 2021 at 15:31
  • Thanks. I am using the describe option. thank you. question posted
    – Eli Sigal
    Dec 19, 2021 at 7:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .