August 19, 2024 at 2:12 pm
Hello SQL experts,
I have a legacy application developed when SQL server did not have native support for sequences. So what is does is creates table and uses its primary key as a sequence value generated. How it does is - inserts and deletes a dummy row and instantly access @@identity value as a sequence value.
As state in the title, I am getting - Arithmetic overflow error converting IDENTITY to data type bigint (even when I manually trying to cast or convert @@identity as bigint). Please help:
CREATE procedure GetCounter @tableName varchar(50) OUTPUT AS
BEGIN
DECLARE @insertSQL NVARCHAR(100)
DECLARE @deleteSQL NVARCHAR(100)
set nocount on
SET @insertSQL = N'INSERT INTO TempSeq' + @tableName + ' VALUES (''a'')'
SET @deleteSQL = N'DELETE FROM TempSeq' + @tableName + ' WITH (READPAST) WHERE seqDummy = ''a'''
EXECUTE sp_executesql @insertSQL
EXECUTE sp_executesql @deleteSQL
SELECT CAST(@@IDENTITY AS BIGINT)
END
August 19, 2024 at 2:19 pm
Theoretically @@IDENTITY value could be too large for a bigint. Hard to imagine actually using enough values to go past 19 digits, but it's theoretically possible.
Btw, you should get @@IDENTITY after the INSERT only, in case a trigger or some other such thing could affect it during the DELETE, i.e.:
EXECUTE sp_executesql @insertSQL
SELECT CAST(@@IDENTITY AS BIGINT)
EXECUTE sp_executesql @deleteSQL
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2024 at 2:26 pm
And accessing it as below:
USE [rcu]
GO
DECLARE @return_value int,
@dNextIndex bigint
EXEC @return_value = [STGSQL].[GetCounter]
@tableName = N'ID',
@dNextIndex = @dNextIndex OUTPUT
SELECT @dNextIndex as N'@dNextIndex'
SELECT 'Return Value' = @return_value
GO
Throws error:
Arithmetic overflow error converting IDENTITY to data type bigint.
Arithmetic overflow occurred.
August 19, 2024 at 2:38 pm
It is preferable to use SCOPE_IDENTITY() instead of @@IDENTITY
SCOPE_IDENTITY() returns the last identity value generated within the same scope (i.e. within the stored procedure)
CREATE procedure GetCounter @tableName varchar(50) OUTPUT AS
BEGIN
DECLARE @insertSQL NVARCHAR(100)
DECLARE @deleteSQL NVARCHAR(100)
set nocount on
SET @insertSQL = N'INSERT INTO TempSeq' + @tableName + ' VALUES (''a'')'
SELECT CAST(SCOPE_IDENTITY() AS BIGINT)
SET @deleteSQL = N'DELETE FROM TempSeq' + @tableName + ' WITH (READPAST) WHERE seqDummy = ''a'''
EXECUTE sp_executesql @insertSQL
EXECUTE sp_executesql @deleteSQL
END
August 19, 2024 at 2:42 pm
Thanks. I have already tried using SCOPE_IDENTITY() as well but facing the same error.
August 19, 2024 at 3:08 pm
So you deliberately set the value so that it would get too large for a bigint ... and that seems to be your error.
Not sure why you felt the need to go that high. Just go to a lower value and you should be ok, right?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 19, 2024 at 6:52 pm
We are adding Java BigInteger support and hence we need underlying database to support this. Oracle supports this and has no issue. I assuming SQL Server's BIGINT is equivalent to Java's BigInteger data type.
August 19, 2024 at 7:20 pm
We are adding Java BigInteger support and hence we need underlying database to support this. Oracle supports this and has no issue. I assuming SQL Server's BIGINT is equivalent to Java's BigInteger data type.
SQL Server BigInt has a maximum of 9,223,372,036,854,775,807
Java's BigInteger is an arbitrary-precision integer. It can represent integers of practically unlimited size, constrained only by available memory.
@@INDENTITY and SCOPE_IDENTITY are type numeric(38,0)
August 19, 2024 at 8:07 pm
Correct. You'd have to use decimal(38, 0) or some other larger value type rather than bigint.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 20, 2024 at 7:50 pm
I am really curious what the use case is for being able to store values this large???
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/
August 20, 2024 at 8:22 pm
I agree. Why the "need" to jump to such a huge number?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy