SAS Error when using "delete from" to remove rows from SQL table

[LinusH]

A long shot perhaps, but since you are getting some sort of CL error, try out the USE_ODBC_CL=YES libname option.

[Jade_SAS]

Will try that.

If I use the explicit SQP Pass through, I got the same error of "Delete From"

[Jade_SAS]

SQL DBA changed something on the SQL server, so right now the explicit SQL Pass through worked. if using the delete from directly in SAS coding, it does not work. But at least we have the explicit SQL pass through solution for now. thank you, everyone!

[JBailey · Accepted solution]

Hi @Jade_SAS

The answer provided by @SuryaKiran is the hint that would enable someone to come up with the solution to this specific problem (with a pretty big caveat - addressed below)... 

In this example I am using a DSN-less connection in the LIBNAME statement because it makes it easier (for me) and helps make the option obvious. Feel free to take a look at the SAS Note for an example of adding the EnableScrollableCursors= option using the ODBC Admin application on Windows.

Here is the example:

libname mssql sqlsvr complete="DRIVER={SAS ACCESS to SQL Server};HOST='mysqlserver.mycomputer.com';
PORT=1433;UID=myuser;PWD=mypassword;";


proc sql;
   create table mssql.cars 
      as select * from sashelp.cars;
quit;


proc sql;
   delete from mssql.cars;
quit;

This results in the error @Jade_SAS reported.

ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]A cursor
with the name 'SQL_CURA06FE22B00000000' does not exist.
ERROR: ROLLBACK issued due to errors for data set MSSQL.cars.DATA.

Add the EnableScrollableCursors=3 option and it works...

libname mssql sqlsvr complete="DRIVER={SAS ACCESS to SQL Server};HOST='mysqlserver.mycomputer.com';
PORT=1433;UID=myuser;PWD=mypassword;EnableScrollableCursors=3"; 

proc sql;
  delete from mssql.cars;
quit;

1865 proc sql;
1866 delete from mssql.cars;
NOTE: 428 rows were deleted from MSSQL.cars.

1867 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.76 seconds
cpu time 0.11 seconds

It is important to note that DBIDIRECTEXEC does not work with SAS/ACCESS Interface to Microsoft SQL Server. This means SAS opens a cursor and deletes each row individually. This is fine for small tables but would be very slow and resource intensive for large DELETEs. The reason is that the rows are deleted individually using WHERE CURRENT OF clause (this uses a scrollable cursor and is the reason the error message appears). By default the Data Direct ODBC driver has scrollable cursors disabled probably because of the performance impact of using them. The DIRECT_EXE= option can be used to force the deletes into SQL Server. 

The caveat: The Explicit Pass-Through approach forces the database to handle the DELETE and will be faster. Even better, use explicit pass-through with the Transact-SQL TRUNCATE command: 

https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-20...

Best wishes,

Jeff

[Jade_SAS]

I got an different error after trying to add

"USE_ODBC_CL=YES to the libname statement.

ERROR: Error updating table entry: [DataDirect][ODBCCUR lib] Driver not capable

BTW, I am using SQLSVR for libname