The following statements fail and return an error in SAS/ACCESS Interface to Snowflake:

• DATA step MODIFY
• PROC SQL UPDATE (with DBIDIRECTEXEC=NO)
• PROC SQL DELETE (with DBIDIRECTEXEC=NO)

These statements require positional cursor support, which is not currently available in the Snowflake ODBC driver.

Click the Hot Fix tab in this note to access the hot fix for this issue.

After you install the hot fix, these steps are supported as long as a primary key is available in the table.

This process requires the table to have a unique primary key, which is currently not enforced by Snowflake. Therefore, you must ensure the uniqueness of the key. In addition, if the table does not contain a primary key or if it is detected that an update was applied to multiple rows, the statement fails.

Adding UPDATE_MULT_ROWS=YES as a LIBNAME option allows updates to multiple rows.

The Full Code tab contains an example of how to create a sample table and then update it successfully after applying the associated hot fix.

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	SAS/ACCESS Interface to Snowflake	Microsoft® Windows® for x64	9.41	9.42	9.4 TS1M6	9.4 TS1M7
		Linux for x64	9.41	9.42	9.4 TS1M6	9.4 TS1M7

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

Here is an example of how to create a sample table and then update it successfully after applying the associated hot fix:

---

libname invty sasiosnf user=myusr1 server=mysrv1 database=test reread_exposure=no;

proc sql;
drop table invty.STOCK23;
quit;

/*Create a DBMS table with a primary key.*/
data invty.STOCK23(drop=PARTNO DBTYPE=(RECDATE="date not null, primary key(RECDATE)"));
input PARTNO $ DESCX $ INSTOCK @20 RECDATE date7. @29 PRICE;
format RECDATE date7.;
datalines;
K89R  seal     34  27jul95  245.00
M447  sander   98  20jun95   45.88
LK43  filter  121  19may96   10.99
MN21  brace    43  10aug96   27.87
BC85  clamp    80  16aug96    9.55
KJ66  cutter    6  20mar96   24.50
UYN7  rod     211  18jun96   19.77
JD03  switch  383  09jan97   13.99
BV1I  timer    26  03jan97   34.50
;

/* Updates the STOCK23 table.*/
proc sql;
update invty.STOCK23 set price=price*1.1 where INSTOCK > 50;
quit;

Viya on Linux: An update for this issue is available for SAS Viya 3.5. For instructions on how to access and apply software updates, see the Updating Your SAS Viya software section in the SAS Viya 3.5 for Linux Deployment Guide at

http://documentation.sas.com/?softwareId=administration&softwareVersion=3.5&softwareContextId=softwareUpdates

---

A fix for this issue for SAS/ACCESS Interface to Snowflake 9.41 is available at:

https://tshf.sas.com/techsup/download/hotfix/HF2/H6S.html#66040

---

Type:	Usage Note
Priority:

Date Modified:	2020-05-26 16:35:43
Date Created:	2020-05-26 14:07:10