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

[KylieF]

Hi there! 

Thank you for your post! This is an interesting request and we're currently looking into the technical requirements around it. Once we have a full understanding of the technical side we'll update this idea again with a response on when or if we'd be able to provide the user more control over when Alteryx makes a cursor.

[KylieF]

This idea is being updated to Revisit at this time, while our product team likes this idea and plan to look into adding this feature to the MS SQL connector, this particular connector is not on the near future roadmap for updating. We'll update this idea again once we know when or if the MS SQL connector will be added to the near future roadmap for updates.

[cphillips1]

This issue occurs when you connect to MS Access 2016 (*.accdb) file also. When I use the SQL Editor with the statement "SELECT * FROM 'table1' it pulls 4-5 duplicates of several of the records. When I open up the table in Access there are no duplicates.