The following error messages may be issued when inserting into Sybase using SAS/ACCESS^® Interface to Sybase using REREAD_EXPOSRE=YES.

ERROR: MEMBER level locking for UPDATE, RANDOM or multiple-sequential-pass access is not supported by this engine.

ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain exclusive access to the data set. This statement will not execute as the SQL option UNDO_POLICY=REQUIRED is in effect.

SAS/ACCESS Interface to Sybase by default (due to compatibility with V6) is in an AUTOCOMMIT mode. To remain in "autocommit" mode using proc sql, you must specify UNDO_POLICY=NONE (implies autocommit), or by specifying DBCOMMIT= <something greater than 1> specifies that the Sybase engine is NOT in autocommit.

To resolve this problem, either specify AUTOCOMMIT=NO or specify the proc sql option UNDO_POLICY=NONE allows updates within proc sql to happen with the default UNDO_POLICY=REQUIRED proc sql option.

Below is an example that demonstrates the error and the resolution:

libname syblib sybase user=xxxx pass=xxxx server=ASE_16 dbgen_name=SAS connection=unique reread_exposure=yes;

  data syblib.testjg_1; x=1; y=2; z=3; a='a'; b='b'; run;

NOTE: The data set SYBLIB.testjg_1 has 1 observation and 5 variables.
NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.01 seconds

proc sql; insert into syblib.testjg_1 values (1,1,1,'2', '2');

ERROR: MEMBER level locking for UPDATE, RANDOM or multiple-sequential-pass access is not supported by this engine. ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain exclusive access to the data set. This statement will not execute as the SQL option UNDO_POLICY=REQUIRED is in effect.

libname syblib sybase user=xxx pass=yyyyy server=ASE_16 dbgen_name=SAS connection=unique reread_exposure=yes dbcommit=1000;

proc sql; insert into syblib.testjg_1 values (1,1,1,'2', '2');

NOTE: 1 row was inserted into SYBLIB.testjg_1.