on 04-14-2016 03:04 PM - edited on 12-29-2021 06:27 AM by MichaelAd
How To: Connect to an ODBC Data Source
Alteryx can connect to databases using different methods. This guide will focus on setting up an ODBC connection to SQL server, however, the same general process can be followed to connect to any database via ODBC.
Prerequisites
ODBC driver for the database (if it is not currently present on your machine, you must download and install it prior to using this guide. The download links to many of the drivers can be found here. If you are unsure of where to get the driver or which driver to use, please contact your IT or database admin for this information). For Designer 2021.3+, a supported 64-bit driver is required; see also here.
User and System DSN (Data Source Name)
Before proceeding, it is important to decide whether to use a System or a User DSN. A DSN is a symbolic name that represents the ODBC connection. It stores connection details like database name, directory, User ID, password etc. and is utilized when making a connection to an ODBC data source. Please refer to this Microsoft article for further information.
There are various types of ODBC data source types.
Both types of DSNs can be configured via the ODBC Admin app.
Please note that Alteryx Designer / Server do not support File DSNs.
2019.2 and later
Before 2019.2
All Versions
Hello Team,
Can you provide me a details on how we connect ODBC to Oracle.
The Example provide is of Microsoft SQL Server.
I want to connect to ODBC with Oracle (64 Bit)
Thanks in Advance.
I am having many issues connecting to ODBC data sources.
Any advice?
I am not getting ODBC 64 bit driver.
Well explained.
64 bit driver is a different file download. Originally I was trying to use the 32 bit and had to go back.
Hi, I am a new user with Alteryx and needed some advice on connecting to Hive tables is Hadoop.
it would be great if I could get some information on how do I start, thanks
@olimpio
Your go-to resources on how to connect to databases is this list of Data Sources and the help for Input Data tool. Many of the Data Sources have articles in Community, and Hadoop is no exception. If you're new to Alteryx this article on how to search Community might be helpful to you.
Hello,
Im trying to connect with mysql and was able to download and install odbc driver for mysql and created odbc data source successfully.
In alteryx also i was able to use Input Tool to connect using the above odbc data source and get the list of tables in "Visual Query Builder", but when i switched to "SQL Editor" to test the query it giving below error "No Columns Returned" although there are data in the table.
did anyone got this issue ? help requested.
Thank you,
Danish
@danishimam , Are you trying to connect to system tables/resources? I have found even in SQL Server ODBC not all objects are available. i.e. I cannot access system stored procedures to get user lists from the db, only application built procedures are available, nothing from the System Stored Procedures objects.
So, even if you have access from testing in other tools, if you type the command in SQL Editor box to access them, Alteryx just responds with No Columns Returned as it cannot find the objects. Note in the visual query builder whether or not you can see them.
@nbrenner, i was trying to connect to system table. I was doing this for one of the POC but we didnt went that path and so no longer needed solution. but not sure why alteryx is behaving that way 😔
In case it is needed again by anyone in the future, I am informed that sys stored procedures are not returned within Alteryx by design, but there is a workaround. If you add in the metadata (column names, etc) expected to be returned to the SQL statement in the editor it should work - it did for me! See below for an example capturing the data for sys.sp_helpuser from MS SQL Server.
There is a known defect (TDCB-3694): sometime when using ODBC connection the Stored Procedure throws "No Columns Returned" error, but OLEDB does not.
Refer this Knowledge Base article .
Defect TDCB-3694 is still open. I have linked this case to the defect. We will notify you of any updates we receive from our development team.
The workaround is to add the metadata at the end of the stored procedure defining the result set when calling the stored procedure.
I tested the workaround using using ODBC connection and system stored procedure sys.sp_helpuser with below statement and it works:
EXEC sys.sp_helpuser
with result sets (
(UserName sysname,
RoleName sysname,
LoginName sysname,
DefDBName sysname,
DefSchemaName sysname,
UserID smallint,
SID smallint)
);
Please apply that workaround.
Thanks to Nora Tobon from the support team on assisting with my ticket!