Problem Note 67051: You might get incorrect values when using the OLEDB engine with the MSDASQL provider to read numeric type data from the PostgreSQL database
You might get incorrect values when you use SAS/ACCESS® Interface to OLE DB with the MSDASQL provider to read a numeric type data from a PostgreSQL database. SAS/ACCESS Interface to OLE DB might return the wrong value, which was carried over from the previous data row. This issue occurs when the MSDASQL provider fails to convert a numeric value from the database.
In this scenario, you might see a warning message similar to the following:
WARNING: During read: Cannot convert data for column
In some cases, you might need to run the following OPTIONS statement to turn on tracing in order to see the message:
options sastrace='d,,d,d' sastraceloc=saslog;
The problem is triggered by the MSDASQL provider failing to convert some numeric values, such as zero, from the database. This issue is considered an MSDASQL provider problem.
As a workaround, you can use explicit SQL pass-through and a CAST function to avoid the problem.
Here is an example:
proc sql;
connect to oledb( connection-parameters );
select * from connection to oledb
(select ..., cast("column-name" as double precision), ... from table );
quit;
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to OLE DB | Windows 7 Ultimate x64 | 9.4_M5 | | 9.4 TS1M5 | |
| Windows 7 Ultimate 32 bit | 9.4_M5 | | 9.4 TS1M5 | |
| Windows 7 Professional x64 | 9.4_M5 | | 9.4 TS1M5 | |
| Windows 7 Professional 32 bit | 9.4_M5 | | 9.4 TS1M5 | |
| Windows 7 Home Premium x64 | 9.4_M5 | | 9.4 TS1M5 | |
| Windows 7 Home Premium 32 bit | 9.4_M5 | | 9.4 TS1M5 | |
| Windows 7 Enterprise x64 | 9.4_M5 | | 9.4 TS1M5 | |
| Windows 7 Enterprise 32 bit | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows Server 2016 | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows Server 2012 Std | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows Server 2012 R2 Std | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows Server 2012 R2 Datacenter | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows Server 2012 Datacenter | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows Server 2008 for x64 | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows Server 2008 R2 | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows Server 2008 | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows 10 | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows 8.1 Pro x64 | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows 8.1 Pro 32-bit | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows 8.1 Enterprise x64 | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows 8 Pro x64 | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows 8 Pro 32-bit | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows 8 Enterprise x64 | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft Windows 8 Enterprise 32-bit | 9.4_M5 | | 9.4 TS1M5 | |
| Microsoft® Windows® for x64 | 9.4_M5 | | 9.4 TS1M5 | |
*
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.
In this scenario, the SAS OLEDB engine might return an incorrect value, which was carried over from the previous data row. This issue occurs when the MSDASQL provider fails to convert a numeric value from the database.
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2020-12-11 08:30:56 |
| Date Created: | 2020-12-07 10:07:15 |