Input Tool Not Displaying Stored Procedure for SQL Server Connections

[JLinAltX]

Issue

 

The Input Tool's Choose Table or Specify Query window does not display the stored procedures for SQL Server connections.  The Stored Procedure tab is empty.  The issue occurs regardless of using Quick Connect or ODBC.
 

 

You may also see this error message:

   Stored Procedures are not supported for this type of connection.

 

Environment Details

 

• Alteryx Designer
  • All
• ODBC Driver for SQL Server
  • Version 17

Cause

 

The issue may arise when using an unsupported ODBC Driver for SQL Server. The supported ODBC Driver for SQL Server can be found in the Designer Help Document under Microsoft SQL Server 2012, 2014, 2016

Resolution

The key to this issue is the supported ODBC drivers.  The most recent supported ODBC drivers for SQL Server can be found in the Designer Help Document under Microsoft SQL Server 2012, 2014, 2016.  Please install a supported driver from the list. 

Once you have the supported ODBC driver installed, you have 3 options.

Solution A - Quick Connect

When using the Quick Connect option to connect to SQL Server, it generates a DSN-less connection string referencing the latest ODBC driver you have installed.  If the latest ODBC driver is not a supported driver, it will still refer to it.  We can default the Quick Connect connection to a supported driver by editing either the UserAlias.xml or the SystemAlias.xml, depending on whether you created the connections under User or System.



1. If creating a System connection, open the SystemAlias.xml from:

C:\ProgramData\Alteryx\Engine\SystemAlias.xml

2. If creating a User connection, open the UserAlias.xml from:

C:\Users\<your yuser>\AppData\Roaming\Alteryx\Engine\UserAlias.xml

Open the SystemAlias.xml or UserAlias.xml and look for the connection entry with the issue.


For example,
   <Connection>odbc:DRIVER={ODBC Driver 17 for SQL Server};DATABASE=;SERVER=AYX\SQLEXPRESS;Trusted_Connection=yes</Connection>

Currently, ODBC 17 is not supported. If ODBC Driver 13 is installed, change from the entry ODBC Driver 17 to ODBC Driver 13 to get what we see below.  

   <Connection>odbc:DRIVER={ODBC Driver 13 for SQL Server};DATABASE=;SERVER=AYX\SQLEXPRESS;Trusted_Connection=yes</Connection>

Save the .XML and the Quick Connect connection will default to the ODBC driver set.  Repeat this process when creating new connections via the Quick Connect feature.
 

Solution B - Create a new DSN-less Connection

Create a new DNS-less connection from Managed Data Connections and use that instead.  Open up Designer and go to Options | Advanced Options | Manage Data Connections.  From the Add Connections drop-down menu, choose Other.  From the Other Database Connection screen, paste in the DNS-less connection string from either the SystemAlias.xml or UserAlias.xml.  Make sure the connection string is referencing a supported driver.

Going with the example in this article, you would copy and paste the following only:

   odbc:DRIVER={ODBC 13 for SQL Server};DATABASE=;SERVER=AYX\SQLEXPRESS;Trusted_Connection=yes
 

Solution C - Create a New ODBC DSN

If preferred, simply create an ODBC DSN connection using the support ODBC driver.  Please refer to the Alteryx Designer Help, ODBC, and OLEDB Database Connections.

Additional Resources

 

• How To: Connect to an ODBC Data Source

[ACharChar]

Afternoon, @JLinAltX. I'm still receiving the following message "stored procedures are not supported...." after changing the driver to the following: MSOLEDBSQL. Is there any additional configuration steps that can be provided? This is for SQL Server.