Using an UPDATE SQL statement with SAS/ACCESS Interface to Microsoft SQL Server might result in an error similar to the following:

The error might occur if you are using the DataDirect 7.1.x or later driver. Previous versions of the driver, such as 7.0.x, do not exhibit this behavior.

Workaround

SAS^® 9.4M3 (TS1M3):

There is only one way to avoid this issue. You must add the following option in the odbc.ini file under the DSN entry:

In Windows, you can set this value in the Extended Options field of the ODBC SQL Server Wire Protocol Driver Setup dialog box, as shown below:

Note: This workaround at SAS 9.4M3 has been known to cause poor "Read" performance issues when querying from SQL Server to SAS. It is highly recommended that you upgrade to the latest release of SAS and apply the following hot fix and solution. 

SAS^® 9.4M4 (TS1M4) to SAS^® 9.4M6 (TS1M6):

You must apply the appropriate hot fix for your SAS version to update the driver and then set the following:

You can set this option either in the odbc.ini file or in the user interface (on Windows), the same as specified for SAS 9.4M3 above.

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

Operating System and Release Information

A fix for this issue for DataDirect ODBC Driver for Microsoft SQL Server 9.43 is available at:

A fix for this issue for DataDirect ODBC Driver for Microsoft SQL Server 9.42 is available at:

A fix for this issue for DataDirect ODBC Driver for Microsoft SQL Server 9.41 is available at: