Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Create an In-Database Connection

AliS
Alteryx Alumni (Retired)
Created

How To: Create an In-Database (In-DB) Connection

 

Apart from making standard database connections, which allow for data to be pulled from the database directly into Alteryx, Designer also has In-Database functionality. The In-Database tools allow for the workflow created to be converted into a query which will then be executed on the database, and not locally in Designer.

 

 

Prerequisites

 

  • Alteryx Designer
  • Windows Operating System

 

Procedure

 

  1. In Designer, drag and drop the Connect In-DB tool (located in the In-Database tool palette) onto the canvas
  2. Select the “Manage Connections…” option to establish a new In-DB connection
  3. manage connections indb.png
  4. The Manage In-DB Connections window should appear. To configure this window:
    1. Select the Data Source/Database type from the Data Source drop-down.
    2. Under Connection Type, select User, System or File connection.
      1. For file connections, see this article: How To: Create an Alteryx In-DB Connection File
      2. On the server, make sure to select a System Connection. If you do not see the System option available, see this article: Unable to create System Connection/System Connection type missing. For more information about In-DB workflows on server, see this article: How to run In-DB workflows on server without using a connection file
    3. Under Connections, select New.
    4. Under Connection Name, enter a name for this connection.
    5. Under Driver, select the method of connection to use.
    6. Click the Connection String drop-down. You can choose to use an existing connection or create a new connection by clicking the New database Connection…option. You can also paste in the full connection string if you have it available.
    7. Repeat steps 5 and 6 for the Write tab.
    8. Click OK. If the connection was successful, the Choose Table or Specify Querywindow should appear.
    9. create indb connection.png
  5. The connection will now be stored in the Connection Name drop-down.
  6. saved manage connection indb.png

 

 

Additional Resources

 

 

Comments
cdgogan
5 - Atom

Can In-Database connections be configured in Gallery to support uploading and scheduling of your In-Database workflow on Alteryx Server?

CraigAustin
6 - Meteoroid

Do you have instructions for connecting In DB to Oracle DB?  I'll keep looking for those instructions in the Community.

DanM
Alteryx Community Team
Alteryx Community Team

@CraigAustin

 

You can check out the Help for the In-D tools for instructions on how to connect to database. https://help.alteryx.com/2018.1/index.htm#cshid=LockInInput.htm. Every database may be setup slightly different, so you may need your database Admin to assist you with the setup. 

DanM
Alteryx Community Team
Alteryx Community Team

@CraigAustin the section you are asking is something that you will have to know based on the connection received from your database Admin. A 'user' connection is a connection that you can only use and would be specific to your machine. If you are planning on sharing the workflow that you are using this connection in; you will want to use a system connection, which can be shared across machines. This is something that your database Admin will have to assist you with as Alteryx does not provide this connection information. If you are still having issues after speaking with your Database Admin please feel free to reach out to support@alteryx.com for assistance.

 

Thanks

karaclayton
5 - Atom

Can the connection strings you create be deleted? For example, some failed attempts still show up and would like to clean it up. Thanks!

AliS
Alteryx Alumni (Retired)

@karaclayton, Yes, Connections can be deleted in the "Manage In-DB Connections" option in the "Connect In-DB" tool. You'll see a "delete" button next to create new data connections. 

karaclayton
5 - Atom

Hi @AliS, I meant the connection string options for ODBC. I've attached a screenshot. Even when I've deleted the connections, the connection strings from past attempts are still listed.

Connection String Alteryx.PNG

iamnotarobot
5 - Atom

Worked like a charm! Thanks so much for posting this solution!