CODEWITHSUNDEEP
sql-serversql-server-2008
urbanmojo
urbanmojo

Reputation: 757

Arithmetic Overflow With bigint

I have two identical databases on two different servers. If I run the query below on one server (version 10.50.2795.0) it runs fine, if I run it on the second server (version 10.50.6000.34) it gives an error:

Arithmetic overflow error converting expression to data type int.

The query is:

SELECT  min(date_time), 
MAX(date_time), count(*), 'meta_prompt'
FROM    event               evt
INNER JOIN
    prompt_event            prmt
ON  evt.event_id    =       prmt.event_id   
INNER JOIN
    meta_prompt         metp
ON  prmt.meta_prompt_id     =   metp.meta_prompt_id

The join field "meta_prompt_id" is type BIGINT. Any idea of differences between the two?

Upvotes: 2

Views: 999

Answers (2)

Ananda Kumar Jayaraman
Ananda Kumar Jayaraman

Reputation: 399

Count operation, by default, SQL Server will try to convert the output to Integer data type. I assume, in your case, the total number of records returned by this query exceeded the maximum ceiling value (2,147,483,647) of an integer. You could try COUNT_BIG function instead of COUNT.

Upvotes: 1

granadaCoder
granadaCoder

Reputation: 27852

Run the below query to verify that all the datatypes are the same on the 2 servers...for the column name(s) that you are joining on.

SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ( N'event_id' ,  N'meta_prompt_id' )
ORDER BY TABLE_NAME, COLUMN_NAME

If you find a column with a non compatible data-type (int instead of bigint as the most likely culprit as one commenter suggests)...use my answer at the below question to alter the datatype.

MS SQL Database with 10m rows, convert varchar to int on column

Upvotes: 0

Related Questions