Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Input Tool Not Displaying Stored Procedure for SQL Server Connections

JLinAltX
Alteryx
Alteryx
Created

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.
 

 image.png
You may also see this error message:

   Stored Procedures are not supported for this type of connection.

image.png
 

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.

image.png

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
 

image.png



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

 
No ratings