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
