SAS/ACCESS to SQL Server bug in error handling?

[Quentin]

Hi All,

I've been playing with defining a primary key in MS SQL server, and then inserting duplicates from SAS, to see that it throws error, so that I can test the SQL server option IGNORE_DUP_KEY which allows SQL server to suppress the error.

The surprise was that my first simple test case failed to throw an error.  I noticed what looks like a bug, tested on Windows PC SAS 9.4M4 using SAS/ACCESS to ODBC and linux 9.4M6 using SAS/ACCESS to SQL server.

Example 1. This example throws a duplicate key error, as expected:

proc sql;
  connect using sql as mydb;

  execute (
    drop table if exists foo 
    CREATE TABLE dbo.foo (bar int PRIMARY KEY ) 
    INSERT dbo.foo VALUES (1) 
    INSERT dbo.foo VALUES (2) 
  ) by mydb;

  execute (
    INSERT dbo.foo VALUES (1) 
    INSERT dbo.foo VALUES (3)
  ) by mydb;

  disconnect from mydb;
quit;

Log is:

2146  proc sql;
2147    connect using sql as mydb;
ODBC: AUTOCOMMIT is YES for connection 2
2148
2149    execute (
2150      drop table if exists foo
2151      CREATE TABLE dbo.foo (bar int PRIMARY KEY )
2152      INSERT dbo.foo VALUES (1)
2153      INSERT dbo.foo VALUES (2)
2154    ) by mydb;

ODBC_346: Executed: on connection 2
drop table if exists foo CREATE TABLE dbo.foo (bar int PRIMARY KEY ) INSERT dbo.foo VALUES (1) INSERT
dbo.foo VALUES (2)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
2155
2156    execute (
2157      INSERT dbo.foo VALUES (1)
2158      INSERT dbo.foo VALUES (3)
2159    ) by mydb;

ODBC_347: Executed: on connection 2
INSERT dbo.foo VALUES (1) INSERT dbo.foo VALUES (3)

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY
       KEY constraint 'PK__foo__DE90ECFF24850DC5'. Cannot insert duplicate key in object 'dbo.foo'. The
       duplicate key value is (1). : [Microsoft][SQL Server Native Client 11.0][SQL Server]The
       statement has been terminated.
2160
2161    disconnect from mydb;
2162  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.25 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              111.53k
      OS Memory           13032.00k
      Timestamp           08/18/2021 04:54:40 PM
      Step Count                        197  Switch Count  0

If I change the order of the inserts in the second execute statement, I don't get an error.

So this code:

Example 2.

proc sql;
  connect using sql as mydb;

  execute (
    drop table if exists foo 
    CREATE TABLE dbo.foo (bar int PRIMARY KEY ) 
    INSERT dbo.foo VALUES (1) 
    INSERT dbo.foo VALUES (2) 
  ) by mydb;

  execute (
    INSERT dbo.foo VALUES (3)   /*only change is insert 3 before inserting 1*/
    INSERT dbo.foo VALUES (1)  
  ) by mydb;

  disconnect from mydb;
quit;

Gives a clean log:

2180  proc sql;
2181    connect using sql as mydb;
ODBC: AUTOCOMMIT is YES for connection 2
2182
2183    execute (
2184      drop table if exists foo
2185      CREATE TABLE dbo.foo (bar int PRIMARY KEY )
2186      INSERT dbo.foo VALUES (1)
2187      INSERT dbo.foo VALUES (2)
2188    ) by mydb;

ODBC_350: Executed: on connection 2
drop table if exists foo CREATE TABLE dbo.foo (bar int PRIMARY KEY ) INSERT dbo.foo VALUES (1) INSERT
dbo.foo VALUES (2)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
2189
2190    execute (
2191      INSERT dbo.foo VALUES (3)  /*only change is insert 3 before inserting 1*/
2192      INSERT dbo.foo VALUES (1)
2193    ) by mydb;

ODBC_351: Executed: on connection 2
INSERT dbo.foo VALUES (3) INSERT dbo.foo VALUES (1)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
2194
2195    disconnect from mydb;
2196  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.36 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              111.53k
      OS Memory           13032.00k
      Timestamp           08/18/2021 04:57:08 PM
      Step Count                        199  Switch Count  0

In both examples the database is handling the data correctly; it rejects the attempt to insert a duplicate record.  But in the second case, SAS is not throwing the error message to the log, which seems bad.  If you can't trust the log, what can you trust?

Happily, if I try to insert duplicates using PROC APPEND with implicit pass-through (which is often how I insert data into SQL from SAS), I get the error message, regardless of the order of rows in the inserted data. I suspect this is because the insert is happing as a single transaction. So both of the appends below throw an error, and rollback, which I would expect:

Example 3.

proc sql;
  connect using sql as mydb;

  execute (
    drop table if exists foo 
    CREATE TABLE dbo.foo (bar int PRIMARY KEY ) 
    INSERT dbo.foo VALUES (1) 
    INSERT dbo.foo VALUES (2) 
  ) by mydb;

  disconnect from mydb;
quit;


data a ;
  bar=1 ;output ;
  bar=3 ;output ;
run ;

proc append base=sql.foo data=a ;
run ;

data _null_ ;
  set sql.foo ;
  put bar= ;
run ;



data b ;
  bar=3 ;output ;
  bar=1 ;output ;
run ;

proc append base=sql.foo data=b ;
run ;

data _null_ ;
  set sql.foo ;
  put bar= ;
run ;

Do you agree that SAS not throwing an error in Example 2 likely a bug?

Thanks,

-Q.

[Tom]

And what happens if you run that second example using some other tool for submitting SQL code your server?

[Quentin]

Running Example 2 in SQL Server Management Studio throws the expected error.  Which is why I think that the error is being thrown by SQL server, but SAS isn't relaying the error.

[jimbarbour]

Hi, Quentin,

Have you checked SYSDBMSG, SYSDBRC, SQLXRC, and SQLXMSG after the query?  I'd be curious as to the value of SYSDBMSG, SYSDBRC, SQLXRC, and SQLXMSG.

Jim

[Quentin]

---

@jimbarbour wrote:

Hi, Quentin,

Have you checked SYSDBMSG, SYSDBRC, SQLXRC, and SQLXMSG after the query?  I'd be curious as to the value of SYSDBMSG, SYSDBRC, SQLXRC, and SQLXMSG.

Jim

---

Good question, @jimbarbour .  I hadn't checked all of those, so thanks for the list.

When Example 1 runs and shows an error in the log, it's captured in SYSDBMSG and SYSDBRC:

1
2    proc sql;
3      connect using sql as mydb;
ODBC: AUTOCOMMIT is YES for connection 1
4
5      execute (
6        drop table if exists foo
7        CREATE TABLE dbo.foo (bar int PRIMARY KEY )
8        INSERT dbo.foo VALUES (1)
9        INSERT dbo.foo VALUES (2)
10     ) by mydb;

ODBC_5: Executed: on connection 1
drop table if exists foo CREATE TABLE dbo.foo (bar int PRIMARY KEY ) INSERT dbo.foo VALUES (1) INSERT
dbo.foo VALUES (2)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
11
12     execute (
13       INSERT dbo.foo VALUES (1)
14       INSERT dbo.foo VALUES (3)
15     ) by mydb;

ODBC_6: Executed: on connection 1
INSERT dbo.foo VALUES (1) INSERT dbo.foo VALUES (3)

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY
       KEY constraint 'PK__foo__DE90ECFF15CF9BC7'. Cannot insert duplicate key in object 'dbo.foo'. The
       duplicate key value is (1). : [Microsoft][SQL Server Native Client 11.0][SQL Server]The
       statement has been terminated.
16
17     disconnect from mydb;
18   quit;
NOTE: The SAS System stopped processing this step because of errors.
19
20   %put &=sysrc  &=SYSDBMSG &=SYSDBRC &=sqlxrc &=sqlxmsg;
SYsrc=0  SYSDBMSG=ODBC: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY
constraint 'PK__foo__DE90ECFF15CF9BC7'. Cannot insert duplicate key in object 'dbo.foo'. The duplicate
key value is (1). : [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been
terminated.  SYSDBRC=23000 SQLXRC=0 SQLXMSG=

But in example 2 where there is no error in the log, there is also nothing in the return codes:

21   proc sql;
22     connect using sql as mydb;
ODBC: AUTOCOMMIT is YES for connection 1
23
24     execute (
25       drop table if exists foo
26       CREATE TABLE dbo.foo (bar int PRIMARY KEY )
27       INSERT dbo.foo VALUES (1)
28       INSERT dbo.foo VALUES (2)
29     ) by mydb;

ODBC_7: Executed: on connection 1
drop table if exists foo CREATE TABLE dbo.foo (bar int PRIMARY KEY ) INSERT dbo.foo VALUES (1) INSERT
dbo.foo VALUES (2)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
30
31     execute (
32       INSERT dbo.foo VALUES (3)  /*only change is insert 3 before inserting 1*/
33       INSERT dbo.foo VALUES (1)
34     ) by mydb;

ODBC_8: Executed: on connection 1
INSERT dbo.foo VALUES (3) INSERT dbo.foo VALUES (1)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
35
36     disconnect from mydb;
37   quit;

38
39   %put &=sysrc  &=SYSDBMSG &=SYSDBRC &=sqlxrc &=sqlxmsg;
SYsrc=0  SYSDBMSG= SYSDBRC=0 SQLXRC=0 SQLXMSG=

I wish I could get to the SQL server logs.  But every test I've run in SSMS has thrown the expected error.  

Here's log from a simpler example which should throw an error, but doesn't:

1    proc sql;
2      connect using sql as mydb;
ODBC: AUTOCOMMIT is YES for connection 1
3
4      execute (
5        drop table if exists foo
6        CREATE TABLE dbo.foo (bar int PRIMARY KEY )
7        INSERT dbo.foo VALUES (1)
8        INSERT dbo.foo VALUES (2)
9        INSERT dbo.foo VALUES (2)
10       INSERT dbo.foo VALUES (3)
11     ) by mydb;

ODBC_20: Executed: on connection 1
drop table if exists foo CREATE TABLE dbo.foo (bar int PRIMARY KEY ) INSERT dbo.foo VALUES (1) INSERT
dbo.foo VALUES (2) INSERT dbo.foo VALUES (2) INSERT dbo.foo VALUES (3)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
12
13     disconnect from mydb;
14   quit;

15
16   %put &=sysrc  &=SYSDBMSG &=SYSDBRC &=sqlxrc &=sqlxmsg;
SYsrc=0  SYSDBMSG= SYSDBRC=0 SQLXRC=0 SQLXMSG=
17
ODBC: AUTOCOMMIT is NO for connection 1
ODBC: AUTOCOMMIT turned ON for connection id 1

ODBC_21: Prepared: on connection 1
SELECT * FROM "dbo"."foo"

18   data _null_ ;
19     set sql.foo ;
20     put bar= ;
21   run ;


ODBC_22: Executed: on connection 1
Prepared statement ODBC_21

bar=1
bar=2
bar=3
NOTE: There were 3 observations read from the data set SQL.foo

Note above that there is only one log statement "ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement." even though there are 3 insert statements.  I think maybe only the message from the first insert is coming through to SAS.  When I run that same block of code on SQL Server Management Studio, the log there returns one message for each insert:

(1 row affected)

(1 row affected)
Msg 2627, Level 14, State 1, Line 5
Violation of PRIMARY KEY constraint 'PK__foo__DE90ECFFEDF176A5'. Cannot insert duplicate key in object 'dbo.foo'. The duplicate key value is (2).
The statement has been terminated.

(1 row affected)

In order to get a log like that from SAS, I would need to use a separate EXECUTE statement for each INSERT:

44   proc sql;
45     connect using sql as mydb;
ODBC: AUTOCOMMIT is YES for connection 1
46
47     execute (
48       drop table if exists foo
49       CREATE TABLE dbo.foo (bar int PRIMARY KEY )
50     ) by mydb;

ODBC_28: Executed: on connection 1
drop table if exists foo CREATE TABLE dbo.foo (bar int PRIMARY KEY )

51     execute (
52       INSERT dbo.foo VALUES (1)
53     ) by mydb;

ODBC_29: Executed: on connection 1
INSERT dbo.foo VALUES (1)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
54     execute (
55       INSERT dbo.foo VALUES (2)
56     ) by mydb;

ODBC_30: Executed: on connection 1
INSERT dbo.foo VALUES (2)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
57     execute (
58       INSERT dbo.foo VALUES (2)
59     ) by mydb;

ODBC_31: Executed: on connection 1
INSERT dbo.foo VALUES (2)

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY
       KEY constraint 'PK__foo__DE90ECFF3836939D'. Cannot insert duplicate key in object 'dbo.foo'. The
       duplicate key value is (2). : [Microsoft][SQL Server Native Client 11.0][SQL Server]The
       statement has been terminated.
60     execute (
61       INSERT dbo.foo VALUES (3)
62     ) by mydb;

ODBC_32: Executed: on connection 1
INSERT dbo.foo VALUES (3)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
63
64     disconnect from mydb;
65   quit;
NOTE: The SAS System stopped processing this step because of errors.
66


ODBC: AUTOCOMMIT is NO for connection 1
ODBC: AUTOCOMMIT turned ON for connection id 1

ODBC_33: Prepared: on connection 1
SELECT * FROM "dbo"."foo"

67   data _null_ ;
68     set sql.foo ;
69     put bar= ;
70   run ;


ODBC_34: Executed: on connection 1
Prepared statement ODBC_33

bar=1
bar=2
bar=3
NOTE: There were 3 observations read from the data set SQL.foo.

[Tom]

Make sure to examine the macro variable values BEFORE you execute another statement.  You current code is waiting until after the DISCONNECT statement so you are getting the status of that statements execution.

[Quentin]

---

@Tom wrote:

Make sure to examine the macro variable values BEFORE you execute another statement.  You current code is waiting until after the DISCONNECT statement so you are getting the status of that statements execution.

---

Thanks Tom.  I always forget that about PROC SQL statements being executed immediately, and usually only write one statement per PROC SQL step.  I moved the %PUT to immediately after the statement that should throw an error. It didn't make a difference, except for in the example which does throw an error to the SAS log, all four automatic macro variables catching return codes are now populated: SYSDBMSG, SYSDBRC, SQLXRC, SQLXMSG.

But in the steps that don't throw an error even thought they should, the macro vars are all set to 0 or null.

[Quentin]

@yabwon kindly recommended an additional test, which ended up supporting my theory that SAS is only honoring the log messages sent by the first INSERT statement in an execute block.  

If you use a single INSERT statement to insert two rows, where the second row violates the primary key, you get the expected error:

*Example 4 ;
proc sql;
  connect using sql as mydb;

  execute (
    drop table if exists foo 
    CREATE TABLE dbo.foo (bar int PRIMARY KEY ) 
    INSERT dbo.foo VALUES (1) 
                         ,(2) 
  ) by mydb;

  execute (
    INSERT dbo.foo VALUES (3)   /*one insert statement inserting two rows throws expected error*/
                         ,(1)  
  ) by mydb;

  %put &=sysrc  &=SYSDBMSG &=SYSDBRC &=sqlxrc &=sqlxmsg;

  disconnect from mydb;
quit;


data _null_ ;
  set sql.foo ;
  put bar= ;
run ;

But as shown before, if you use multiple INSERT statement to insert two rows, where the second row violates the primary key, you do not get the expected error:

*Example 5 ;
proc sql;
  connect using sql as mydb;

  execute (
    drop table if exists foo 
    CREATE TABLE dbo.foo (bar int PRIMARY KEY ) 
    INSERT dbo.foo VALUES (1) 
                         ,(2) 
  ) by mydb;

  execute (
    INSERT dbo.foo VALUES (3)  
    INSERT dbo.foo VALUES (1) /*second insert statements does not throw expected error*/ 
  ) by mydb;

  %put &=sysrc  &=SYSDBMSG &=SYSDBRC &=sqlxrc &=sqlxmsg;

  disconnect from mydb;
quit;


data _null_ ;
  set sql.foo ;
  put bar= ;
run ;

Log is :

1
2    *Example 4 ;
3    proc sql;
4      connect using sql as mydb;
ODBC: AUTOCOMMIT is YES for connection 1
5
6      execute (
7        drop table if exists foo
8        CREATE TABLE dbo.foo (bar int PRIMARY KEY )
9        INSERT dbo.foo VALUES (1)
10                            ,(2)
11     ) by mydb;

ODBC_50: Executed: on connection 1
drop table if exists foo CREATE TABLE dbo.foo (bar int PRIMARY KEY ) INSERT dbo.foo VALUES (1) ,(2)

ODBC: 2 row(s) affected by INSERT/UPDATE/DELETE or other statement.
12
13     execute (
14       INSERT dbo.foo VALUES (3)   /*one insert statement inserting two rows throws expected error*/
15                            ,(1)
16     ) by mydb;

ODBC_51: Executed: on connection 1
INSERT dbo.foo VALUES (3) ,(1)

ERROR: CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY
       KEY constraint 'PK__foo__DE90ECFF0DE5EB18'. Cannot insert duplicate key in object 'dbo.foo'. The
       duplicate key value is (1). : [Microsoft][SQL Server Native Client 11.0][SQL Server]The
       statement has been terminated.
17
18     %put &=sysrc  &=SYSDBMSG &=SYSDBRC &=sqlxrc &=sqlxmsg;
SYsrc=0  SYSDBMSG=ODBC: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY
constraint 'PK__foo__DE90ECFF0DE5EB18'. Cannot insert duplicate key in object 'dbo.foo'. The duplicate
key value is (1). : [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been
terminated.  SYSDBRC=23000 SQLXRC=23000 SQLXMSG=[Microsoft][SQL Server Native Client 11.0][SQL
Server]Violation of PRIMARY KEY constraint 'PK__foo__DE90ECFF0DE5EB18'. Cannot insert duplicate key in
object 'dbo.foo'. The duplicate key value is (1). : [Microsoft][SQL Server Native Client 11.0][SQL
Server]The statement has been terminated.
19
20     disconnect from mydb;
21   quit;
NOTE: The SAS System stopped processing this step because of errors.
22
23


ODBC: AUTOCOMMIT is NO for connection 1
ODBC: AUTOCOMMIT turned ON for connection id 1

ODBC_52: Prepared: on connection 1
SELECT * FROM "dbo"."foo"

24   data _null_ ;
25     set sql.foo ;
26     put bar= ;
27   run ;


ODBC_53: Executed: on connection 1
Prepared statement ODBC_52

bar=1
bar=2
NOTE: There were 2 observations read from the data set SQL.foo.

28
29
30   *vs:  ;
31
32   *Example 5 ;
33   proc sql;
34     connect using sql as mydb;
ODBC: AUTOCOMMIT is YES for connection 1
35
36     execute (
37       drop table if exists foo
38       CREATE TABLE dbo.foo (bar int PRIMARY KEY )
39       INSERT dbo.foo VALUES (1)
40                            ,(2)
41     ) by mydb;

ODBC_54: Executed: on connection 1
drop table if exists foo CREATE TABLE dbo.foo (bar int PRIMARY KEY ) INSERT dbo.foo VALUES (1) ,(2)

ODBC: 2 row(s) affected by INSERT/UPDATE/DELETE or other statement.
42
43     execute (
44       INSERT dbo.foo VALUES (3)
45       INSERT dbo.foo VALUES (1) /*second insert statements does not throw expected error*/
46     ) by mydb;

ODBC_55: Executed: on connection 1
INSERT dbo.foo VALUES (3) INSERT dbo.foo VALUES (1)

ODBC: 1 row(s) affected by INSERT/UPDATE/DELETE or other statement.
47
48     %put &=sysrc  &=SYSDBMSG &=SYSDBRC &=sqlxrc &=sqlxmsg;
SYsrc=0  SYSDBMSG= SYSDBRC=0 SQLXRC=0 SQLXMSG=
49
50     disconnect from mydb;
51   quit;

52
53
ODBC: AUTOCOMMIT is NO for connection 1
ODBC: AUTOCOMMIT turned ON for connection id 1

ODBC_56: Prepared: on connection 1
SELECT * FROM "dbo"."foo"

54   data _null_ ;
55     set sql.foo ;
56     put bar= ;
57   run ;


ODBC_57: Executed: on connection 1
Prepared statement ODBC_56

bar=1
bar=2
bar=3
NOTE: There were 3 observations read from the data set SQL.foo.

I'll send it in as a bug report to tech support, and see if they agree it's a defect.  The data that ends up in the database from all these examples is correct.  But it's a bit scary that  it looks like SAS isn't relaying an error message thrown by the database.

[Tom]

So what is the actual valid syntax for that foreign SQL CREATE TABLE statement you are trying to execute?

What does that dialect of SQL use to indicate the end of a statement? Isn't it a semicolon?

Why aren't the INSERT statements separate statements?

  execute (
    drop table if exists foo ;
    CREATE TABLE dbo.foo (bar int PRIMARY KEY ) ;
    INSERT dbo.foo VALUES (1) ;
    INSERT dbo.foo VALUES (2);
  ) by mydb;

I suspect that if you want SAS to be able to trap the error messages from individual statements you should send them the statements individually.  Or perhaps build some type of procedure in that databases language that runs multiple statements and aggregates any errors they generate.

[Quentin]

The semi-colon statement delimiter is part of the ANSI standard but not required by MS SQL Server / t-sql for the most part. https://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server  Adding the semicolon didn't change any behavior.

Yes, if I submit one insert statement per execute block, I will get a log message back from each statement.  And I would hope if I used a transaction, I could get a message from the transaction.

But from the SAS side, since the EXECUTE statement allows you to submit a block of code to the remote database (and that block will often have multiple statements), it seems fair to expect that SAS will return any errors that the database throws from executing that block of code.  If you can't trust SAS to return database error messages, I'm not sure how you could feel good about using SAS to talk to a database.

[jimbarbour]

Hi, @Quentin,

Well, this sure sounds like a bug to me.  If an error occurs anywhere along the way, SAS should pass that back via the log.

I forget, have you tried running with the following options in effect?  Sometimes these can shake loose more information about what's going on between SAS and the DBMS:

OPTIONS 	SASTRACE		=	",,,ds";
OPTIONS 	SASTRACELOC		=	SASLOG;
OPTIONS 	NOSTSUFFIX;
OPTIONS 	MsgLevel		=	I;
OPTIONS		DEBUG			=	DBMS_SELECT;

Jim

[Quentin · Accepted solution]

Preliminary response from tech support is that the EXECUTE statement is not designed to send multiple statements to the RDBMS.  Each EXECUTE statement should only be used to send a single statement to the RDBMS.

So even though EXECUTE was able to send multiple statements in this case, I guess it's 'off-label use' and therefore the lack of proper logging is unlikely to be filed as a defect.

UPDATE: final response from tech support said that EXECUTE can be used to send multiple statements, but the logging doesn't work (as shown here).  So I still see this as a defect, but I don't think they were convinced. They seemed to say it's working fine; if you want the logging to work, only send one SQL statement per EXECUTE statement.

From the docs: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0pj5uu3i328pmn1fackclh2xnd9.htm

I read "Sends DBMS-specific, non-query SQL statements to the DBMS" and "The EXECUTE statement sends dynamic non-query, DBMS-specific SQL statements to the DBMS and processes those statements" to imply that an EXECUTE block sending multiple statements is allowed.

But in the syntax section, it does use singular throughout for the required argument:

EXECUTE (dbms-specific-sql-statement) BY dbms-name | alias;

(dbms-specific-sql-statement)

a dynamic non-query, DBMS-specific SQL statement. This argument is required and must be enclosed in parentheses. The SQL statement might be case sensitive, depending on your DBMS, and it is passed to the DBMS exactly as you enter it.

On some DBMSs, this argument can be a DBMS stored procedure. However, stored procedures with output parameters are not supported in the SQL pass-through facility. Furthermore, if the stored procedure contains more than one query, only the first query is processed.

Any return code or message that is generated by the DBMS is available in the macro variables SQLXRC and SQLXMSG after the statement executes. See Macro Variables for Relational Databases for more information about these macro variables.