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 1
The SQL Server table involved in 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.
Workaround 1:
Use the Fetch Single Rows option in the Input Data tool.
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.