2

Could you please let me know what's wrong with my code?

<script runat="server">
Platform.Load("core", "1.1.1");

var prox = new Script.Util.WSProxy();

//Switch context to different BU. Change the MID to suit.
 prox.setClientId({"ID": 99999999});
 try {
 var query_string = "select top 10 name from table"
 var dataExtensionCustomerKey = "dataExtensionCustomerKey";

 var queryObj = {
    ObjectID: queryObjectObjectID,
    Name: dataExtensionCustomerKey,
    CustomerKey: dataExtensionCustomerKey,
    Description: "Description",
    QueryText: query_string,
    TargetUpdateType: 'Overwrite',
    DataExtensionTarget: {
        CustomerKey: "tyasduysgduchi",
        Name: "sql"
    }

};
 var update_status = prox.updateItem("QueryDefinition", queryObj);
 Write(Stringify(update_status)+"<br><br>")

 if (update_status == 'OK') {
    sleep(5000);
  prox.performItem("QueryDefinition", queryObj);
 }

} catch (err) {
 Write(Stringify(err))
} 

//reset the context if required
prox.resetClientIds();
</script>

Getting error:

"Status": "Error",
"RequestID": "asasadds1232435",
"Results": [{
"Object": {
        "CustomerKey": "query",
        "Name": "query",
        "Status": "Active",
        "ObjectID": "6572345748",
        "QueryText": "select * from table",
        "TargetType": null,
        "DataExtensionTarget": null,
        "TargetUpdateType": "Overwrite",
        "FileSpec": null,
        "FileType": null,
        "CategoryID": 0,
        "InteractionObjectID": null,
        "Description": "desc",
        "Keyword": null,
        "Client": {
            "ID": 99999,
            "ClientID1": 0,
            "PartnerClientKey": null,
            "UserID": 0,
            "PartnerUserKey": null,
            "CreatedBy": 0,
            "ModifiedBy": 0,
            "EnterpriseID": 0,
            "CustomerKey": null,
            "CustomerID": null
        },
        "PartnerKey": null,
        "PartnerProperties": null,
        "CreatedDate": "0001-01-01T00:00:00.000",
        "ModifiedDate": null,
        "ID": 0,
        "Owner": null,
        "CorrelationID": null,
        "ObjectState": null,
        "IsPlatformObject": false
    },
    "UpdateResults": null,
    "ParentPropertyName": null,
    "StatusCode": "Error",
    "StatusMessage": "Exception occurred during [UpdateQueryDefinition] ErrorID: 98517498",
    "OrdinalID": 0,
    "ErrorCode": 2,
    "RequestID": null,
    "ConversationID": null,
    "OverallStatusCode": null,
    "RequestType": "Synchronous",
    "ResultType": null,
    "ResultDetailXML": null
}]
  • You need to provide more information about what you are trying to achieve with your code and what errors you are getting.. – Rachid Mamai Feb 27 at 12:27
  • @RachidMamai I am trying to update a QueryActivity locate in a Child BU, running the SSJS from the parent BU, the error got from catch is the following: { "message": "Object reference not set to an instance of an object.", "description": "System.NullReferenceException: Object reference not set to an instance of an object. - from Jint\r\n\r\n" } – Felipe Jordani Feb 27 at 12:32
  • And you verified the query target data extension's customer/external key? – Adam Spriggs Feb 27 at 12:55
  • @AdamSpriggs the query customer key is in the code, but the data extension customer isn’t there, as I only want to update the Query. – Felipe Jordani Feb 27 at 13:03
  • The same code works if I don't try to impersonate the child BU, but unfortunately the SQL is in the child BU, and I need to run it there – Felipe Jordani Feb 27 at 16:53
2

When updating a QueryDefinition object via SOAP(WSProxy would have the same limitation), it requires that the ObjectID be passed. It will not find it based on CustomerKey alone. A retrieve can be used to find the ObjectID.

|improve this answer|||||
  • Thanks Mac, I had retrieve the ObjectID, and passed it in the var queryObj = { Name: dataExtensionCustomerKey, CustomerKey: dataExtensionCustomerKey, Description: "desc", QueryText: query_string, TargetUpdateType: 'Overwrite', ObjectID: queryObjectObjectID }; But I am still getting the "StatusMessage": "Exception occurred during [UpdateQueryDefinition] ErrorID: 1041625481" And the catch error "message": "Object reference not set to an instance of an object.", – Felipe Jordani Feb 27 at 13:42
2

I did notice that your perform WSProxy is malformed. This may be the cause for the error.

First you are missing some of the parameters to be passed in it.

prox.performItem("QueryDefinition", queryObj);

should be

prox.performItem("QueryDefinition", props, action, opts);

Where props is the object stating the ObjectID of the query you want to perform:

var props = {
    ObjectID: queryObjID 
};

action should be "Start" as you are running the Query.
opts is fine to leave as an empty object like:

var opts = {};

Your current object you are passing to the perform call (queryObj) is not formatted correctly for this action, which is likely causing issues. I also did notice that inside this object you use both " and ' to denote constant strings - it is highly recommended you instead just use " on both.

Outside that, I would try commenting out the executions and just write each part of it to ensure that it is filling in correct information. Then I would take this info and verify in the child BU that they actually exist. If those both are accurate, then I would first try just the update call. If that succeeds, then attempt the perform (with above adjustments). If after all this it is still failing, at least you know have a stronger idea on what part is causing the issue which should make it easier to debug.

|improve this answer|||||
2

The non-WSProxy method for updating queries is a lot more straightforward, IMO. Here's an example that I pulled from a Script Activity that I have running:

  var addObj = {}
  addObj.Name = queryNameAndExternalKey;
  addObj.CustomerKey = queryNameAndExternalKey;
  addObj.QueryText = queryText;
  addObj.TargetType = "DE";
  addObj.TargetUpdateType = action;
  addObj.Target = {};
  addObj.Target.Name = foundDENameAndExternalKey;
  addObj.Target.CustomerKey = foundDENameAndExternalKey;
  addObj.CategoryID = foundQueryCategoryID;

  var addStatus = QueryDefinition.Add(addObj);

  if (addStatus != "OK") {

    var updateObj = {};
    updateObj.QueryText = queryText;
    updateObj.TargetType = "DE";
    updateObj.TargetUpdateType = action;
    updateObj.Target = {};
    updateObj.Target.Name = foundDENameAndExternalKey;
    updateObj.Target.CustomerKey = foundDENameAndExternalKey;
    updateObj.CategoryID = foundQueryCategoryID;

    var qd = QueryDefinition.Init(queryNameAndExternalKey);
    var updateStatus = qd.Update(updateObj);

  }
|improve this answer|||||
0

I made it working using SSJS and Rest, thanks Gortonington, for your articles https://gortonington.com/how-to-interact-with-query-activities-via-rest-api/ and https://gortonington.com/restapimethodinssjs/

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.