Problem Note 64616: A DataDirect driver causes a data integrity issue after you update a DATETIME column in an SQL Server table while using the SQLSRV LIBNAME engine
 |  |  |  |  |
You update a Microsoft SQL Server table by using SAS/ACCESS® Interface to Microsoft SQL Server and the SQL procedure. You then see a note in the log that confirms that the row was updated. However, the data in the row is not updated. There is no indication that the update did not work as expected. In some cases, the update does complete successfully.
This issue is caused by the underlying DataDirect driver. SAS is working with Progress to resolve the issue.
The issue occurs when all of the following are true:
- The Microsoft SQL Server version is 2016 or later.
- The column in the database has a DATETIME definition.
- The SQLSVR LIBNAME engine is used.
- The CURSOR_TYPE=DYNAMIC option is not in the LIBNAME statement.
In the meantime, you can use one of the following workarounds:
- Set EnableScrollableCursors=4 in the obdc.ini file. Then add the CURSOR_TYPE=DYNAMIC option to the LIBNAME statement.
- Use the SQL pass-through facility.
- Change the data type in the SQL Server table from DATETIME to DATETIME2.
- Change the Compatibility Level setting of the SQL Server database to SQL Server 2014 (120). This setting is database-specific, so it would need to be done only for the database that the data server is accessing.
- Migrate the database by using the SQL Server Native Client driver. Deselect the advanced option Map MSS Datetime Type in the OpenEdge DB to SQL Server Utility in order to migrate OpenEdge DATETIME types to Microsoft SQL Server DATETIME2 types.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to Microsoft SQL Server | Microsoft Windows 8.1 Enterprise 32-bit | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 |
| Microsoft Windows 8.1 Pro 32-bit | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Pro x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows 10 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2008 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2008 R2 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2008 for x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 Datacenter | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 R2 Datacenter | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 R2 Std | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 Std | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2016 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Windows 7 Enterprise 32 bit | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Windows 7 Enterprise x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Windows 7 Home Premium 32 bit | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Windows 7 Home Premium x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Windows 7 Professional 32 bit | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Windows 7 Professional x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Windows 7 Ultimate 32 bit | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Windows 7 Ultimate x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| 64-bit Enabled AIX | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| 64-bit Enabled Solaris | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| HP-UX IPF | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Linux for x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Solaris for x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Enterprise 32-bit | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Enterprise x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Pro 32-bit | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Pro x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Enterprise x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
| Microsoft® Windows® for x64 | 9.43 | 9.45 | 9.4 TS1M5 | 9.4 TS1M7 | ||
A fix for this issue for DataDirect ODBC Driver for Amazon Redshift 9.43 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/E8C.html#64616A fix for this issue for DataDirect ODBC Driver for Greenplum 9.43 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/H9L.html#64616A fix for this issue for DataDirect ODBC Driver for Microsoft SQL Server 9.43 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/F1N.html#64616
When you update a DATETIME column in a Microsoft SQL Server table, the update is reported as completing successfully, but the data in the row is not updated.
| Type: | Problem Note |
| Priority: | high |
| Topic: | Data Management ==> Data Sources ==> External Databases ==> MS SQL Server Data Management ==> Manipulation and Transformation ==> Date and Time |
| Date Modified: | 2020-09-10 07:13:20 |
| Date Created: | 2019-08-14 04:11:08 |