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.

Troubleshooting Database Connections

DanM
Alteryx Community Team
Alteryx Community Team
Created

Troubleshooting Database Connections


Alteryx are 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 aims help understand and 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 driver 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 talks 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 OraclePostgreSQLMySQLMicrosoft SQL Server(but not for the Compact aka CE edition),Sybase ASE,SAP HANA,andDB2. 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:

image.png

If you see your Driver in the error message, the troubleshooting should start there. You may see that an Alteryx tool is referenced in the error, but more than likely it is the Input Data Tool , which relays the error message directly from the driver.
The driver error and suggested solutions can then be found doing a quick internet search on the error text!
 
  • This is other common oracle driver error which relates to driver setup and configuration, specifically referencing tnsnames.ora file rather than an Alteryx issue. 
image.png


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 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:

image.png

 

Hive:

image.png

 

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:



Additional Resources

Comments
SeanAdams
17 - Castor
17 - Castor

Great article Dan - is it worth also giving folk a sense of how to troubleshoot strange errors to see if it's a query error or a driver error?

 

I'm thinking something along the lines of:

Step 1: is this a query issue?

- Can you run this same query through a direct query window or through some other connection to the DB (e.g. through Excel or Access)?

- if not, then this is likely to be a query issue (step through to "debugging a query")

- If yes, then we may have a connectivity issue

Step 2: Can you connect to anything on this db?

- When you open the input data - can you see the list of tables coming up?

- can you run simple queries (like selecting from a table with no filters)?

 

etc...

 

Reason for asking this is that I sometimes see people who believe they have a driver or a connectivity error, but sometimes it's just an error in a query?

 

Thanks for a great article Dan

Sean

Matt_S
7 - Meteor

I'm (attempting to) using the Connect In-DB tool to access my SAP BW system. I have a mostly successful connection; however, I cannot readily access fields that violate SQL four part rules.

 

eg. GLMATPLNT is available to select but GLMATPLNT.GLAPLFZ is not as the field name can't/shouldn't have multiple parts.

 

I know my system is unique and these field names are specific to only me, but is there a known way to bypass the SQL restriction?

royhampton
6 - Meteoroid
Wonder if you can provide some guidance for me interpreting an error I receive when trying to connect Alteryx to a Greenplum postgresql database, using an ODBC connector. I'm using a simple input tool and select my greenplum data source. All schemas in the database are shown but when i try to test a simple query i receive this error: Error SQLExecute: ERROR: permission denied for relation geometry_columns; Error while executing the query I can run the same query using the PGAdmin III tool - so i dont think its a permissions issue and my internal IT/DBA support indicates that I have the correct permissions.
blyons
11 - Bolide

I was hoping this would help me solve problems I am having connecting to spatial data in SQL Server, however when I click on the link, I get:

blyons_0-1577147384209.png

DanM
Alteryx Community Team
Alteryx Community Team

@blyons,

 

Sorry! Old link. I removed it.

 

You should be able to access that data through the drivers you install. As long as you have access to the data Alteryx should be able to pull it in without any other hoops to jump through. Alteryx can also connect to an ESRI GeoDatabase as well.

 

I would just make sure you have the supported drivers installed. Drivers