by Following is an SQL Server 6.5 service pack release 2 script.
The bug is as follows.
THE SET IDENTITY_INSERT WorkCenterPerformance is OFF.
The INSERT INTO with SELECT DISTINCT causes a zero value to be inserted
into
table WorkCenterPerformance.WorkCenterPerformanceID column no matter how
many rows
currently exist in table WorkCenterPerformance.
My understanding is that the value that should be inserted into the
identity column is 1
greater than the last value inserted.
This bug is re-creatable and I can make it fail every time.
This bug was discovered while executing a stored procedure that was invoked
from a trigger.
If you would like further info, e-mail me.
Sincerely, Jim create table WorkCenterPerformance /*
***************************************************************************
***********
(
WorkCenterPerformaceID int identity,
WorkCenterID smallint null ,
WorkOrderExplodedLinesID int null ,
PrintItemSpecID int null ,
WorkOrderReleaseNo T_WorkOrderReleaseNo null
default 0
constraint CKC_WORKORDERRELEASEN_WORKCENT check (WorkOrderReleaseNo
Priority tinyint null
constraint CKC_PRIORITY_WORKCENT check (Priority >= 0),
SupervisorStopWorkCode tinyint null ,
TotalQuantityCompleted T_WholeQuantity not null
default 0
constraint CKC_TOTALQUANTITYCOMP_WORKCENT check
(TotalQuantityCompleted >= 0),
CurrentQuantityToMake T_WholeQuantity not null
default 0
constraint CKC_CURRENTQUANTITYTO_WORKCENT check
(CurrentQuantityToMake >= 0),
StateOfWorkCenter tinyint not null
default 0
constraint CKC_STATEOFWORKCENTER_WORKCENT check (StateOfWorkCenter
in (0,1,2,3,4)),
StateOfWork tinyint not null
default 0
constraint CKC_STATEOFWORK_WORKCENT check (StateOfWork in
(0,1,2,3)),
WorkOrderStatusID tinyint null
default 2,
MostRecentStartTime datetime null ,
MostRecentStopTime datetime null ,
MostRecentHaltTime datetime null ,
ConcurrencyCount timestamp not null,
LastModifiedTime T_LastModifiedTime not null,
LastModifiedBy T_LastModifiedBy not null,
constraint PK_WORKCENTERPERFORMANCE primary key
(WorkCenterPerformaceID)
)
THIS STATEMENT CAUSES the value of zero to be inserted into column
WorkCenterPerformanceID.
I believe that this is a Microsoft BUG!!!
*/
INSERT INTO WorkCenterPerformance
( WorkCenterID
,WorkOrderExplodedLinesID
,PrintItemSpecID
,CurrentQuantityToMake
,WorkOrderStatusID
)
SELECT DISTINCT
300
,SB.WorkOrderExplodedLinesID
,NULL
,SB.OverQuantity /* Quantity to make */
,2 /* RELEASED (status) */
FROM #S2_Books SB
INNER JOIN BookSpec BS ON SB.PartID = BS.BookSpecPartID
LEFT OUTER JOIN BookComponentSpec BCS ON BS.BookSpecPartID =
BCS.BookSpecPartID
LEFT OUTER JOIN BookSpecOperations BSO ON BS.BookSpecPartID =
BSO.BookSpecPartID
LEFT OUTER JOIN SpecOperWorkCenter SOW ON BSO.SpecOperWorkCenterID =
SOW.SpecOperWorkCenterID
WHERE
BS.BindingTypeID IS NOT NULL
OR BCS.PrintBlockTypeID IN(1,2,5) /* Book Cover(Front or One Piece)
,Book Text ,Book Cover (Rear) */
OR SOW.WorkCenterID = 300 /* 300 = BINDERY */