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

Can you execute generic SQL commands?

dustin
5 - Atom

Hi, I'm searching for a way to simply execute generic SQL commands against a server.  Specifically, I'd like to execute some stored procedures on the server after loading data into staging tables using alteryx. 

I know you can use custom SQL as part of data input connectors, but I'm just looking for a way to run other SQL statements against a server such as exec and delete statements, etc.

26 REPLIES 26
RodL
Alteryx Alumni (Retired)

And just to be clear on what is happening, the "drop" of the "all records" table would actually occur at the "beginning" of the Output tool (which would happen before any records from your macro start being inserted into the "all records" table.

travist6983
6 - Meteoroid

Hello MargaritaW - i know this is an old ticket but i am trying to do what you suggested and maybe my version of Alteryx is different than when this was written but I dont see Post-SQL Statment. I see Post Create SQL Statement which i am assuming only works when you are creating a table which i am using append to existing. 

 

What would be the best way to execute this SQL block 

 

DECLARE 
  n_SL_ID number;
BEGIN
  select max(sl_id) into n_SL_ID from source_log where sj_id = (select sj_id from source_job sj where sj.sj_code = 'GOOGLEANALYTICS');
  wa_di_pkg.ga_post_process(n_SL_ID);
END;

 

Or how could I set a variable to be passed into my procedure and make the block simplier so it would be something like this...

 

begin
  wa_di_pkg.ga_post_process(113)
end;

 

Where 113 would be a variable.

 

MargaritaW
Alteryx Alumni (Retired)

Hi @travist6983,

 

You are correct, the names have changed for the Post and Pre SQL to Pre Create and Post Create SQL Statement and now they are also available in the Input Data tool.

 

If your statement is in the Post Create, it will be executed via the ODBC/OleDB driver BEFORE the output table is created/data is read (if in the Output Data/Input Data tools respectively). If it is in the Post Create, it will execute AFTER.

 

One way that you could feed new values to your statement to update a value, per your example 113 would be with a control parameter (interface tools) in a batch macro. It would look like this.  

 

capture.png

 

For your reference, there is a community article that goes over batch macros at https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Batch-Macros-An-Example/ta-p/1137

 

Hope this helps.

 

 

Margarita Wilshire
Sr. Manager, Customer Support Engineering
Women & Allies Committee


Alteryx, Inc.
UpendraPandey
7 - Meteor

Hello @MargaritaW.

 

I disagree with you statement that "The Post-SQL statement runs after the output".

 

When i am running my Stored Procedure in Post SQL. My Stored procedure is getting kicked off before my Output load is done.

Here is the Screen shot for the same. Do you have any idea how i can create dependencies to execute stored proc once my Output load is done.Stored_Proc.JPG 

 

byarsmc79
7 - Meteor

I have been using both the pre and post SQL command options, but I noticed that now they do not always execute in order anymore.  i.e. the post sql statements are ran prior to the writing the output.  This causes quite an issue with my data.  Is this a known issue already?  Also, if you notice in the below example, the pre-sql is being ran even before the input data is being read.  This can cause an issue if the process is canceled.screen shot showing the output going out of orderscreen shot showing the output going out of order

Sri9
8 - Asteroid

What about if we are using In-DB tools how do we execute  Pre-SQL Statements? Like I have only RW Insert and Delete Access onto the tables by DBA. I cannot execute the TRUNCATE from In-DB anyhow. Any idea? 

ak2018
8 - Asteroid

have you tried using Dynamic input tool instead? You can replace certain strings with the arguments you want to pass. I do that for dynamic sql, but for stored procedures it can be done in similar fashion.

Labels