topic Re: SQL error: cursors are not supported on a table which has a clustered columnstore index in Administration and Deployment

SQL error: cursors are not supported on a table which has a clustered columnstore index

Filipvdr — Wed, 16 Mar 2022 07:47:48 GMT

We are using SAS to connect to a SQL Server database but we are getting this error for a few tables in one of the schemas.

I found this note so I changed the libname:

65778 - "Cursors are not supported on a...Clustered Columnstore Index" occurs when you use SAS/ACCESS® Interface to ODBC on Microsoft SQL Server

LIBNAME test SQLSVR

cursor_type=forward_only Datasrc=BISQL2 SCHEMA=MW1DM_03 AUTHDOMAIN="Auth_BISQL2" ;

But I’m still getting the same error. Am I doing something wrong?

Re: SQL error: cursors are not supported on a table which has a clustered columnstore index

LinusH — Wed, 16 Mar 2022 08:18:34 GMT

What kind of operation do you do to get this error?

Submit

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

might give som more hints.

Can you reproduce directly in SQL Server?

Re: SQL error: cursors are not supported on a table which has a clustered columnstore index

Filipvdr — Wed, 16 Mar 2022 08:44:21 GMT

I register the library in SAS Eguide and then try to open the table.

Or in code: just a simple datastep gives me the same result.

28         options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
SQLSRV: AUTOCOMMIT is NO for connection 0
29         
30         LIBNAME test SQLSVR
31         cursor_type=forward_only   Datasrc=BISQL2  SCHEMA=MW1DM_03  AUTHDOMAIN="Auth_BISQL2" ;
NOTE:  Credential obtained from SAS metadata server.
NOTE: Libref TEST was successfully assigned as follows: 
      Engine:        SQLSVR 
      Physical Name: BISQL2
32         
SQLSRV: AUTOCOMMIT is NO for connection 1
SQLSRV: AUTOCOMMIT turned ON for connection id 1
 
SQLSRV_1: Prepared: on connection 1
SELECT * FROM "MW1DM_03"."ds_validation_evolution"
 
33         data temp;
34         set test.ds_validation_evolution;
35         run;

 
SQLSRV_2: Executed: on connection 1
Prepared statement SQLSRV_1
 
ERROR: CLI open cursor error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cursors are not supported on a table 
       which has a clustered columnstore index. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Statement(s) 
       could not be prepared.

Re: SQL error: cursors are not supported on a table which has a clustered columnstore index

LinusH — Wed, 16 Mar 2022 10:47:54 GMT

And I assume you get a successful query result using

SELECT * FROM "MW1DM_03"."ds_validation_evolution"

in SQL Server using thesa same credentials?

Looks like a connection configuration problem.

I would open a track with SAS tech support for assistence.

Re: SQL error: cursors are not supported on a table which has a clustered columnstore index

Filipvdr — Wed, 16 Mar 2022 14:05:52 GMT

Yes correct in SQL server no problem to do the same query. I opened a ticket. Thanks!

Re: SQL error: cursors are not supported on a table which has a clustered columnstore index

Sajid01 — Fri, 18 Mar 2022 20:26:51 GMT

Please have a look at this post that may solve your issue
https://stackoverflow.com/questions/35411122/sqlexceptionhelper-cursors-are-not-supported-on-a-table-which-has-a-clustered-c

Re: SQL error: cursors are not supported on a table which has a clustered columnstore index

Filipvdr — Mon, 21 Mar 2022 07:06:26 GMT

So the solution would be to add "SelectMethod=cursor" to my odbc.ini file?

[BISQL2]
Driver=/opt/sas/sashome/AccessClients/9.4/SQLServer/lib/S0sqls27.so
Description=BISQL2_ICT_MW
Address=bisql-db, 1433
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=BISQL2
EncryptionMethod=0
EnableBulkLoad=1
EnableScrollableCursors=3
ValidateServerCertificate=0
Trusted_Connection=yes
AuthenticationMethod=9
EnableQuotedIdentifiers=1
SelectMethod=cursor

Re: SQL error: cursors are not supported on a table which has a clustered columnstore index

Filipvdr — Mon, 21 Mar 2022 09:52:24 GMT

Driver=/opt/sas/sashome/AccessClients/9.4/SQLServer/lib/S0sqls27.so
Description=BISQL2_ICT_MW
Address=bisql-db, 1433
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=BISQL2
EncryptionMethod=0
EnableBulkLoad=1
EnableScrollableCursors=4
ValidateServerCertificate=0
Trusted_Connection=yes
AuthenticationMethod=9
EnableQuotedIdentifiers=1

Changing the "EnableScrollableCursors" to 4 solved my issue. Thanks for the support!

Re: SQL error: cursors are not supported on a table which has a clustered columnstore index

Sajid01 — Mon, 21 Mar 2022 13:27:10 GMT

Good to know it worked.