Workflow with Dynamic Input Doesn't work completely on the server

[BMB]

Hi every one

I am trying to run a WF with InputDynamic Tool which is attached with Input Text tool contains all all the tables with their schemas.

The dynamic tool has connection with SQL Server to run a specific Stored Procedure that reads tables from linked server (TrakCare) and then insert on the SQL Server 

Spoiler

The problem is when I run the WF on the Designer everything works fine but,

when I run it on the server it runs for short time and shows successful But none of the tables are inserted

[RishiK]

@BMB it looks like the SQL is getting updated but not running on the database itself. What does the workflow log say after running on the Server? I assume other workflows that update the database via the Server work fine - from a data connection perspective?

[BMB]

hello @RishiK

This is what the server log say :

Just to be Known, All the selection and insertion process done by SQL Server. Alteryx WF only call the stored procedure  

[RishiK]

@BMB  it looks like that the proc is not running on SQL Server here.

Can you try running this with 1 table and see what happens?

[BMB]

@RishiK I tried that and nothing change

Also, I tried to run the proc from the SQL Server and it's working fine.

As I mentioned in the first topic when I run it in the Designer everything works fine it load all the 1127 tables to the destination and took around 16 hours to finish. But when I run in the server it runs for few seconds and shows successful. 

But none of tables are inserted. 

[RishiK]

@BMB I have been thinking about this. I think its a permissions issue between the Server user and the database.  There are 2 things you can do to test this.

1. Can you run the workflow on the Designer on the Server machine to see what happens.

2. You can run the workflow "Run As" on Server with your username, can you see what happens when you do this on Server?

[danilang]

Hi @BMB 

I think the key here may be the "ended by a downstream tool" message in your output log

I've seen this kind of error before when the output of a tool is not used.  In your case the output of the Dynamic Input is not connected to any other tool.  When this happens the Alteryx engine can sometimes "decide" that only the first record in from the input needs to be executed, since that's enough to generate the downstream metadata. 

This sounds overly simplistic, but try adding a Browse tool after the Dynamic Input.  This may be enough to get all the rows executed.  If not add a Sort on one of the fields returned from the query followed by a browse

Dan