Recycle bin in marketing cloud / SOAP call active objects

Question

I'm using a SOAP call to perform an SQL Query and I get this error:

QueryDefinition was unable to queue this task. This query definition is not active

My call :

<RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
        <RetrieveRequest>
        <ObjectType>QueryDefinition</ObjectType>
        <Properties>ObjectID</Properties>
        <Properties>Name</Properties>
        <Filter xsi:type="ns1:SimpleFilterPart" xmlns:ns1="http://exacttarget.com/wsdl/partnerAPI">
                <Property>CustomerKey</Property>
                <SimpleOperator>equals</SimpleOperator>
                <Value>Scoring-Query-key</Value>
            </Filter>
        </RetrieveRequest>
</RetrieveRequestMsg>

I found that this error was thrown because I did delete the same SQL query with the same name and customerKey so when I retrieve the ObjectID via the customerKey, there are 3 of them like marketing cloud would have a recycle bin.

"Results":[
{"$":{"xsi:type":"QueryDefinition"},"PartnerKey":[{"$": 
{"xsi:nil":"true"}}],"ObjectID":["6fec1e6c-fa87-4016-b67c-b5777d21a17a"],"Name":["AllJobsID"]},
{"$":{"xsi:type":"QueryDefinition"},"PartnerKey":[{"$": 
{"xsi:nil":"true"}}],"ObjectID":["0e4969bd-916b-43ad-a237-b5e4733950f1"],"Name":["AllJobsID"]},
{"$":{"xsi:type":"QueryDefinition"},"PartnerKey":[{"$": 
{"xsi:nil":"true"}}],"ObjectID":["9b9890db-3934-407e-86f7-f032d166d56c"],"Name":["AllJobsID"]}]}]}]}}

Do you have any idea, how could I delete those ghosts ?

Answer 1

If you want to chase ghosts from your SOAP API calls, use the property : 'Status' that will tell you if the object is 'Active' or 'Inactive'. Here an example of result:

"soap:Body": [{
        "RetrieveResponseMsg": [{
            "$": {
                "xmlns": "http://exacttarget.com/wsdl/partnerAPI"
            },
            "OverallStatus": ["OK"],
            "RequestID": ["bdc3ef5a-22f7-4d10-a4fc-90089cfe57c3"],
            "Results": [{
                "$": {
                    "xsi:type": "QueryDefinition"
                },
                "PartnerKey": [{
                    "$": {
                        "xsi:nil": "true"
                    }
                }],
                "ObjectID": ["0e4969..."],
                "Name": ["AllJobsID"],
                "Status": ["Active"]
            }]

Use a complex filter in your SOAP call to chase them :

<Filter xsi:type="par:ComplexFilterPart" xmlns:par="http://exacttarget.com/wsdl/partnerAPI">
           <LeftOperand xsi:type="par:SimpleFilterPart">
              <Property>Status</Property>
              <SimpleOperator>equals</SimpleOperator>
              <Value>Active</Value>
           </LeftOperand>
           <LogicalOperator>AND</LogicalOperator>
           <RightOperand xsi:type="par:SimpleFilterPart">
              <Property>CustomerKey</Property>
              <SimpleOperator>equals</SimpleOperator>
              <Value>Scoring-Query-key</Value>
           </RightOperand>
        </Filter>