Ghost Table Truncation with Stored Procedures in Alteryx?

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!!

Input

Database Connection

Accepted answers

lhornstra

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.

All comments

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:

Input Config

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

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

So here's my final solution:

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703