December 19, 2013 at 6:54 am
I am at a loss - but also a bit of a novice. We have a SQL 2005 SP2 (A) with a linked server set up to a SQL 2008 SP2 server (B). A stored procedure on server A is scheduled to run every morning to use data from server A to update a table on server B. This has been working successfully until earlier this week when a value on table A became quite large (420,000,000). The destination field on table B is set up as float. The stored procedure is failing with an arithmetic overflow converting decimal to numeric. I have successfully saved the values directly into table B just by writing an Insert query on both server A and B. But the stored procedure consistently fails at this record. I have validated that it is this record by eliminating it from the data queried by the stored procedure, which then completes successfully saving all the remaining data.
I can't think what else to try! Never having worked with float data types before, I am not sure what complications they may cause. Any suggestions gratefully received.
December 20, 2013 at 12:53 pm
sneilson (12/19/2013)
I am at a loss - but also a bit of a novice. We have a SQL 2005 SP2 (A) with a linked server set up to a SQL 2008 SP2 server (B). A stored procedure on server A is scheduled to run every morning to use data from server A to update a table on server B. This has been working successfully until earlier this week when a value on table A became quite large (420,000,000). The destination field on table B is set up as float. The stored procedure is failing with an arithmetic overflow converting decimal to numeric. I have successfully saved the values directly into table B just by writing an Insert query on both server A and B. But the stored procedure consistently fails at this record. I have validated that it is this record by eliminating it from the data queried by the stored procedure, which then completes successfully saving all the remaining data.I can't think what else to try! Never having worked with float data types before, I am not sure what complications they may cause. Any suggestions gratefully received.
We'll need more information to help you with this. What is the data type of the column on server A? Does the stored proc do anything other than an INSERT INTO . . . SELECT FROM (i.e., are there any calculations or transformations in the proc)? If you could post the table definitions for both servers and the actual code of the proc, that would be very helpful, as would the actual error message you get.
It's very unusual to see an arithmetic overflow error converting decimal to numeric because those data types are functionally identical and have the same range. However, this kind of arithmetic overflow can occur when performing nesting an expression or function within another function - when the outer function requires a numeric argument but the inner function or expression returns a value that overflows the limits of that datatype, the implicit conversion fails.
Jason Wolfkill
December 23, 2013 at 2:21 am
The actual stored procedure is quite complex but essentially is picking up a money field multiplying by a decimal (23,10) and then calculating a percentage by multiplying by an integer and dividing by 100 and the result goes into the float field. The stored proc has been running successfully for 3 years until we added a product in Colombian Pesos with an exchange rate of 2800. So the money field is 150,000 but multiplying by the decimal exchange rate to get the currency rate results in 420,000,000 (a value that is also being written away to a float field). The percentage calculation is to get the probability that is also then written to a float field.
The actual error is
Arithmetic overflow error converting decimal to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.
The stored procedure is being run daily as a task, hence the 'step failed' message.
December 23, 2013 at 10:49 am
sneilson (12/23/2013)
The actual stored procedure is quite complex but essentially is picking up a money field multiplying by a decimal (23,10) and then calculating a percentage by multiplying by an integer and dividing by 100 and the result goes into the float field.
So your calculation looks like this?
[money] * [decimal(23,10)] * [integer] / 100
What is the value of that integer?
The stored proc has been running successfully for 3 years until we added a product in Colombian Pesos with an exchange rate of 2800. So the money field is 150,000 but multiplying by the decimal exchange rate to get the currency rate results in 420,000,000 (a value that is also being written away to a float field). The percentage calculation is to get the probability that is also then written to a float field.
If you exclude the data for that product, will the proc run correctly? Is the 2800 exchange rate the decimal(23,10) value in the formula above?
The actual error is
Arithmetic overflow error converting decimal to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.
The stored procedure is being run daily as a task, hence the 'step failed' message.
Can you run the stored procedure directly (e.g., from a query window in SSMS or VS)?
Are you *sure* that this is the full extent of the operations on these numbers? Are you sure that it's the formula you described above that causes the error and not some other operation?
My best guess at this point is that you have either a function that yields a result that overflows its output datatype (coincidentally, I've just written about that problem here) or an expression that overflows the data type of its target variable or column, but without seeing the code, I can't dig any deeper than that. Maybe you could post an anonymized version of your proc?
Jason Wolfkill
December 24, 2013 at 1:58 am
wolfkillj (12/23/2013)
sneilson (12/23/2013)
The actual stored procedure is quite complex but essentially is picking up a money field multiplying by a decimal (23,10) and then calculating a percentage by multiplying by an integer and dividing by 100 and the result goes into the float field.So your calculation looks like this?
[money] * [decimal(23,10)] * [integer] / 100
It does!
What is the value of that integer? The integer in this example was just 1.
The stored proc has been running successfully for 3 years until we added a product in Colombian Pesos with an exchange rate of 2800. So the money field is 150,000 but multiplying by the decimal exchange rate to get the currency rate results in 420,000,000 (a value that is also being written away to a float field). The percentage calculation is to get the probability that is also then written to a float field.
If you exclude the data for that product, will the proc run correctly? Is the 2800 exchange rate the decimal(23,10) value in the formula above? If I exclude the data for that product, the stored proc does run OK.
The actual error is
Arithmetic overflow error converting decimal to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.
The stored procedure is being run daily as a task, hence the 'step failed' message.
Can you run the stored procedure directly (e.g., from a query window in SSMS or VS)?
If I run the stored proc from a query window I get the same error; if I just plug the resulting values into the table it does save OK so it must be something in the calculation process that is overflowing the bounds of the data type, but not sure how to find it.
Are you *sure* that this is the full extent of the operations on these numbers? Are you sure that it's the formula you described above that causes the error and not some other operation?
My best guess at this point is that you have either a function that yields a result that overflows its output datatype (coincidentally, I've just written about that problem here) or an expression that overflows the data type of its target variable or column, but without seeing the code, I can't dig any deeper than that. Maybe you could post an anonymized version of your proc?
The problem with posting the code is that there is a huge amount of it! And you may well look at it and think what a rubbish coder I am! I have attached the stored proc though. Many thanks for any suggestions but otherwise I will read your post and see how that helps - but in the New YEar!
December 26, 2013 at 9:38 am
sneilson (12/24/2013)
wolfkillj (12/23/2013)
sneilson (12/23/2013)
The actual stored procedure is quite complex but essentially is picking up a money field multiplying by a decimal (23,10) and then calculating a percentage by multiplying by an integer and dividing by 100 and the result goes into the float field.So your calculation looks like this?
[money] * [decimal(23,10)] * [integer] / 100
It does!
What is the value of that integer? The integer in this example was just 1.
The stored proc has been running successfully for 3 years until we added a product in Colombian Pesos with an exchange rate of 2800. So the money field is 150,000 but multiplying by the decimal exchange rate to get the currency rate results in 420,000,000 (a value that is also being written away to a float field). The percentage calculation is to get the probability that is also then written to a float field.
If you exclude the data for that product, will the proc run correctly? Is the 2800 exchange rate the decimal(23,10) value in the formula above? If I exclude the data for that product, the stored proc does run OK.
The actual error is
Arithmetic overflow error converting decimal to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.
The stored procedure is being run daily as a task, hence the 'step failed' message.
Can you run the stored procedure directly (e.g., from a query window in SSMS or VS)?
If I run the stored proc from a query window I get the same error; if I just plug the resulting values into the table it does save OK so it must be something in the calculation process that is overflowing the bounds of the data type, but not sure how to find it.
Are you *sure* that this is the full extent of the operations on these numbers? Are you sure that it's the formula you described above that causes the error and not some other operation?
My best guess at this point is that you have either a function that yields a result that overflows its output datatype (coincidentally, I've just written about that problem here) or an expression that overflows the data type of its target variable or column, but without seeing the code, I can't dig any deeper than that. Maybe you could post an anonymized version of your proc?
The problem with posting the code is that there is a huge amount of it! And you may well look at it and think what a rubbish coder I am! I have attached the stored proc though. Many thanks for any suggestions but otherwise I will read your post and see how that helps - but in the New YEar!
Well, the cursor is probably unnecesary and could be dragging down your overall performance, but we can talk about that later. 😉
On a very cursory inspection of the code, I see one thing that could definitely cause the problem you experience. The SELECT statement in the cursor definition UNPIVOTs the BudgetAmt column from the several month## columns, which are all decimal(12,2) datatype because of the CONVERTs. Later on, your FETCH NEXT statement attempts to fetch a BudgetAmount value into the @value variable, which is declared as decimal(10,2). If the formula for the month## columns can yield a value greater than 99999999.99, an attempt to fetch that value into the @value variable will fail.
Using the example numbers of 150,000 and 2,800 that you provided, an integer greater than 23 will cause a result that overflows the decimal(10,2) type:
(150000.00 * 2800.00 * 24)/100 = 100800000.00 <-- this result has 9 digits to the left of the decimal point and will therefore overflow the decimal(10,2) type.
The quick way to see if this is the problem is to ALTER the stored proc to declare the @value variable as decimal(12,2). If it runs without error then, you know this was problem.
A similar error could occur here:
if @sbuExchange <> @leadexchange
set @value = (@value /@sbuexchange) * @leadexchange
All three variables involved in the expression in the SET statement are declared as decimal(10,2). When SQL Server performs division and multiplication on decimal values, it will type the result based on the precision and scale of the inputs. I determined that the result of the expression above will be typed as decimal(34,15), so again, if this calculation can yield a result greater than 99999999.99, an attempt to set @value (a decimal(10,2) data type) equal to that result will fail. If, after you've ALTERed the stored proc to declare @value as decimal(12,2) as suggested above, the proc still returns an error, try declaring @value as decimal(34,15). If it runs correctly then, you'll know that this is a problem.
One last thing to check would be the data type of the CMRF_VALUE columns in the target tables to be sure they can hold the result of ROUND(@value, 0) (which will also be a decimal(10,2) and could therefore hold values up to 99999999.99).
Lastly (for now), know that neither I nor most others who post in SSC forums will judge you negatively for posting code that needs work. This is a place to learn and share knowledge, and if fear of ridicule prevents people from seeking help with their code, we've failed as a community. So, if you want to take a stab at simplifying your stored proc (most likely by eliminating the cursor), open a new thread and post table definitions, sample data, and expected results (using the methods explained here[/url]. If you give us those resources, at least one or two of us will almost always come up with a solution that is more efficient and/or more elegant AND explain to you how it works.
Good luck with this, and Happy New Year!
Jason Wolfkill
December 30, 2013 at 3:15 am
Jason - you really are a star! Thank you so much for such a helpful, considerate post. I have to apologise; had I really taken the time to work through the stored procedure, I should have seen that for myself. In the run up to the hols, I went into panic mode! I have ALTERed the SP so that the decimals are all 16,2 but I won't set it to run again until later in the week when I can monitor it and adjust as necessary. I can't tell you how much I appreciate your constructive help and advice.
Happy New Year!
December 30, 2013 at 11:56 am
sneilson (12/30/2013)
Jason - you really are a star! Thank you so much for such a helpful, considerate post. I have to apologise; had I really taken the time to work through the stored procedure, I should have seen that for myself. In the run up to the hols, I went into panic mode! I have ALTERed the SP so that the decimals are all 16,2 but I won't set it to run again until later in the week when I can monitor it and adjust as necessary. I can't tell you how much I appreciate your constructive help and advice.Happy New Year!
:blush:
Really, though, I only did what at least a dozen others on this forum would happily do. Sometimes, it just takes a fresh pair of eyes to spot problems hiding in plain sight. Let us know how things work out with this proc, and Happy New Year to you, too!
Jason Wolfkill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply