Arithmetic overflow error converting expression to data type smallint

Question

Arithmetic overflow error converting expression to data type smallint

Rajesh Kumar Yadav 1

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

Viorel 122.5K Reputation points

2023-02-21T06:29:22.2733333+00:00

Probably there is a value which cannot be stored as smallint and varies even if the parameter is the same. (The allowed interval is -32,768 to 32,767). Show some details about the problematic script.

For investigations, maybe you can use the TRY-CATCH statements.
Anonymous

2023-02-21T06:56:59.38+00:00

You show an insert statement, but it doesn't show information about the data type.

For the difference between int, bigint, smallint, etc., you can refer to this official document.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver16
Rajesh Kumar Yadav 1 Reputation point

2023-02-22T08:35:28.5366667+00:00

this is the complet script

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

-- bridgeid is bigint, remarks is varchar(250) and status is smallint

and both table has same datatypes

-- mean while i changed status col to bigint , but of no avail.
Viorel 122.5K Reputation points

2023-02-22T09:14:44.1933333+00:00

Which line of the complete script is 11, 51, 101, 156, 177?
Rajesh Kumar Yadav 1 Reputation point

2023-02-23T05:22:56.61+00:00

the script i have given is from storedprocedure , and same type of scripts are there, which i have posted u. soon i will try to show the stored procedure also ( but i have to take permistion) can u join me on outlook meeting?
Anonymous

2023-03-10T08:27:32.17+00:00

Hi @Rajesh Kumar Yadav

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

2 answers

Your answer

Viorel 122.5K Reputation points

2023-02-21T06:29:22.2733333+00:00

Probably there is a value which cannot be stored as smallint and varies even if the parameter is the same. (The allowed interval is -32,768 to 32,767). Show some details about the problematic script.

For investigations, maybe you can use the TRY-CATCH statements.
Anonymous

2023-02-21T06:56:59.38+00:00

You show an insert statement, but it doesn't show information about the data type.

For the difference between int, bigint, smallint, etc., you can refer to this official document.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver16
Rajesh Kumar Yadav 1 Reputation point

2023-02-22T08:35:28.5366667+00:00

this is the complet script

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

-- bridgeid is bigint, remarks is varchar(250) and status is smallint

and both table has same datatypes

-- mean while i changed status col to bigint , but of no avail.
Viorel 122.5K Reputation points

2023-02-22T09:14:44.1933333+00:00

Which line of the complete script is 11, 51, 101, 156, 177?
Rajesh Kumar Yadav 1 Reputation point

2023-02-23T05:22:56.61+00:00

the script i have given is from storedprocedure , and same type of scripts are there, which i have posted u. soon i will try to show the stored procedure also ( but i have to take permistion) can u join me on outlook meeting?
Anonymous

2023-03-10T08:27:32.17+00:00

Hi @Rajesh Kumar Yadav

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Answer 1

Olaf Helper 47,436

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,

Rajesh Kumar Yadav 1 Reputation point

2023-02-22T08:38:20.0366667+00:00

there is no calulation no conversion in the script u can see th script

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

-- bridgeid is bigint, remarks is varchar(250) and status is smallint

Answer 2

Naomi Nosonovsky 8,431

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

Rajesh Kumar Yadav 1 Reputation point

2023-02-23T05:23:59.9233333+00:00

i will check triggers ( thank u), can u joint me on outlook meeting, where i can show the stored porcedure while running it on database.
Naomi Nosonovsky 8,431 Reputation points

2023-02-23T14:10:49.47+00:00

I probably cannot join in the Outlook meeting.

Share via

Arithmetic overflow error converting expression to data type smallint

2 answers

Your answer