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

[chris_love]

Ive never seen this behaviour. One interesting thing to try would be a
"COMMIT" in the post SQL. This would check that Alteryx isn't simply
processing a single uncommitted transaction.

[lhornstra]

Chris_love - that's a great suggestion, one I hadn't though of... however I question its ability to work well as these truncations happen completely at random (so it seems). Some days there is no truncation, some days there is. Some times it's one table, some times it's all tables. No rhyme nor reason.

[chris_love]

I can't see how Alteryx can be at fault. You would need to monitor profiler
to be sure. Make sure sessions are closed after the scheduler has completed.

I can appreciate the problems finding the cause though. The only way is
taking the slow scientific approach and trying to rule one thing out at a
time.

[lhornstra]

If I was a sys admin on this DB, I wouldn't be using Alteryx at all to schedule my stored procedures. 

[s_pichaipillai]

Hi LH,

can you post the SQL queries that you are trying to execute ?

[lhornstra]

s_pichaipillai -- they are excessively long stored procedures and for proprietary reasons, I can't post. I'm still sure it's not the stored procedure. Here is what my input configuration looks like:

 

The only reason I thought to post this question was because I had just received the notification that this workflow finished running from scheduler. I did a simple select statement on this table to see the results. Then someone had thought the data was outdated, so I opened up the workflow to see if there were errors and to see if it had truly run--which it did without error. When I told that person it was up to date, she then ran a query against it and the table was magically empty. So that's why I had the thought that perhaps there was an initilization when opening a workflow, specifically against SQL tables. While it makes no sense, I'm not sure why else this would happen and only happen when I opened the workflow.  

Sorry I can't provide more information.

[lhornstra]

And what do you know--the table that is updated from the workflow that I just opened to take a screen shot of is empty. 

EDIT: I tested this again.  I checked if one table was empty--wasn't. Then I opened the workflow that updates it in Alteryx... did nothing in the workflow but open it.

Then I checked the table AND IT'S EMPTY. 

[s_pichaipillai]

not a problem
Question
which database are you using sql server, oracle..
what does the SP actually do, truncate the subscriberdetail table then do some updates
trying to understand of your workflow logic bit more
thanks

[lhornstra]

SQL Server 2008

The SP begins by truncating the table. Then it inserts data by way of many temp tables pulling from our base data. Then it drops the #temp tables once the data has been written. 

So workflow is this:

PreSql runs SP to truncate table, build temp tables, load data into table --> Select max date from updatedtable --> check to see if this date is as of yesterdays date --> yes: send success message/no: send error message