Check for Errors

Question

I have a list of SQL statements. I have a batch macro where i run the queries 1 by 1. I know and am expecting some of them to be wrong (due to system changes) and i am looking for a way i can get the macro to attempt the query and report if it works or not. i am currently using dynamic input in the macro and have it configured to run all iterations before output.

dataguyW · Answer

@JTCairns  wondering if this helped get it running for you?

dataguyW · Answer

Yes, this does in fact work.   I had opened a discussion on "Proactive Connection Testing" a while ago and got something close, but never finished the sample.   This is tricky because in order to do this with standard tools + CReW you have to do this in a few steps.  I created this as a quick sample so assumed the same data source etc.  You could obviously tweak all of that.  In my input below, Row 3 tries to pull a column that doesn't exist from a table.

Summary is you have an input containing your SQL queries, run each row through a batch macro that writes the SQL to a FILE/INPUT of your choice and then runs the CReW CList Runner with log file parsers attached and massage your output.     You can grab what you need in terms of output, I just did a minimum sample here to show that it runs all SQL whether or not they succeed or error.   I'm sure there are more elegant ways to achieve this, but this is quick.

dataguyW · Answer

Alteryx tends to directly error when an error is reached from within a workflow.  This is the default nature of the code and those errors are logged in the results and tend to stop the code.    There are ways you can execute code that do not worry if your alteryx code succeeds or fails by using a CReW Conditional Runner or Runner.   Both of those tools have an output anchor for success and one for failure.    The failure anchor will contain the logs of the macro run which can be parsed out to determine if there was going to be an error.   This requires some nesting of macros.   I looked for my example of this.