Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.

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

Highlighted
5 - Atom

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};

Highlighted
5 - Atom

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.

Highlighted
5 - Atom

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.  

 

 

Highlighted
5 - Atom

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.

Highlighted
6 - Meteoroid

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

Highlighted
8 - Asteroid

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

 

 

 

Labels