Table has identity value with primary key as big integer.

Stored procedure is running Return Scope_Identity() which works until the identity values exceeds the value of the implicit integer conversion, 2,147,483,646.

This is documented, but rarely run into.

The correct best practice is too use an output parameter for the stored procedure.

This requires changing the procedure and calling code, which can be difficult if this happens in production environment during a busy period.

The temporary solution is too reset the identity value to a negative values, as the range of an integer, -2,147,483,646 to +2,147,483,646.

Watch when you do this, as checkident('tablename') will reset an identity value, so be sure to use noreseed as part of the command.

For monitoring purposes:

 

and some discussion on the work-around, which is too return the value as an output parameter.

 

https://stackoverflow.com/questions/25915653/return-bigint-in-a-stored-procedure