I'm trying to implement a query that uploads data into SQL Server table using OpenRowSet.
Occasionally, I run into a file that generates an error message:
8115, Arithmetic overflow error converting expression to data type nvarchar
My research indicates that this is generally caused by trying to stuff too long of a string into an nvarchar field. I have reviewed the data in Excel, and cannot find any field where the number of characters in the a cell exceed the number of characters defined for the field in the destination table. All of the fields that are currency in the spreadsheet are being imported into fields defined as Float in the SQL Server. But there are a number of columns in the Excel file which contains what looks like numbers but should actually be represented as text (thus the import into nvarchar(x) fields).
I'm assuming that either Excel or SQL Server is experiencing a data representation issue and although it is formatting the numbers display properly in Excel, the values are actually being stored with an excessive number of decimals, and when SQL tries to convert to nvarchar it is doing so for the actual value in the field, not the value that is displayed in the spreadsheet.
So, how do I limit the number of digits that are being imported from Excel into these nvarchar fields.