community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Input Data Tool: Can we control use of Cursors?

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

9 Comments
Nebula
Nebula

Out of interest - this came up during Beta testing.

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...

Atom

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

Nebula
Nebula

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.

 

 

Alteryx
Alteryx

@SeanAdams 

 

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

Meteor

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

Alteryx
Alteryx

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.

Meteor

@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

Alteryx
Alteryx

@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.

Atom

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