Ghost Table Truncation with Stored Procedures in Alteryx?

[lhornstra]

Hi All,

This is going to be hard to explain, but here goes. My team has been using Alteryx to run stored procedures using Scheduler (not the server version though). As you probably know, in order to execute a stored procedure that returns no data and requires no parameters can only be done with the Pre/Post SQL statement in the Input node. So, in the Pre SQL I simply have "EXEC sp_SPName", the SP will truncate the table, then update it. Then the query portion in the Input node has "select max(date) from tablename" to return what the most recent date in said table would be to ensure table has properly updated. However, once the workflow is finished and I verify the table is updated with a select statement, a few hours later someone will say to me, "the table is empty?!" and sure enough--it's empty! There have been no other transactions to these tables I update and no other tables have this issue that are not updated via Alteryx. 

So my question is--does Alteryx "initialize" these input nodes such that it quickly tests connectivity, parsing queries, etc., and runs the first portion of my SP that is "TRUNCATE TABLE"? I've tried everything, looked all over the community, tested and retested. I can't come up with anything else.

Any thoughts would be much appreciated!!

LH

[s_pichaipillai]

Ok. Can you try the below
1. Can you use the OLEDB instead ODBC
2. Can you modify your SP not to use temp table, you can build some physical tables OR use CTE (i am suspecting the temp tables are loosing connections)
If the above not helping
3.can you use WAITFOR command in select MAX query for some mins to see whether this max query being executed before the pre sql
 
Thanks
Sar

[lhornstra]

I thought OLEDB is going away? I can try that but I'd rather continue to use ODBC.

I prefer temp tables over CTE's for indexes and they generally run quicker & I don't want to muddle up the database with physical staging tables.

I'll try the waitfor...I'm also going to test adding a statement before the truncate to see if it's just hitting the first line of the SP when I open the workflow. That seems to be when the truncates are happening. 

I do appreciate your ideas!

[lhornstra · Accepted solution]

So here's my final solution:

I added a datetime criteria in the stored procedure to check to see if it was between the hours of runtime and if it is to execute the store procedure, otherwise don't execute. I opened the workflow, then checked the data and it was still there. 

Clearly, Alteryx is doing some sort of initialization and parsing the query causing the stored procedure to kick off without ever running the workflow. I believe this to be a bug.

Thanks for everyones help.

[AdamR_AYX]

Hi LH,

Sorry I'm a bit late to this thread.  The short answer is that it is expected behavior for the pre-SQL in the input tool to run when the workflow is opened or the configuration updated.

The reason for this is that the input query has to be sent to the database to retrieve the returned columns so that the rest of the workflow can be configured.  As far as the input tool is aware, that pre-SQL is part of the query and is run when it is querying the DB for the column information.  Which I *think* explains the behavior you have being seeing.

I'm pleased you have found a solution that works for you.

The real solution for running stored procs really is a new SQL tool as per this idea here:

http://community.alteryx.com/t5/Alteryx-Product-Ideas/Have-an-SQL-Tool/idi-p/5392

Please up vote this idea and add any additional comments you might have.

[chris_love]

Really Adam, how did I not know that? Thanks - can you clarify this isn't the same for the Output tool?

[LindaT]

It is not the same for the Output Tool.  The Output Tool doesn't require metadata and is only called when actually running a module.

[lhornstra]

Adam, thank you for enlightening us all!

I will most definitely vote for your suggestion.

Thanks again,

LH