topic Re: Delete Row in SQL Table Via Proc SQL Over SAS ODBC Connection in SAS Programming

Delete Row in SQL Table Via Proc SQL Over SAS ODBC Connection

elmoreoocyte — Thu, 03 Feb 2022 16:19:36 GMT

I'm trying to insert and drop rows over an ODBC connection. My Insert block works fine, and I'd hoped the drop would work the same, but I get the following error in my SAS log when trying to drop the row I just inserted:

ERROR: Updating or deleting data cannot be supported by this engine when a WHERE clause and the DBCOMMIT option are used together

proc sql;
insert into sql_meta.dash_meta_dates
SELECT *
FROM WORK.test;


proc sql;
delete from sql_meta.dash_meta_dates
where GROUP_AE_REGISTRY = "Test";

Re: Delete Row in SQL Table Via Proc SQL Over SAS ODBC Connection

elmoreoocyte — Thu, 03 Feb 2022 16:39:42 GMT

Solved my own problem.

I had to comment out the DBCOMMIT portion of my ODBC conneciton:

*Libname for the meta dashboard dates table;
LIBNAME SQL_META ODBC DSN="Dash" DATABASE="QA" SCHEMA=dbo
READBUFF=4000 INSERTBUFF=4000 /*DBCOMMIT=4000*/
;

Re: Delete Row in SQL Table Via Proc SQL Over SAS ODBC Connection

SASKiwi — Thu, 03 Feb 2022 19:24:37 GMT

Please update your post as answered in that case to avoid us checking it to see if you still need help or not.

Re: Delete Row in SQL Table Via Proc SQL Over SAS ODBC Connection

elmoreoocyte — Thu, 03 Feb 2022 19:26:51 GMT

This was my first post, so I didn't know to do that. Thanks.