1

I am trying to update a query definition in Marketing cloud using SSJS script which updates the query based on the datestamp. When I run the script, I don't get any error but it does not seem to be updating the query definition for some reason. The script is below:

<script language="javascript" runat="server">
    Platform.Load("core", "1");
var qd = QueryDefinition.Init("37af1441-0b0c-4f99-b8ac-ac511da5ff2c");
var today = new Date();
var dd = today.getDate()+1;

var mm = today.getMonth()+1; 
var yyyy = today.getFullYear();
var year = yyyy.toString();
var yy = year.substring(2);
var send_date = (today.getMonth() + 1) + "-" + (today.getDate() + 1) + "-" + yy;
var str1 = "]";
var dename = "10-ControlGroup-MyWeekend-" + yyyy + mm + dd + str1;
var sql = "Select A.EmailAddress as Email, 2 as [Campaign ID], 1 as [Control Group],'" + send_date + " 12:00:00 AM' as [Send Date], B.[Janrain ID] as [Janrain ID] FROM [" + dename + " A INNER JOIN [Subscribers] B on A.EmailAddress = B.EmailAddress" ; 
var status = qd.Update({
    Name : "Control Group - Query to Populate MyWeekend History Log - UPDATED",

    QueryText : sql
    });
qd.Perform();
</script>

Would anyone know why it might not be working as expected? Thanks for your help in advance.

Cheers, Gaurav

  • Is there any updates at all to the query, or is it exactly the same regardless of what you put in your script? Have you output your variables and Qry text in a cloudpage to verify they are correctly formed? Have you output the returned status code from your update function? Please provide more info and we should be able to provide better debugging help. – Gortonington May 24 at 13:09
  • Hi @Gortonington, thanks for your reply. I just outputted the variable sql in Cloudpages and can verify it is correct! And answer to your first question, there is no updates at all to the query definition. How to return the status code from the update function? I have not done this before, can you please advise? – gds945 May 27 at 6:16
  • You just output the var you used to declare the update (status). So something like Write(Stringify(status)) – Gortonington May 27 at 11:32
  • Hi @Gortonington, thanks for that. I am getting "error" in cloudpages. But there is no additional details. Do you know what kind of error this might be? Thanks for your help. – gds945 May 28 at 0:26
  • 1
    @Gortonington, thanks the code is working now. I did not realise earlier that the variable was only pulling as single digit '5' whereas the name of the DE had double digits '05" which I have fixed and the code is working as intended now. Thanks for your help suggestions, it helped me to troubleshoot the issue. Cheers! – gds945 May 30 at 23:29

Your Answer

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

Browse other questions tagged or ask your own question.