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

[Filipvdr]

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

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?

[LinusH]

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?

[Filipvdr]

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.

[LinusH]

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. 

[Filipvdr]

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

[Sajid01]

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

[Filipvdr]

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

[Filipvdr · Accepted solution]

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!

[Sajid01]

Good to know it worked.