Usage Note 65469: "CLI execute error...Arithmetic overflow error..." occurs when you insert data into a Microsoft SQL Server table that contains a SMALLMONEY data type
When you insert data into a Microsoft SQL Server table that contains a SMALLMONEY data type, you might receive the following message:
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Arithmetic overflow error for type smallmoney, value = <value.fraction>
You receive this message when the value is outside of the range supported with the SMALLMONEY data type in SQL Server: - 214,748.3648 to 214,748.3647.
To work around the problem, change the data type to MONEY or to another Microsoft SQL Server data type that has a larger range of values.
When you copy a SAS table that contains columns with currency formats such as DOLLARw.d and EUROw.d. into Microsoft SQL Server, the MONEY or SMALLMONEY data type is used for those columns. The exact data type is selected based on information obtained from the Microsoft SQL Server client driver, as well as the width of the SAS currency format. This behavior can lead to the error described above.
Format DOLLAR6. produces a SMALLMONEY data type, whereas format DOLLAR7. produces a MONEY data type. The data type can be overridden with the DBTYPE= table option, as shown here:
LIBNAME mssql sqlsvr dsn=myMSSQL user=myuser password=mypw;
data mssql.new_table(DBTYPE=(val='MONEY'));
format val DOLLAR6.;
val=215000;
run;
Using the code above, the Microsoft SQL Server table mssql.new_table is created with one column named 'val' with data type MONEY.
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to Microsoft SQL Server | Microsoft® Windows® for x64 | 9.4 TS1M6 | |
| Microsoft Windows 8 Enterprise 32-bit | 9.4 TS1M6 | |
| Microsoft Windows 8 Enterprise x64 | 9.4 TS1M6 | |
| Microsoft Windows 8 Pro 32-bit | 9.4 TS1M6 | |
| Microsoft Windows 8 Pro x64 | 9.4 TS1M6 | |
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4 TS1M6 | |
| Microsoft Windows 8.1 Enterprise x64 | 9.4 TS1M6 | |
| Microsoft Windows 8.1 Pro 32-bit | 9.4 TS1M6 | |
| Microsoft Windows 8.1 Pro x64 | 9.4 TS1M6 | |
| Microsoft Windows 10 | 9.4 TS1M6 | |
| Microsoft Windows Server 2012 Datacenter | 9.4 TS1M6 | |
| Microsoft Windows Server 2012 R2 Datacenter | 9.4 TS1M6 | |
| Microsoft Windows Server 2012 R2 Std | 9.4 TS1M6 | |
| Microsoft Windows Server 2012 Std | 9.4 TS1M6 | |
| Microsoft Windows Server 2016 | 9.4 TS1M6 | |
| Microsoft Windows Server 2019 | 9.4 TS1M6 | |
| Windows 7 Enterprise 32 bit | 9.4 TS1M6 | |
| Windows 7 Enterprise x64 | 9.4 TS1M6 | |
| Windows 7 Home Premium 32 bit | 9.4 TS1M6 | |
| Windows 7 Home Premium x64 | 9.4 TS1M6 | |
| Windows 7 Professional 32 bit | 9.4 TS1M6 | |
| Windows 7 Professional x64 | 9.4 TS1M6 | |
| Windows 7 Ultimate 32 bit | 9.4 TS1M6 | |
| Windows 7 Ultimate x64 | 9.4 TS1M6 | |
| 64-bit Enabled AIX | 9.4 TS1M6 | |
| 64-bit Enabled Solaris | 9.4 TS1M6 | |
| HP-UX IPF | 9.4 TS1M6 | |
| Linux for x64 | 9.4 TS1M6 | |
| Solaris for x64 | 9.4 TS1M6 | |
*
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.
When you insert data into a Microsoft SQL Server table that contains a SMALLMONEY data type, you might receive this message: "ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Arithmetic overflow error for type smallmoney, value =
"
| Date Modified: | 2020-02-04 07:49:22 |
| Date Created: | 2020-01-29 03:23:07 |