Troubleshooting Database Connections
Alteryx is working diligently and intelligently on making connections to databases easier, more secure, and faster than ever. Even when we can make the best possible experience for our users come to fruition, Users may still encounter some issues due to the use of third-party drivers and/or the intricate communications between the database, driver, and Alteryx. To help to minimize those issues, Alteryx has tested with an abundance of "supported data sources" and drivers that are proven to be compatible but, unfortunately, it's not possible to test with every database and driver out there. Our Data Sources (Help page found here) will show you those databases Alteryx supports, as well as the drivers we have tested and have support for. This doesn't mean Alteryx cannot connect to a database or driver not listed on the page; it's simply not supported, as the database/drivers have not been tested with the application
This is a short overview aimed at helping you understand possible resolutions to your database connections. Believe it or not, the majority of support cases we receive regarding supported database issues are actually from the configuration of the driver or an issue with the database. Alteryx simply reads what the driver passes to it. It isn’t translating the data like the driver is, and it also isn’t trying to read your database, get past security settings, authenticate, or find the tables. That's why we often see so many support cases involving databases stemming from a driver misconfiguration or an underlying issue/error coming from a database. Keeping that in mind as you troubleshoot, an error you receive in Alteryx can frequently end up being communicated directly from your driver or your database.
That said, It is important for users to understand what drivers do and what they can connect to in order to help circumvent issues that may stem from them. ODBC drivers are the most commonly used drivers and can have many variations depending on the software or database need.
ODBC Drivers
“ODBC is based on the device driver model, where the driver encapsulates the logic needed to convert a standard set of commands and functions into the specific calls required by the underlying system. For instance, a printer driver presents a standard set of printing commands, the API, to applications using the printing system. Calls made to those APIs are converted by the driver into the format used by the actual hardware, say PostScript or PCL.
In the case of ODBC, the drivers encapsulate many functions that can be broken down into several broad categories. One set of functions is primarily concerned with finding, connecting to, and disconnecting from the DBMS (Database Management System) that drivers talk to. A second set is used to send SQL commands from the ODBC system to the DBMS, converting or interpreting any commands that are not supported internally. For instance, a DBMS that does not support cursors can emulate this functionality in the driver. Finally, another set of commands, mostly internal, is used to convert data from the DBMS's internal formats to a set of standardized ODBC formats, which are based on the C language formats.
An ODBC driver enables an ODBC-compliant application to use a data source, normally a DBMS. Some non-DBMS drivers exist, for such data sources as CSV files, by implementing a small DBMS inside the driver itself. ODBC drivers exist for most DBMSs, including Oracle, PostgreSQL, MySQL, Microsoft SQL Server (but not for the Compact, aka CE edition),Sybase ASE,SAP HANA, and DB2. Because different technologies have different capabilities, most ODBC drivers do not implement all functionality defined in the ODBC standard. Some drivers also offer extra functionality not defined by the standard.
Please note: Alteryx does not support JDBC drivers.
From Designer / Server 2021.3 only 64-bit ODBC driver versions are supported.
Common Driver Errors
- Depending on the driver you have installed, you will see that the error will have the driver name or type in the error message.
- This is another common oracle driver error that relates to driver setup and configuration, specifically referencing the tnsnames.ora file rather than an Alteryx issue.
Database Errors
Just like the driver message, if you are seeing your database mentioned in the error, the first thing to do is to search for that error on the internet. In most cases, you will also be able to see what the error is related to and how to fix it!
Oracle:
Hive:
Most of these errors can be found on the internet, usually in the database vendors' support forums, and can give you some direction as to where to look to resolve the issue. If, after you have researched the error and attempted to troubleshoot with your database administrator, you still have an issue, please waste no time posting your issue to our Community or reaching out to our Support Team for further assistance! If you do post on the Community or contact Support, please also include the version and type of the database you are using, driver version, and Alteryx version to help us accelerate your time to resolution. If possible, contact your DB admin and ask for your DB logs if they're available as well. This will give us the requisite information to get you up and running as quickly as possible!
Please see the links below for more specific troubleshooting and setup instructions: