SQL Server - SQL Editor Test Query Failed With "Error opening <query>: No Columns Returned."

[JLinAltX]

Error: “Error opening <query> : No Columns Returned."

 

Environment Details

 

For a SQL Server connection, the Test Query in the SQL Editor is returning with message "Error opening <query>: No Columns Returned.

 

Error opening <query>: No Columns Returned

 

 

• Alteryx Designer
• Input Tool | SQL Editor | Test Query
• SQL Server DSN-Less or DSN

Cause

 

If the SQL Server DSN-Less connection string or DSN does not include a default database, the SQL Editor does not know which database to validate the query against.

Diagnosis

 

If you do not wish to include a default database in either the DSN-Less connection string or the DSN, go to Solution A.  If you want to step a default database go to Solution B.

 

Resolution

 

Solution A - Qualify the Table Call in the SQL With the Database Name and Schema That the Table Belongs Too

 

1. Paste your query into the SQL Editor
2. For all tables being referenced in the query, qualify it with the database name and the schema the tables belong to.
   • For example If you have "SELECT * FROM Department", you will change it to "SELECT AdventureWorks2017.HumanResources.Department.* FROM AdventureWorks2017.HumanResources.Department".

 

Solution B - Specify a Default Database in the DSN-Less Connection String or DSN

 

1. If using a DSN-Less connection string make sure you include DATABASE= parameter.
   • For example,  odbc:Driver={SQL Server Native Client 11.0};Server=ayx.extendthereach.com\sql;Database=AdventureWorks2017;Uid=testdummy;Pwd=__EncPwd1__;Trusted_Connection=yes
2. If creating a DSN, check the box "Change the default database to" and enter the database name

Additional Resources

 

• How to format common ODBC DSN-less connection strings