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

Raised the identity for testing like this:

DBCC CHECKIDENT(“TempSeqID”, RESEED, 9223372036854775805)

Also, I have tried SCOPE_IDENTITY() instead of @@IDENTITY but facing the same error..

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.

​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  Raised the identity for testing like this:DBCC CHECKIDENT(“TempSeqID”, RESEED, 9223372036854775805) Also, I have tried SCOPE_IDENTITY() instead of @@IDENTITY but facing the same error..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.   Read More

​