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.
Solved! Go to Solution.
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.
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.
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.
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.
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.
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.
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?
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.