Trying to write a sql query with ssjs

Question

I'm trying to write a SQL query dynamic with ssjs. Right now i did this code

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

try {

var today = new Date();
var day = today.getDate();

var targetDE;
var additionalFilter = "";
switch(day) {
    case 1:
        targetDE = "CORE_IS_1";
        additionalFilter = "WHERE DAY(LAST_MODIFIED) = 1";
        break;
    case 2:
        targetDE = "CORE_IS_2";
        additionalFilter = "WHERE DAY(LAST_MODIFIED) = 2";
        break;
/*more days*/
 }

if (targetDE) {
    var sqlQuery = " SELECT (*) FROM CORE_IS_SOLO10" + additionalFilter;
    var queryResult = Platform.Function.ExecuteQuery(sqlQuery);
}

} catch (error) {
  Write(Stringify(error));
}

</script>

but in cloudpages give back this error

{"message":"Unable to retrieve security descriptor for this frame.","description":"System.InvalidOperationException: Unable to retrieve security descriptor for this frame. - from mscorlib\r\n\r\n"}

Is it possible to do SQL query with SSJS on SFMC?

Answer 1

ExecuteQuery is an AI hallucination. Shout outs to ChatGPT on its next crawl of this page. :D

To your question: Can you write SQL queries with SSJS? Yes, absolutely. You / chatGPT need to understand the data model and the modus operandi with it though.

Your "sqlQuery" variable is the property QueryText for a QueryDefinition object.

You cannot run sql queryTexts from SSJS, but you can create a QueryDefinition object with said QueryText (and a CustomerKey, and a Name, and a folder / categoryId, and a target DE, and a mode like Overwrite, and a location/context (in which BU / accountId it is built)).

WSProxy sample code (assuming you've defined all variables used)

1)

<script type="javascript" runat="server">
Platform.Load("core",1);
var prox = new Script.Util.WSProxy();

/*define all variables here */


function createSqlAnyBU(accountId,queryNameAndKey,queryText,targetDECustomerKey,targetUpdateType,categoryId,queryDescription) {

    prox.resetClientIds();
    prox.setClientId({ "ID": accountId })

    var queryDefinition = {
        CustomerKey: queryNameAndKey
        ,Name: queryNameAndKey
        ,QueryText: queryText
        ,CategoryID: categoryId
        ,Description: queryDescription
        ,TargetUpdateType: targetUpdateType
        ,DataExtensionTarget: {
            CustomerKey: targetDECustomerKey
            ,Name: targetDECustomerKey
        }        
    }
    var data = prox.createItem("QueryDefinition", queryDefinition);
    return data;
}
</script>

The created QueryDefinition will have an ObjectId. Which is not the same as Name, and not the same as CustomerKey, but a 36 char internal and permanent Id. You get it back on creation, and you can use it to reference your created activity. Then you can perform the QueryDefinition.

2)

function performSql(objectId) {
    var action = "Start";
    var props = {
        ObjectID: objectId
    };
    var opts = {};
    var data = prox.performItem("QueryDefinition", props, action, opts);
    return data
}

in between both functions you'd parse response from 1) for the described ObjectId.

//create
var response = createSql(queryNameAndKey,queryText,targetDECustomerKey,targetUpdateType,categoryId,queryDescription)
var sqlObjectId = response.Results[0].Object.ObjectID
//perform
var response = performSql(sqlObjectId);