SP Resulting in "Cannot Insert Duplicate Key Row"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP Resulting in "Cannot Insert Duplicate Key Row"

Author

Topic

powersakthi
Starting Member

9 Posts

Posted - 2007-04-28 : 19:43:20

Hi,

We have two tables by name available_sn and serialnumber. The table available_sn will have a list of about 55 lakhs MAC Address.

Now in our Programming application we call a Stored Procedure called usp_GetMac and this procedure will perform the following steps.

1) Select Top 11 [value] from available_sn Where status=0 to a cursor [Here Status=0 means whichever not used]

2) Insert into serialnumber the [value] in the cursor.

3) Update available as status=1 where value=[value] from cursor.

The USP_GetMac Procedure is as below. Actually our Networking equipment will be programmed with these MAC Address and each equipment needs 11 mac address. At any single point of time totally 142 such units will get programmed. So this results in error "Cannot Insert Duplicate Key Row in the Object serialnumber with unique index "serialnumber_IDX01". Can anyone help to modify this SP to make the request to wait in queue and serve the programming application's request.

GetMAC SP is as below

***************************************************************************

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE USP_GetMac
@TraceId Varchar(30), @MacNo Int, @HexOutPut Varchar(1000) OutPut
AS

Declare @Temp Table (Value varchar(200))
Declare @value varchar(30), @iCtr Int, @iLimit int,@iCheck int,@UnitID varchar(200)
Declare @Hex_output varchar(1000)

Select @UnitID=[UnitID] From SerialNumber With (NoLock) Where [value]=@TraceID
Select @iCheck=Count(*) from SerialNumber With (NoLock) Where [UnitID]=@UnitID
set @Hex_output = @TraceId

-- To Check whether MAC Address is already allocated for this Equipment, if yes then

--return the previous allocated MAC Address

if (@iCheck > 2)

Begin
DECLARE Mac_Cursor CURSOR FOR
Select Top 11 [value] From SerialNumber With (NoLock)
Where [UnitID]=@UnitID and [SerialNumberTypeID] <> 0
OPEN Mac_Cursor
FETCH NEXT FROM Mac_Cursor INTO @value
WHILE (@@Fetch_Status=0)
Begin
set @Hex_output = @Hex_output + ',' + @value
FETCH NEXT FROM Mac_Cursor INTO @value
END
CLOSE Mac_Cursor
Deallocate Mac_Cursor
End

-- If not allocated already then allocate now

ELSE
Begin
Begin Tran
If (@UnitID <> '')
Begin
DECLARE Mac_Cursor1 CURSOR FOR
SELECT Top 11 [Value] FROM AvailableSN
Where [StatusID]=0 Order By Value
OPEN Mac_Cursor1
Set @iCtr = 0
FETCH NEXT FROM Mac_Cursor1 INTO @value
WHILE (@iCtr < @MacNo)
BEGIN
Insert into SerialNumber Values (@UnitID,(@iCtr+1),@value)
set @Hex_output = @Hex_output + ',' + @value
update AvailableSN Set [StatusID]=1 Where [Value]=@value
Set @iCtr = @iCtr + 1
FETCH NEXT FROM Mac_Cursor1 INTO @value
END
CLOSE Mac_Cursor1
DEALLOCATE Mac_Cursor1
End
Commit
End

select @HexOutPut = @Hex_output
Select @HexOutPut

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

********************************************************************************

sakthi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources