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

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.

Error Message

Database Connection

Accepted answers

All comments

HakanS

Hi Mark,

Thanks for trying out Alteryx and sorry you are running into challenges. While I am not technical and cannot help you directly, I saw your post and got some questions. I am hoping your further info may be helpful to someone who is more technical and who can provide a solution...;

Can you share a bit more about the configuration?
Did you download Alteryx Designer and running locally, or are you provisioning Designer off the Azure Marketplace?
Are you trying to connect to SQL Server (on premise or in Azure) or to Azure SQL Database? (In your description you say SQL Database but the error says SQL Server)

Also, if you don't get an answer here in a timely manner, please connect directly with our support team at http://alteryx.com/support.

Best wishes

Håkan

SeanAdams

Hey @MarkStern - very happy to help here- I have a SQL Server 2016 install at home and can work with you to debug this.

Not sure if you have concerns about sharing proprietary data (don't break any of your company's policies) but if you can share the table definition script (the create table xxx as ...) including the indices; and the workflow that you're trying to build, I'm pretty confident we can get you back on the road.

Not sure how much you know about column-store indices (vs row-store), so if necessary I can help there too.

Just post the table definition; and a stripped down version of your alteryx flow with a simple text input going into your output components (or a file input) so that you're not sharing any corporate IP on the workflow, and I'll crack into this when you reply.

Cheers Mark

Sean

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.

kguevaraj

Hi!

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

BEGIN

----Your script----

SET NOCOUNT ON;

END

🙂

madler_alteryx

Hi @MarkStern ,

You might want to use the option "Fetch Single Row" in the Input Data tool.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703