Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

PreSQL error - Trying to build a macro to allow for arbitrary SQL execution based on input

Claje
14 - Magnetar

Hi,

I'm not sure if all of this is possible or not within Alteryx.

A request came in to me today of "Could we use Alteryx to run this SQL Statement (that involves creating temp dbs) and then create an output file?"

The short answer to that was "yes absolutely, thats easy!"

The follow-up I was given is "Great! Could we run any statement we want through this?"

So, I tried to build a batch macro that might allow for something like this - I've attached what I came up with so far.


However, when I try to run this (leveraging preSQL) I get a "No Columns Returned" error on my PreSQL statement.

 

Any thoughts anyone has would be fantastic

5 REPLIES 5
MarqueeCrew
20 - Arcturus
20 - Arcturus

Sorry @Claje, but I'm confused.  I do want to help you.

 

What is going to feed the dynamic nature of the SQL generation (and pre/post SQL statements)? 

 

Have you looked at the dynamic input tool for a solution? https://help.alteryx.com/11.3/index.htm#DynamicInput.htm

 

With a little more context and information, I might be of help.

 

Cheers,
Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Claje
14 - Magnetar

Hi,

 

I seem to have sorted out my issue, thanks in part to your response causing me to take a closer look at things, although I have a follow-up question for you.


Thank you very much for the quick response!

The actual error I was encountering was because I had accidentally included part of the PRESQL create series in my main SQL query (that was creating a temp table with no output) and so Alteryx was correctly saying "Hey that piece of code didnt return columns", and I was reading the error and going "oh this must have happened in PRESQL" due to the specific SQL that was in the error message.

 

 

Lesson learned - make sure to test with a different piece of code before assuming the problem is in Alteryx :)

My follow-up question is: Do you know if the Dynamic Input route will work with Pre-SQL? My understanding is that PreSQL runs before dynamic input updates anything, but I would love to be proven wrong!

MarqueeCrew
20 - Arcturus
20 - Arcturus

Glad to have helped.  

 

In using the dynamic SQL, I wonder if you were to have multiple SQL statements if you could create something like:

 

DELETE * FROM TABLE;

 

INSERT .....

 

SELECT * ...

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Fgill
7 - Meteor

has anyone responded to this question?

Claje
14 - Magnetar

Hi,

As far as I am aware, there is no way to use only the "SQL" portion of an input or dynamic input tool to insert, select, and delete from a data source, due to the way that the drivers are configured in these tools.

If someone has an example of doing this, I am happy to hear it, but I've never encountered a method that doesn't leverage the PreSQL/PostSQL portions of the tools.

The unfortunate side effect of this is that the Dynamic Input tool cannot be used alone to modify PreSQL/PostSQL on a per query basis, so it will not work for executing arbitrary sql.

I have tested the example MarqueeCrew suggested, and encounter the error :

Error: Input Data (1): Error: No target OLEDB object available. in Query: INSERT INTO [dbo].TestTable VALUES(1,'b','c');SELECT * FROM [dbo].TestTable

 

 

Further, In testing this, I found that the main SQL statement WILL accept a "CREATE TABLE" statement, but I would not recommend using this functionality, since it will attempt to create this table every time you add a tool to your workflow, unless you specifically check the User setting "Disable Auto Configure".

Labels