SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Regular Contributor
Posts: 223

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

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.

Super User
Posts: 23,309

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

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? 

Regular Contributor
Posts: 223

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

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

Valued Guide
Posts: 560

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

Remove asterisk(*)  symbol. 

PROC SQL;

DELETE FROM SQLlib.TEST;

QUIT;

 

 

Thanks,
Suryakiran
Regular Contributor
Posts: 223

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

Posted in reply to SuryaKiran

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!

 

 

Valued Guide
Posts: 560

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

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

Thanks,
Suryakiran
Regular Contributor
Posts: 223

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

Posted in reply to SuryaKiran

Thank you, will check this.

Super User
Posts: 23,309

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


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

Regular Contributor
Posts: 223

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

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.

Respected Advisor
Posts: 4,679

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


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

 

Regular Contributor
Posts: 223

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

Thank you, I will check with the DBA

Regular Contributor
Posts: 223

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

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

Respected Advisor
Posts: 4,679

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

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

 

Super User
Posts: 5,852

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

A long shot perhaps, but since you are getting some sort of CL error, try out the USE_ODBC_CL=YES libname option.
Data never sleeps
Regular Contributor
Posts: 223

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

Will try that.

 

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

Ask a Question
Discussion stats
  • 14 replies
  • 228 views
  • 2 likes
  • 5 in conversation