Arithmetic overflow error converting expression to data type smallint

Question

i am using sqlserver 2019

hi,

I have started getting this message, from a stored procedure, which is not repeating, it comes after now and then, or sometimes it does not come at all. (while testing: I kept stored procedure  and parameter same).

Msg 8115, Level 16, State 2, Procedure uspMS, Line 11 [Batch Start Line 0]
Arithmetic overflow error converting expression to data type smallint.
The statement has been terminated.
Msg 8115, Level 16, State 2, Procedure uspMS, Line 51 [Batch Start Line 0]
Arithmetic overflow error converting expression to data type smallint.
The statement has been terminated.
Msg 8115, Level 16, State 2, Procedure uspMS, Line 101 [Batch Start Line 0]
Arithmetic overflow error converting expression to data type smallint.
The statement has been terminated.
Msg 8115, Level 16, State 2, Procedure uspMS, Line 156 [Batch Start Line 0]
Arithmetic overflow error converting expression to data type smallint.
The statement has been terminated.
Msg 8115, Level 16, State 2, Procedure uspMS, Line 177 [Batch Start Line 0]
Arithmetic overflow error converting expression to data type smallint.
The statement has been terminated.

Completion time: 2023-02-19T16:54:41.9293756+05:30

 

INSERT INTO KKR (BridgID,Remarks,Status)
SELECT DISTINCT BridgID,Remarks,1
FROM
kkrMain where kkrmainid=23

when i update the stored procedure which has the above code , it starts running properly, and after sometimes it show it again. so i have decided to put "WITH RECOMPILE" to see if it works. but pls consider above question with out recompile

yours sincerly

 

Answer 1

Arithmetic overflow error converting expression to data type smallint.

As the message says, you get the same with

select convert(smallint, 100000)

But without knowing your data and code it's impossible to say what it causes,

Answer 2

Double check triggers on your table and show the whole procedure code for investigation.