Will try that.
If I use the explicit SQP Pass through, I got the same error of "Delete From"
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:
Best wishes,
Jeff
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.