BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jade_SAS
Pyrite | Level 9

Hi All,

    I am running SAS on the SAS server, I try to use "Delete from" to delete rows from a table at SQL server, but ran into error. Please shed your light why I got this error. Thank you!

 

The coding I am using:

 

PROC SQL;

DELETE * FROM SQLlib.TEST;

QUIT;

 

The error message is as below:

ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]A cursor with the name

'SQL_CURE0D90EA04B2B0000' does not exist.

ERROR: ROLLBACK issued due to errors for data set SQLlib.TEST.DATA.

1 ACCEPTED SOLUTION

Accepted Solutions
JBailey
Barite | Level 11

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

View solution in original post

19 REPLIES 19
Reeza
Super User

What happens if you do select * from the same table?

 

proc sql;
select * from SQLlib.TEST;
run;

Is it possible you have only read only access to the table and cannot delete records? 

Jade_SAS
Pyrite | Level 9

Thank you! I can insert table into it, so I think I have write right.

Also I tried on the SQL server directly, I can use "delete from" on the SQL server for that table

SuryaKiran
Meteorite | Level 14

Remove asterisk(*)  symbol. 

PROC SQL;

DELETE FROM SQLlib.TEST;

QUIT;

 

 

Thanks,
Suryakiran
Jade_SAS
Pyrite | Level 9

Yes, I did try "PROC SQL;DELETE FROM SQLlib.TEST;QUIT;" and got the same error.

 

I can use the same coding for any files at SAS server, but not with the SQL server tables.

 

Thank you!

 

 

SuryaKiran
Meteorite | Level 14

Can you check if this is causing the issue >> http://support.sas.com/kb/57/757.html

Thanks,
Suryakiran
Jade_SAS
Pyrite | Level 9

Thank you, will check this.

Reeza
Super User

@Jade_SAS wrote:

Yes, I did try "PROC SQL;DELETE FROM SQLlib.TEST;QUIT;" and got the same error.

 

I can use the same coding for any files at SAS server, but not with the SQL server tables.

 

Thank you!

 

 


I suspect that's a permissions issue then, can you verify with your DBA that you have the correct privileges and can delete records from tables.

Jade_SAS
Pyrite | Level 9

Thank you. I even can use this statement to drop table as below:

%_eg_conditional_dropds(SQLlib.TEST);

 

I also checked with the DBA, he said I have the read and write right.

Patrick
Opal | Level 21

@Jade_SAS wrote:

Thank you. I even can use this statement to drop table as below:

%_eg_conditional_dropds(SQLlib.TEST);

 

I also checked with the DBA, he said I have the read and write right.


 Read allows for SELECT, Write allows for INSERT.... doesn't mean you're allowed to DELETE.

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql...

 

Jade_SAS
Pyrite | Level 9

Thank you, I will check with the DBA

Jade_SAS
Pyrite | Level 9

@Patrick

Checked I do have Delete from right because I can use the "Delete from" at the SQL studio with same table, but could not do it in SAS.

Patrick
Opal | Level 21

@Jade_SAS

Given that a SELECT or INSERT clause also works out of SAS for this table, everything "connectivity" appears to be o.k.

Is the user with SQL Studio and SAS the same? And if you've got working code in SQL Studio then why don't you just copy/paste this working code 1:1 into an explicit pass-through SQL in SAS?

 

David_Billa
Rhodochrosite | Level 12
What will happen if the datasets listed in proc sql delete from is not available?

Requirement is to delete multiple datasets in one step and sometimes some of the datasets won't be available for delete.

Will it throws an error or warning?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 19 replies
  • 5589 views
  • 4 likes
  • 7 in conversation