October 17, 2011 at 2:35 pm
According to the error message I am getting an Arithmetic overflow error, Msg 8115, Level 16 at line number 147. Line #147 is and [Period] = (Select [hist_Period] from @hist_temp where idx ='1').
I am creating a table variable with an index so that I can do a join with the first and the last entry (there are only 2) because I might get data for month 12 year 2011 and month 1 year 2012 for example.
Here is the offending section from the stored procedure with a line added to spit out the contents of the table variable. If I run just this section I get the results shown below
1020111
1120112
and no error message. If I take out the Select * from @hist_temp and run this section it completes without throwing an error. (2 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
If I create the stored procedure and run it, it throws the Msg 8115, error.
Stored Procedure code section throwing error:
/*** get rid of the months in the upload file to update the history ***/
Declare @hist_temp table
(hist_Period int,
hist_year int,
idx int identity(1,1))
Insert into @hist_temp (hist_Period,hist_year)
(Select distinct [Period] , [Year] from FINBPA.zzz_ERP_LN_BulkLoad)
Select * from @hist_temp
DELETE FROM [FINBPA].[FINBPA].[zzz_All_History]
WHERE Source_System = 'ERP-LN'
and [Period] = (Select [hist_Period] from @hist_temp where idx ='1')
and [Year] = (Select [hist_year] from @hist_temp where idx = '1')
DELETE FROM [FINBPA].[FINBPA].[zzz_All_History]
WHERE Source_System = 'ERP-LN'
and [Period] = (Select [hist_Period] from @hist_temp where idx ='2')
and [Year] = (Select [hist_year] from @hist_temp where idx = '2')
/* Now update the history */
I have tried putting the idx = '1' with no quotes, with single quotes, I have tried Top 1 and that seems to work but then I get the arithmetic error when I try to do anything to get the last or bottom record from the table variable. AVG does not throw an error but only gives me the bottom month and bottom year. I suppose I can try switching so that I get the Top 1 month with the AVG year and the AVG month with the Top 1 year and skip the index. But I'd rather figure out what is causing the error in the current code.
October 17, 2011 at 2:50 pm
Never mind. The line numbers in the error message are what is misleading. The arithmetic overflow was occurring further down in the stored procedure.
October 17, 2011 at 4:45 pm
Thanks for the update and glad you found it.
Double Clicking on the error should take you to the line. It might not be always exact, but it should get you closer.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply