Input Data Tool: Can we control use of Cursors?

[SeanAdams]

Originally posted here: https://community.alteryx.com/t5/Data-Sources/Input-Data-Tool-Can-we-control-use-of-Cursors/m-p/5871...

Hi there,

I've profiled a simple query using SQL Server Profiler (Query: Select * from northwind.dbo.orders; row limit: 107; read Uncommitted: true) and interestingly it opens up a cursor if you connect via ODBC or SQL Native; but not by OleDB - full queries and profile details are on the discussion thread above.

However - in some circumstances a cursor is not usable - e.g. https://community.alteryx.com/t5/Data-Sources/Error-SQL-Execute-Cursors-Not-supported-on-Clustered-C... because SQL doesn't allow cursors on columnstore indexed tables & columns

Is there any way (even if we need to manually adjust via the XML settings) to ask Alteryx not to create the cursor and execute directly on the server as written?

Thank you

Sean

[SeanAdams]

Out of interest - this came up during Beta testing.

• If you download the Microsoft Wide World Importers DW test data set (https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers) which is used for both learning and testing out the Microsoft Data Warehouse capabilities - all the key tables are set up as columnstore
• However the input tools in Alteryx all assume that data is in RowStore, and so you get errors that "Cursor not supported on clustered columnstore" 

While it is possible to bring this back with a cursor, or change to OleDB, there is no real need to use a cursor on this data set - and it should be allowed to be set as an option of some kind.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Error-SQL-Execute-Cursors-Not-supporte...

[Fraser]

Hi, long time Alteryx user here, recent Community lurker and new Community poster.

At our organisation we are still suffering from this same issue whereby any tables with Clustered Columnstore Index enabled cannot be streamed out locally into an Alteryx workflow. 

Most of the production tables in our datawarehouse have CCI enabled and configuring CCI for large tables is generally good business practice.

This limitation is a big inhibitor for rolling Alteryx out to the rest of the business for us.

At the moment, we are having to configure OLE DB workaround connection strings, but Alteryx should really allow us the use of ODBC / SQL Native configurations which have better performance and are much easier for our users to set up.

This had initially been flagged over 2 years ago and last posted about 6 months ago, so it's a relevant issue for many end-users.

Has any work been planned to remediate this issue in an upcoming release? Hopefully adding an option to disable the cursor functionality would suffice to clear this problem.

Thanks

Fraser

[SeanAdams]

cc: @jpoz 

This is the cursor issue I was mentioning on the ODBC connection for SQL.    Relatively easy to replicate, just create a columnstore table on MS SQL Server (2016 or later) and then try to query some data.

[jpoz]

@SeanAdams 

There is a workaround for this issue. Encapsulate the query between "BEGIN <<your sql query>> SET NOCOUNT ON END"

[mattd17]

@jpoz you can elaborate the SET NOCOUNT ON END workaround? I take it I have to start that with each query? 

[jpoz]

Just surround your query in:

BEGIN

     your query

SET NOCOUNT ON END 

This should allow you to use the Input Data tool with a columnstore index.

[mattd17]

@jpoz I now get a "No Columns Returned?" error message on all the queries I added the BEGIN and SET NOCOUNT ON END to... any thoughts there? The only change was switch the driver from Ole DB to ODBC

[jpoz]

@mattd17 Maybe that workaround doesn't work. I didn't have a SQL Server with columnstore support to test on. I will look into another fix for this.

[CwP]

@JPoz. That work around did work in my scenario.

[nelsonm91]

@jpoz Just came looking for a solution to this & that worked for me - thanks!

Trying the workaround of changing from ODBC to OLEDb didn't work for my scenario.