This script goes way back so please have a backup in place before running it. It basically tries to correct the next master number.
/****** Object: Stored Procedure dbo.sopGetMasterNumber Script Date: 1/14/97 8:46:29 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.sopGetMasterNumber') and sysstat & 0xf = 4)
drop procedure dbo.sopGetMasterNumber
GO
create procedure dbo.sopGetMasterNumber
@O_iOUTMasterNumber int = NULL output,
@O_iErrorState int = NULL output
as
/*
**********************************************************************************************************
* (c) 1994 Great Plains Software, Inc.
**********************************************************************************************************
*
* PROCEDURE NAME: sopGetMasterNumber
*
* SANSCRIPT NAME: Get_Master_Number of form SOP_Entry
*
* PARAMETERS:
* @O_iOUTMasterNumber Retreived Master Number
* @O_iErrorState contains any errors that occur in this procedure
*
* DESCRIPTION:
* Returns the next number field from the given SOP_SETP record and increments
* the next number.
*
* Customization was made to look at SOP40500 to verify the NXTMSTNO is larger than existing values.
*
* TABLES:
* Table Name Access
* ========= =====
* SOP40100 Read/Write
*
* DATABASE:
* Company
*
*
* RETURN VALUE:
*
* 0 = Successful
* non-0 = Not successful
*
* REVISION HISTORY:
*
* Date Who Comments
* ------------- -------- -------------------------------------------------
* 24Jun98 msluke Initial Creation
*****************************************************************************
*/
declare @tTransaction tinyint,
@iError int,
@MaxMSTRNUMB int
/*
* Initialize variables and Output Parameters.
*/
select @O_iOUTMasterNumber = 0,
@O_iErrorState = 0
/*
* Start a transaction if the trancount is 0.
*/
if @@trancount = 0
begin
select @tTransaction = 1
begin transaction
end
/*
* Read record from SOP_SETP table within an update statement so a lock is held
* on the record until the number is updated. This will ensure that only a single
* user is reading this record at any given time.
*/
update
SOP40100 WITH (TABLOCKX,HOLDLOCK)
set
@O_iOUTMasterNumber = NXTMSTNO,
NXTMSTNO= NXTMSTNO + 1
if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
/*
* Do an additional read from SOP40500 to attempt to recover from the situation where the NXTMSTNO
* is less than or equal to the max value in SOP40500.
*/
select @MaxMSTRNUMB = max(MSTRNUMB) from SOP40500 (nolock)
if (@MaxMSTRNUMB >= @O_iOUTMasterNumber)
begin
update
SOP40100
set
@O_iOUTMasterNumber = @MaxMSTRNUMB + 1,
NXTMSTNO= @MaxMSTRNUMB + 2
if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
end
/*
* Reset next master number to 2, if master number has reached max value
* or it is zero.
*/
if (( @O_iOUTMasterNumber = 99999999) or ( @O_iOUTMasterNumber = 0)) and @O_iErrorState = 0
begin
select @O_iOUTMasterNumber = 1
update
SOP40100
set
NXTMSTNO = 2
if ( @@rowcount <> 1)
begin
/* Failed writing to SOP40100. */
select @O_iErrorState = 21035 /* Failed writing to SOP40100 */
end
end
/*
* Determine if a rollback or commit should be executed.
*/
if @O_iErrorState <> 0
begin
select @O_iOUTMasterNumber = 0
/*
* Rollback the transaction if this procedure started it.
*/
if @tTransaction = 1
rollback transaction
end
else
begin
/*
* Commit the transaction if this procedure started it.
*/
if @tTransaction = 1
commit transaction
end
return
GO
GRANT EXECUTE ON dbo.sopGetMasterNumber TO DYNGRP
GO
