Arithmetic Overflow error when SP run from SSA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Arithmetic Overflow error when SP run from SSA

Author

Topic

rkapur
Starting Member

9 Posts

Posted - 2011-03-31 : 14:04:33

I have a stored procedure (sp_gl_gltransv) that fails intermittently when run from the SQL Server Agent (SSA). It always runs successfully when run directly from the SQL Server Management Studio (SSMS).

For debugging, I added the capture and display of @@ERROR after every SQL statement. When run from SSA, all the SQL statements ran successfully with @@ERROR = 0. At exit, the stored procedure (inexplicably) reports an Arithmetic overflow. The following logs were generated in SSA. How can I debug/fix this?

---------------------------------------------------
Executed as user: NT AUTHORITY\SYSTEM. START: Mar 31 2011 10:14AM++ [SQLSTATE 01000] (Message 0)

Running: sp_gl_gltransvMar 31 2011 10:14AM [SQLSTATE 01000] (Message 0)

insert complete ERROR:0 [SQLSTATE 01000] (Message 0)
Index Creation Complete ERROR:0 [SQLSTATE 01000] (Message 0)
First Update of MONTH_SORT Complete ERROR:0 [SQLSTATE 01000] (Message 0)
Second Update of MONTH_SORT Complete ERROR:0 [SQLSTATE 01000] (Message 0)
Update GL_MONTH_STATUS complete ERROR:0 [SQLSTATE 01000] (Message 0)
Update LOOKUP_OWNER_CC_DESCRIPTION Complete ERROR:0 [SQLSTATE 01000] (Message 0)
Update OWNER_LEVEL_DESCRIPTION Unknown complete ERROR:0 [SQLSTATE 01000] (Message 0)
Update COST_CENTER_LEVEL_DESCRIPTION unknown complete ERROR:0 [SQLSTATE 01000] (Message 0)

Arithmetic overflow error converting int to data type numeric. [SQLSTATE 22003] (Error 8115). The step failed.

The stored procedure did its job and all target rows were present and all updates in the stored procedure were successful. That is, all SQLs ran successfully. (If needed, I can post the stored procedure to the group).

The data fields of the source and target of the insert are identical as far as I can tell (with the exception of a DECIMAL(18,2) to NUMERIC(18,2), which should be fine). Conversions in other SQLs also seem to be fine.

Version Detail
--------------------
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 14:07:54

We definitely need to see the code plus whatever is in the job step.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rkapur
Starting Member

9 Posts

Posted - 2011-03-31 : 15:10:12

I am unable to post the SP code. IT is only a couple of hundred lines. After pasting the code, 'Post New Reply' and 'Preview' do not work. The popup window says the page cannot be displayed.

Is there a way to upload the file? or posting the code in a way that will work?

Thanks!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 15:15:01

Wow, a couple of hundred lines? I wouldn't say "only" for that. It is very unlikely that we can help you with such a gigantic stored procedure. You're going to need to do some troubleshooting to figure out which portion of code is breaking, and then we can help you fix it. Add some debug code to it so that output gets put into the job history to help you narrow it down.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rkapur
Starting Member

9 Posts

Posted - 2011-03-31 : 17:07:01

I have already added the debug code in the stored procedure. I am printing @@ERROR after each SQL statement.... please look at the output from my first post.

The trouble is, none of the SQLs are failing. They all return @@ERROR = 0. Still the stored procedure returns 'Arithmetic overflow error converting int to data type numeric. [SQLSTATE 22003] (Error 8115).' upon exit.

Does the @@ERROR = 0 capture the SQLSTATE? or is there another way to capture SQLSTATE and Error codes after an SQL statement?

Thanks!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 17:13:32

@@ERROR is not enough debug code. You can get the other information about the error if you use TRY/CATCH.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-31 : 19:04:02

To add to what Tara suggested about try/catch, try/catch lets you see the LINE on which the error happened. Look for examples on this page, and specifically for ERROR_LINE(). http://msdn.microsoft.com/en-us/library/ms175976.aspx

Also, the error message you are getting indicates that you are trying to store a number into a decimal variable, which is too large for it to hold. Copy this code and run it and it will show the same error message. The largest number that @d can store is 99.99

declare @d decimal(4,2);
declare @f float;

set @f = 100.75;
select @f;

set @d = @f; -- will cause error
set @d = cast(@f as decimal(3,2)); -- will cause error
set @d = 100.75; -- will cause error

rkapur
Starting Member

9 Posts

Posted - 2011-04-01 : 00:18:38

I have put the entire stored procedure logic/SQLs in 'BEGIN TRY/END TRY' block. I have coded the CATCH block as follows...

BEGIN CATCH
print 'Inside CATCH'

SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

I ran the stored procedure from SSA. It worked the first time. I reran the SP again without any code changes. The second time it failed but never hit the CATCH block. That is no display of 'Inside CATCH' etc. I still got the Arithmetic Overflow error.

I have never seen this SP fail from SSMS.

In short.. all SQLs are running error free. However, when run from SSA, the SP intermittently exhibits Arithmetic Overflow at the time of exit. The code does not hit the CATCH block when the error occurs.

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-01 : 07:06:01

Only two thoughts come to mind:

a) the problem is not in the stored proc at all, it is somewhere else.

b) one or more of the parameters you are trying to the stored procedure is causing the overflow. For example, if you had a stored proc with a decimal(4,2) parameter, and you tried to pass in a value of 100.75, you will get this error message. BUT, you will not get to the try-catch because the exception would have been thrown even before the first BEGIN in the stored proc.

rkapur
Starting Member

9 Posts

Posted - 2011-04-01 : 10:27:52

The stored procedure does not have any parameters. So (b) cannot be the reason for failure.

How do I go about finding the problem outside the stored procedure? I do not know where to begin...

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-01 : 12:34:09

What's in the job step?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rkapur
Starting Member

9 Posts

Posted - 2011-04-01 : 13:06:53

The entire job has 8 steps that populate the staging tables and then the presentation layer of a data warehouse from a transactional finance system. The step that is failing is really an independent/standalone step that does not interfere with the rest of the ETL steps 1 thru 7. Steps 1-7 have never failed.

Step 8 first SELECTS/INSERTS a few fields from a transaction table into a local table. Then it has half a dozen update statements that copy description fields from a lookup table into the newly created/inserted table.

This step was really step number 4. Since it was independent, I was able to move it to the end. Step 8 is doing the job and then exiting with an Arithmetic Overflow. The users do not see the effect of this failure because the data is all there. Still it is a loose end that I do not like.

Regards.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-01 : 13:18:29

It's really hard for us to help with the information you've provided. Without seeing code, we can't really help you with this. If it's step 8 that's failing, then that's the code we need to see.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rkapur
Starting Member

9 Posts

Posted - 2011-04-01 : 16:46:57

I am unable to post the 200 line stored procedure to the forum. This is the same SP I was trying to post yesterday. Please tell me if there is a way to get it across to you... Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-01 : 16:55:59

You missed my point. We need the code from the job step.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rkapur
Starting Member

9 Posts

Posted - 2011-04-04 : 11:05:20

I am including code for the jobs steps 7 thru the end. Step 8 is the one failing with Arithmetic Overflow... Thank you so much for looking into this. Regards!

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [SECURITY - POST AC MAP] Script Date: 04/04/2011 09:59:24 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SECURITY - POST AC MAP',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC [dbo].[sp_security_post_ac_map]',
@database_name=N'Lawson_Reporting',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [ETL - GENERAL LEDGER - GLTRANSV Load] Script Date: 04/04/2011 09:59:24 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ETL - GENERAL LEDGER - GLTRANSV Load',
@step_id=8,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC [dbo].[sp_gl_run_3_gltransv_load]',
@database_name=N'Lawson_Reporting',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily Run',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20090930,
@active_end_date=99991231,
@active_start_time=40000,
@active_end_time=235959,
@schedule_uid=N'b6339b36-a6b2-442f-8c26-977ddaaf9538'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-04 : 11:23:54

Hmmm, I thought you said there was more in the job step that is failing. Is it only a stored procedure call in there?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rkapur
Starting Member

9 Posts

Posted - 2011-04-04 : 12:51:42

The entire ETL is a set of 8 procedures. The job was configured using SSA within SS Management studio. The job steps are therefore straight calls (without parameters) to the 8 stored procedures. Only the 8th one is failing as it exits. The job step code (that I posted) was generated from SSA (by rt clicking on the job name). I apologize if I am not understanding the information you are asking me to send. My skills in SQL Server are rather limited. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources