topic Re: SAS Error when using "delete from" to remove rows from SQL table in SAS Data Management

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

Jade_SAS — Tue, 05 Jun 2018 21:13:49 GMT

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.

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

Reeza — Tue, 05 Jun 2018 21:52:40 GMT

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?

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

SuryaKiran — Tue, 05 Jun 2018 23:41:02 GMT

Remove asterisk(*) symbol.

PROC SQL;

DELETE FROM SQLlib.TEST;

QUIT;

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

Jade_SAS — Wed, 06 Jun 2018 01:03:52 GMT

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

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

Jade_SAS — Wed, 06 Jun 2018 01:05:57 GMT

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!

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

SuryaKiran — Wed, 06 Jun 2018 01:19:13 GMT

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

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

Reeza — Wed, 06 Jun 2018 02:58:45 GMT

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

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

Jade_SAS — Thu, 07 Jun 2018 02:05:24 GMT

Thank you, will check this.

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

Jade_SAS — Thu, 07 Jun 2018 02:09:22 GMT

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.

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

Patrick — Thu, 07 Jun 2018 12:39:50 GMT

@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-server-2017

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

Jade_SAS — Thu, 07 Jun 2018 15:31:26 GMT

Thank you, I will check with the DBA

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

Jade_SAS — Thu, 07 Jun 2018 20:07:43 GMT

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

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

Patrick — Thu, 07 Jun 2018 22:05:35 GMT

@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?

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

LinusH — Wed, 13 Jun 2018 04:52:49 GMT

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

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

Jade_SAS — Fri, 15 Jun 2018 15:48:50 GMT

Will try that.

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

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

Jade_SAS — Wed, 11 Jul 2018 12:46:09 GMT

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

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

Jade_SAS — Wed, 18 Jul 2018 15:47:12 GMT

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!

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

JBailey — Fri, 03 Aug 2018 19:54:54 GMT

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

Best wishes,

Jeff

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

David_Billa — Sun, 19 Apr 2020 16:13:25 GMT

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?

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

Reeza — Sun, 19 Apr 2020 18:29:28 GMT

It'll throw warnings.