3

I built a Microsite which staff can you use to run one-off queries without having to create an activity and then run it or place it in an automation. It works great but is very basic - when you click Submit it updates a query activity with the text, target data extension and type you gave it, and then runs it. If you do it right, the next screen reports 'OK'. If you did something wrong it just says 'Error'. I'd really like to be able to report on this page what went wrong with the query. Here's a fragment of my current code:

<script runat=server>
Platform.Load("Core","1");
var qd = QueryDefinition.Init("RunQuery");
var target = Variable.GetValue("@target");
var type = Variable.GetValue("@type");
var qdef = Variable.GetValue("@definition");
var status = qd.Update({
    Name : "RunQuery",
TargetUpdateType : (type),
TargetType : "DE",
Target : {
    Name : (target),
    CustomerKey : (target)
},
    QueryText : (qdef)
    });

qd.Perform();
Write("Status: " + status)
Variable.SetValue("@status",status);
</script>

This successfully prints Status: OK or Status: Error. If it's an error, can I get it to print the reason why it failed? For example if you run a query in an automation and it fails it will say things like Error: Conversion failed converting 'xxx' to data type int' or 'Primary Key Violation'. Can I print that in SSJS?

  • 2
    There is no way via the SSJS function to get any further details. You could try utilizing the API to build/update your query, etc. to get more details than the SSJS function, but even that will be sparse. In August release they are adding more robust error details in general, but likely it will be limited to the UI only. – Gortonington Jul 25 '19 at 14:17
  • why dont you trying using Try and catch block, it can give you the detailed exception message. But, im not sure if you will see the appropriate explanation of the error. – Abhishek Jul 26 '19 at 12:04
0

Well, recently Salesforce released Query Studio, a free addon on AppExchange. It allows you to validate queries in an interface that explains what went wrong. It's rather new but feedback seems good, and they may expand it.

That said, it seems what you're doing is basically building workarounds for less/non-technical users. Why not instead consider a drag-and-drop segmentation tool like DESelect instead of instructing SQL?

| 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.