Error SQL Execute: Cursors Not supported on Clustered Columnstore index.

[MarkStern]

All,

I am new to this product and trying to connect the input data node to our SQL Database and I get the following error each time i connect to a table and try to run the node.  I need to get past this point t enable me to use and try out the power of the tool.

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

[DataDrifting]

I have been using clustered columnstore indexes for two years along with Alteryx, the only work around I have found is to drop and recreate the index as a pre and post SQL statement.

Pre Create SQL Statement:

IF EXISTS (SELECT name FROM sysindexes WHERE name = '{index name}') DROP INDEX {table name}.{index name};

Post Create SQL Statement:

CREATE CLUSTERED COLUMNSTORE INDEX {index name} ON {table name};

[jurejaklic]

The only problem with that "workaround" is that analysts who are usually using Alteryx can't (or at least shouldn't be allowed to) do that on an Enterprise Data Warehouse at a FTSE100 (or any other) company. The analsyt would require write permissons on a production data warehouse.

This only works in non production environments where you have write permissons on the tables, which I presume most people don't have.

[DataDrifting]

For performance, an ETL process should always disable or drop indexes for inserts.  That's what most of the big organisations using Informatica do and is recommended, production ETL accounts should have this ability.  

[jurejaklic]

That's correct, but the analysts who are the only ones using Alteryx do not and will not have the same permissons as the production ETL service accounts. For that we have Informatica as well. Analysts and with that Alteryx will have a read only access on the production DWH. The whole point of having Alteryx was to make the analysts' job easier, but with this limitation, it's close to being useless. Yes, there are workarounds, but for something so basic there shouldn't have to be workarounds, especially with a tool that markets itself as a tool that can make data easily accessible.

[Fraser]

Hi @SeanAdams - thank you for posting the link to the "ideas" page for this last year.

This issue is still occurring and we have had a number of people echo their concerns on this same thread. We have the OLE DB workaround, but this is cumbersome to set up and use.

How can we raise the profile of this issue with Alteryx? This has been open for 2 years but we've had no indication of any progress on addressing this problem.

Thanks

Fraser

[MonBrazier]

This seems like a recurring issue, as I am getting this same error. 😞

[CharlieS]

In case anyone is looking for a solution, you can try wrapping your query with these lines. This came up for us today and this worked for us with no other changes.

BEGIN

<<your sql query>>

SET NOCOUNT ON
END

[Derangedvisions]

Three years later and you have managed to quickly solve a problem I was running into. Thanks @SeanAdams 

[kguevaraj]

Hi! 

My team and I fixed using this at the beginning and end of the script:

BEGIN

----Your script----

SET NOCOUNT ON;

END

🙂

[CDunhill]

Yet ANOTHER 3 years on and I see this is still an issue. 

Regardless, your solution was just the ticket, so thank you.