Error: Numeric Value out of Range ODBC SQL Server

Question

I'm trying to append data to an ODBC SQL Server from a table and I am receiving the error: "DataWrap2ODBC::SendBatch: [Microsoft][ODBC SQL Server Driver]Numeric value out of range".

There are no other errors or warnings thrown.

Two other tables that I appended before this one worked without an issue and have the same structure of data/fields.

I also checked to make sure that the dates I have were not less than 2021 or greater than 2023 in case that was causing the numeric value issue.

Is there anything else that could be possibly leading to this issue?

Its strange because the first 20,000 records wrote successfully, but the last 20,000 failed.

I have an auto field tool at the beginning of my workflow, and I have a tool that converts all of my string dates to date-time fields.

Any help would be greatly appreciated, thank you in advance.

Miles_Waller · Answer

I ran into this issue myself and it was caused by constraints on the destination field in the database. My DBA changed the data type for one of the numeric fields I was trying to upload to numeric(32,16), and it solved the problem, where I was able to load data from a Double data type in Alteryx.

livingja · Answer

That's another part that I can't quite pin down because I have my fields with the highest amount of numbers set to Int32. My numbers with decimals are set to Double and my single digits are set to Byte. I'm wondering if its possible that the initial table I created to append this additional data to had slightly different field types in its Alteryx workflow and is thus causing the collective table in SQL to have issues appending the larger storage data types? I'm not sure it would work like that or not.

alisonpitt · Answer

I believe that the "numeric value out of range" error happens when you're trying to store a number in a field whose data type is too small for the number you're trying to store. e.g. if your data type is INT16 (max 32,767) and you're trying to store 12,345,678. So it may be the actual values vs your SQL table configuration, not the structure, that's giving you the issue?