SAS==>SQL Server

nzkunb0c 于 2023-06-21 发布在 SQL Server

关注(0)|答案(1)|浏览(134)

I tried to launch a SAS query after a connection to an 'MVT' (FAMA) SQL Server database, I have a problem that I cannot apply the DELETE function to empty the TAB1 table (this table already exists in the MVT database which is connected to SQL Sserver, I have the following error:

note: on the other hand I was able to apply the DROP function to delete the table but that is not my objective, my objective is to delete the rows

libname MVT sqlsvr shema="dbo" datasrc=FAMA user=xx pwd=xx;
run;

proc sql;
delete from MVT.TAB1 ;
quit;


28         proc sql;
29         delete from MVT.TAB1;
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]A cursor with the name
       'SQL_CURF02A540A00000000' does not exist.
ERROR: ROLLBACK issued due to errors for data set MVT.TAB1.DATA.
NOTE: Deletes were rolled back by the DBMS software.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
30         run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.

sql-server

来源：https://stackoverflow.com/questions/76485810/sas-sql-server

1条答案

按热度按时间

ctzwtxfj1#

Potentially this known issue: https://support.sas.com/kb/57/757.html

Might be easiest to change to using explicit pass-through. Something like (untested):

proc sql;
  connect using mvt;
  execute by mvt
  (
   delete from Tab1;
  );
quit;

With explicit pass-through you write native SQL Server code and the database runs it. It's generally faster, and you can use native commands like the SQL Server statement TRUNCATE which should make it even faster.

赞(0）回复(0）举报 2023-06-21

我来回答

SAS==>SQL Server

1条答案

相关问题

热门标签

最新问答