Arithmetic overflow error converting expression to data type smallint. The state

Question

Post reply

Arithmetic overflow error converting expression to data type smallint. The state

rajemessage 14195

Hall of Fame

Points: 3315
More actions
February 20, 2023 at 5:45 am

#4151770

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

yours sincerly

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Could you move a little? I can't see the actual stored procedure.

No, really. Post the code for it. Or did you intend for a game of pin the tail on the donkey?

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 2

Thename of the stored procedure "uspMS". no need to shift.
The same code runs fine with the same parameters.
it is only, sometimes, it gives an error, and the SQL server is known to give the same result on the same parameters.

so could it be some Microsoft problem? because today I tried to post this to Microsoft foram and something happend to my account and I am not able to log in.

ratbak Ten Centuries Points: 1358 More actions · Answer 3

Despite the name, I'm not aware of that being a Microsoft system stored procedure.

Can you provide the relevant body of the stored procedure (obfuscated for security if necessary)?

The error means what it says -- somewhere the data is trying to put a value outside the range of a smallint -- -32,768 to 32,767. You will need to debug to find the code/data that is causing that issue.

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 4

1)it is my stored procedure.

2) I debuged my sp and it stated running properly. and after that it was also running properly with same params.

but after sometimes it showed me again same error.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

without you giving us the sp and the exact lines where it is happening we can't really help you other than telling that the most obvious error is that your queries within it deal with different data on different executions.

for errors like this even changing the clustered index of one of the tables used can cause the issue (for example using a top 10 without an order by will cause different rows to be processed, even if your parameters are the same.)

Michael L John One Orange Chip Points: 27055 More actions · Answer 6

This is one of the easiest errors to diagnose.

Your code, which we cannot see, is trying to assign a value to a variable or a column that is defined as a smallint data type. The valid values that a smallint can hold are -32,768 to 32,767. The value that it is assigning is either smaller than -32,768 or larger than 32,767.

This will show you the range of values that integer datatypes can hold in SQL Server.

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

So, maybe something like this is occurring. Again, without being able to see your code, we can't be sure.

DECLARE @My_smallint smallint;
SELECT @My_smallint = (32000 + 32000)

Since 32000 + 32000 = 64000, it cannot be assigned to the variable @My_smallint, which cannot hold a number larger than 32767.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

rajemessage 14195 wrote:

1)it is my stored procedure.
2) I debuged my sp and it stated running properly. and after that it was also running properly with same params.
but after sometimes it showed me again same error.

Based on that, I'm sure that your code is perfect and that there's been absolutely no deviation in the parameters you've used. You should report this to Microsoft as a bug and ask them why their junk is messing with your perfect code.

Oooorrrr... you could post one of the sections of code that's producing the error along with some test data and we might be able to tell you what's going on. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 8

This is the code which is creating the problem (bridgid is bigint ,remaks is varchar(250) status smallint) in both the tables mentioned below.

INSERT INTO KKR (BridgID,Remarks,Status)

SELECT DISTINCT BridgID,Remarks,1

FROM

kkrMain where kkrmainid=23

when i updated the sp it started running but after sometimes it shows the same error.

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

Please script and paste the whole procedure so everyone can see what is failing as you have multiple issues on multiple lines.

Michael L John One Orange Chip Points: 27055 More actions · Answer 10

rajemessage 14195 wrote:

This is the code which is creating the problem (bridgid is bigint ,remaks is varchar(250) status smallint) in both the tables mentioned below. INSERT INTO KKR (BridgID,Remarks,Status) SELECT DISTINCT BridgID,Remarks,1 FROM kkrMain where kkrmainid=23
when i updated the sp it started running but after sometimes it shows the same error.

Do yourself and us a favor, and read this article

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

It will describe the best way to ask questions, and provide the volunteers the information needed to provide real help.

SIX different people have asked you to please post your code. Clearly you do not understand what that means. If you read the article, hopefully understand what we are asking for.

Now, and this is a guess, because I can't see what you are looking at, there is a value greater than 32,767 for "Status" that is being inserted into the table "kkr". Look for that.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

rajemessage 14195 wrote:

This is the code which is creating the problem (bridgid is bigint ,remaks is varchar(250) status smallint) in both the tables mentioned below. INSERT INTO KKR (BridgID,Remarks,Status) SELECT DISTINCT BridgID,Remarks,1 FROM kkrMain where kkrmainid=23
when i updated the sp it started running but after sometimes it shows the same error.

We need to know some things like what the datatypes for those columns are for both tables. Based on what you've stated and what the error is, I'm thinking they're different.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 12

due to company policy i can not send the stored procudure.

but this is the line which is creating problem. and same line is written many times in the SP with diffrent id's and few more cols which are fixed text like 'epoxy' this is entered in a column which is of varchar type.

so i have deleted the column as that is not creating the problem.

This reply was modified 1 year, 9 months ago by rajemessage 14195.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

rajemessage 14195 wrote:

This is the code which is creating the problem (bridgid is bigint ,remaks is varchar(250) status smallint) in both the tables mentioned below.
 INSERT INTO KKR
         (BridgID,Remarks,Status)
 SELECT DISTINCT BridgID, Remarks, 1
   FROM kkrMain where kkrmainid=23
when i updated the sp it started running but after sometimes it shows the same error.

You're positive that the code is that and you've double checked all the datatypes involved? It just doesn't seem possible. Do you have a trigger or indexed view on the table in question and, if so, are they coming into play?

Also, we really have no idea what you mean when you say "when i updated the sp".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 14

yes,

kkrMain has one trigger which also inserts in KKR table ( but that one only works in update mode, and I got the error in inserts also)

and one indexed view is there on kkrMain.