When you insert data into a Microsoft SQL Server table that contains a SMALLMONEY data type, you might receive the following message:

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:

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

Product Family	Product	System	SAS Release
			Reported	Fixed*
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 = "

Type:	Usage Note
Priority:

Date Modified:	2020-02-04 07:49:22
Date Created:	2020-01-29 03:23:07