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.

[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

[MarkStern]

Hi Sean,

Please find below and i have attached simple workflow

CREATE TABLE [dbo].[FACT_xxx](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CUSTOMER_SEQ] [bigint] NOT NULL,
[SEGMENT_ID] [int] NOT NULL,
[BEHAVIOR] VARCHAR(50) NOT NULL,
[FINANCIAL_YEAR_PERIOD] [int] NOT NULL,
) ON [PRIMARY]

GO

/****** Object: Index [cx_cs_FACT_SEGMENT_BEHAVIOUR] Script Date: 03/05/2017 13:13:36 ******/
CREATE CLUSTERED COLUMNSTORE INDEX [cx_cs_FACT_xxx] ON [dbo].[FACT_xxx] WITH (DROP_EXISTING = OFF)
GO

/****** Object: Index [missing_index_340328_340327_FACT_SEGMENT_CORAL_BEHAVIOUR] Script Date: 03/05/2017 13:13:48 ******/
CREATE NONCLUSTERED INDEX [missing_index_340328_340327_FACT_xxxx] ON [dbo].[FACT_xxx]
(
[CUSTOMER_SEQ] ASC,
[FINANCIAL_YEAR_PERIOD] ASC
)
INCLUDE ( [BEHAVIOR]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

SET ANSI_PADDING OFF

[SeanAdams]

Hey Mark,

Firstly - I was able to replicate this error exactly, so yes, this seems to be a repeatable issue.    

The workaround that I found is to switch the database connection from native SQL to OleDB to SQL connectivity - that way I'm getting a good connection.

@LeahK - given that this is a replicatable error - would you mind passing this on to one of the team who works with the input control or SQL connectivity?    The exact error message is below, and it looks like Alteryx is trying to open up a cursor to read the table which is not supported, but from what we see on the OleDB connection you can read this without a cursor with no problem.

Error: Input Data (8): 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.

Thank you 

Sean

[jurejaklic]

Hi Sean and Leah,

We're having the same problem with Alteryx not being able to connect to tables with clustered columnstore index on SQL Server 2016. Has there been any progress to resolving this problem. There's been a new release since the issue was first reported, but I'm still having this problem. Running Alteryx Designer x64 v11.7.4.37815 with SQL Server 2016.

Regards,

Jure

[yuliya_a]

Hello,

I am getting the same error message when I run my existing SQL query in Alteryx using ODBC connection. Is there a workaround?

Thanks.

[MJ]

Same here.  Please advise if there is a workaround.  

Preferable one that does not involve making changes to the database configuration...

[SuzanneMc]

I had the same error and switching to the OLEdb driver corrected it for me, give it a try.

[SeanAdams]

Please support the idea to make cursors optional for clustered columnstore data. https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Input-Data-Tool-Can-we-control-use-of-Cursor...

Although you can work around this by using a different driver type (as @SuzanneMc mentioned) - if using the latest generation of ODBC against a columnstore table with no preview of rowcount and read uncommited , a cursor actually creates an unnecessary overhead on the read.