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.

Error: "Cursors are not supported" or "Cursor support is not an implemented feature" when running a workflow against SQL Server or Azure Synapse

gtorres8
Alteryx Alumni (Retired)
Created

Environment Details


When running a workflow that contains queries connected to SQL Server or Azure Synapse (formerly known as Azure Data Warehouse), respectively; the following error occurs:
 

Azure Synapse


Error: Input Data (1): Error SQLExecute: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint.
 

 

SQL Server


Error: Input Data (1): Error SQLExecute: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cursors are not supported on a table which has a clustered columnstore index.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.
 

Alt SQL Server Error

 

Invalid cursor state UPDATE <table_name> SET <followed by every field name in the data>


  • Alteryx Designer
    • All version
  • Input Data Tool
  • SQL Server ODBC Drivers
    • Native Client 11
    • ODBC 13
    • ODBC 17
  • SQL Server OLEDB
  • SQL Server
    • 2012R2 +
  • Azure Synapse (Azure Data Warehouse)


Cause


Cause 1


The SQL Server table involved the data set contains a ColumnStore Index. By default, ODBC automatically opens a cursor for every result set returned from a SQL statement.

 

Cause 2


Cursors are not supported on Azure Synapse. By default, the Synapse SQL pool creates a clustered columnstore index when no index options are specified on a table.



Resolution


Workaround 1


Connect to the SQL Server database using OleDB drivers instead of ODBC, if possible.

 

Workaround 2

 

1. In the Choose Table or Specify Query window, select the SQL Editor tab.

2. After the SQL query, enter the line: SET NOCOUNT ON at the end as such:
 

--SQL query code here--
SET NOCOUNT ON
 

3. Click OK and re-run the workflow.
 

Note


Opening the Visual Query Builder to edit the query will cause the parsing error seen below, which can be ignored.





It's also possible to set the SQL Editor as the default to avoid the error from showing up right away.



 

Additional Resources

   
 
Comments
asifk
8 - Asteroid

Hi All, 

 

i have tried this however still experiencing an error when querying data from table

 

Error: Input Data (1): Error SQLExecute: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint.

 

and

 

Error: Input Data (2): Error SQLExecute: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cursor support is not an implemented feature for SQL Server Parallel DataWarehousing TDS endpoint.

gtorres8
Alteryx Alumni (Retired)

Hi @asifk 

Have you tried using the SET NOCOUNT ON command in the Pre SQL Query or the Post SQL Query section of the Input Data tool?

 

sol1.png

 


Or, have you tried OLEDB instead of ODBC? The two errors indicate you tried ODBC. If you are still having issues, please contact support and open a new case for further investigation.

asifk
8 - Asteroid

Hi @GT 

 

I have used the above functionality but still encountered the issue, Alteryx was able to cintact me and we found the issue was selecting the right OLEDB connection, to do this i had to switch to the new input layout and select the appropriate driver for Azure Synapse. thereafter the connection was established.

 

 

Regards,

Asif

porjeajinkya
5 - Atom

Hey @asifk, can you please share the workaround you received. I need to connect to the database and I'm facing the same issue. Looking forward to your reply

asifk
8 - Asteroid

Hi

 

So in order to connect, you need to use SQL client 11. Add the oledb connection and thereafter on Alteryx select the Azure Data Lake connection

The process will not work if you do not select the appropriate driver on Alteryx

 

Choose from the predefined driver settings and you should be able to establish the connection.

 

 

Kind regards

Asif