Interface Dropdown tool shows error while running from gallery.

[alt_tush]

Hi,

I am connecting my dropdown tool to external data base (SQL) I have connected dropdown list to view (View table which is created in sql).

I have used List Values as external source - Must contain NAME && VALUE fields (can be relative path).

And in properties connect the SQL Server and write a select query retrieve the data from view and populated in dropdown.

Execution of query takes 40 sec to execute, so while running the query in gallery it gives me error like :

Server Error: 500 Server Error Timed out in IboundNamedPipe::ReadFile: after 30000 milliseconds

Could you please help me on this. If there is an any alternative then please suggest.

I was stuck in this since last month and not get any solution yet

Note : I don't want to create any other table in SQL. I want to run with existing view only and view has refresh with millions of data and select query written on that view using some distinct records on particular column which gives mi output with 6-7 records which I want to show in dropdown list.

Thank you in advance. 

[JustinBabbitt]

Hello @alt_tush , 

That particular error refers to what you mentioned, the query to the SQL database you've used as a reference is taking longer than the server/load balancer is set to handle. You will need to or have someone responsible to increase the time limit to allow for the query to take place. 

Thanks, 

Justin 

[alt_tush]

Hi JustinBabbitt,

Thank you for your reply.

Yes you are right we need to set server/load balancer.But while running this query from my designer it doesn't show me error message. It executed properly by taking 40 sec to run. Same is failed while running from Alteryx gallery. I cant ask DBA to increase the time limit only for this simple query issue.

As query giving me problem while clicking on dropdown tool from gallery, Otherwise it work fine in normal execution in gallery. So my concern is, is there any way to manage this query in Alteryx Desinger only. For example : An application open first this query will run and save the data in .yxdb in temp folder then same temp folder I can use in Dropdown property to get the dropdown list from yxdb instead of directly connect to sql server to avoid this load/balance issue?

Thanks again 🙂

[JustinBabbitt]

@alt_tush 

Thank you for explaining the situation.

I would look into a couple of options: 

1. (Recommended) Develop a workflow that runs the query and saves the results (Name|Value as yxdb or txt) to an accessible network location, then use the scheduler to execute the workflow on a cadence that works for your organization. Use this file in your app, which will allow the app more to generate the dropdown tool contents instead of waiting for the SQL query.

2. Another option is similar but uses the Chained App approach where app 1 would do the heavy lifting and save the query results (similar to option 1) and app two would be the app you are using now, which will use the file created in app 1. This is a little more difficult to setup.  I'm also not sure if app 1 would encounter the timeout issue. 

Let me know how it goes!