Cannot get data from a table with a columnstore index when using MssqlConnectionFactory.
When trying to execute a query I am getting the error:
Cursors are not supported on a table which has a clustered columnstore index.; nested exception is io.r2dbc.mssql.ExceptionFactory$MssqlNonTransientException: [35370] [S0001] Cursors are not supported on a table which has a clustered columnstore index.
The query I am trying to execute has this structure:
SELECT TOP 1000 * FROM <table_name> ORDER BY <date_field> DESC
The queries are being executed using spring-r2dbc DatabaseClient (v5.3.22) with a MssqlConnectionFactory.
What I have tried
Setting the option preferCursoredExecution to use direct. Which I believe it should be using by default. To set the property I have tried the few different approaches suggested in the documentation. e.g.:
ConnectionFactoryOptions
MssqlConnectionConfiguration.builder()
Setting the options value with boolean vs Predicate<String>.
Connecting to the database directly and changing the selectMethod between cursor and direct. When using direct the data is returned successfully. When using cursor I get the exception as expected.
Versions
Issue
Cannot get data from a table with a columnstore index when using MssqlConnectionFactory.
When trying to execute a query I am getting the error:
Cursors are not supported on a table which has a clustered columnstore index.; nested exception is io.r2dbc.mssql.ExceptionFactory$MssqlNonTransientException: [35370] [S0001] Cursors are not supported on a table which has a clustered columnstore index.
The query I am trying to execute has this structure:
SELECT TOP 1000 * FROM <table_name> ORDER BY <date_field> DESC
The queries are being executed using spring-r2dbc DatabaseClient (v5.3.22) with a MssqlConnectionFactory.
What I have tried
Setting the option
preferCursoredExecution
to use direct. Which I believe it should be using by default. To set the property I have tried the few different approaches suggested in the documentation. e.g.:Setting the options value with
boolean
vsPredicate<String>
.Connecting to the database directly and changing the
selectMethod
betweencursor
anddirect
. When usingdirect
the data is returned successfully. When usingcursor
I get the exception as expected.