Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

SQL Server Connection Staying Open

justindavis
9 - Comet

At my company, we primarily work out of SQL Server, so to connect to most data requires me to input data with a database connection. Unfortunately anytime I connect to the database, I start getting long-running query/connection alerts from our IT team, and I'm not sure how to prevent that from happening. The workflows I'm creating thus far are relatively simple pulls from the database right at the start (queries lasting no more than a few seconds) so my understanding was that my work in the database was done -- but then 30 or 45 minutes later I'll get alerts and realize somehow the connection is still open.

 

Is there a way to ensure that your connection gets closed when the workflow finishes running? I already tried one recommendation on the board (Disable Auto Configure), and that hasn't prevented the connection from staying open.

3 REPLIES 3
CharlieS
17 - Castor
17 - Castor

You're not alone with this issue. The connection will stay open until the instance is closed. Not only will the connection remain open, but the connection count can be multiplied by the quantity of records going into a Dynamic Input tool.

 

I haven't tried this myself, but another suggestion I came across was to write a SQL statement to force close connections and execute it with the Post SQL statement option of the Input tool. 

SeanAdams
17 - Castor
17 - Castor

Hey @CharlieS  and @justindavis 

 

I was curious about this when Charlie mentioned it - and decided to check if this is happening to me - and the answer is no - so I'm wondering if it's a dB setup issue, or how we're connecting.

 

My test method was:

- Set up an ODBC connection to my SQL server (I have a SQL 2019 box on my home machine) from an Alteryx canvas

- Then run a simple query: Select @@SPID As SessionID, * From sys.tables

       - This brings back the process / connectionID and a list of all tables - I'm really looking for the process ID.

       - For example - I get back SPID of 61, and I can see this go through on the SQL query profiler

- When I run this - it runs sub-second - and when I check in SQL, the connection has closed.

       - You can check this fairly easily by doing an SP_Who 61 (where 61 is the process ID that alteryx reported).

 

In my case it's closing the connection as soon as the Alteryx canvas completes.

If you're seeing something different - i.e. completed canvasses still holding open connections - then I'm super curious to dig into this with you.

 

ariadnamg
5 - Atom

Hi, I'm having the same problem. Can you please tell me what did you do to solve this?

Labels