Remove ADV Elixirs

use this and completely delete the item... problem solved

Quote:

exec _SEEK_N_DESTROY_ITEM 'ITEMNAME'

in case u dont have the stored procedure...

here it is:

Quote:

USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_SEEK_N_DESTROY_ITEM] Script Date: 04/09/2013 17:56:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[_SEEK_N_DESTROY_ITEM]
@CodeName varchar(129)
AS

SET NOCOUNT ON

declare @RefItemID int
select @RefItemID = isnull(max(ID), 0) from _RefObjCommon with ( nolock ) where Codename128 = @CodeName

if(@RefItemID = 0)
begin
raiserror('Unknown Item', 1, 16)
return -1
end

-- Selecting inuse equal = 1
select ID64, OptLevel, Variance, Data into #DeleteItems from _Items as it with ( nolock, index = 0 ), _ItemPool as itp with ( nolock, index = 0 ) where it.RefItemID = @RefItemID and it.ID64 = itp.ItemID and itp.InUse = 1
delete from #DeleteItems where ID64 = 0

-- Avatar Table Exist?
declare @IsExistAvatarTable tinyint
if exists (select * from dbo.sysobjects where id = object_id(N'[_InventoryForAvatar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
set @IsExistAvatarTable = 1
END
ELSE
BEGIN
set @IsExistAvatarTable = 0
END

BEGIN TRANSACTION
-- _Inventory = 1, _InvCOS = 2, _Chest = 3, _GuildChest = 4, _InventoryForAvatar = 5
-- Deleted Log Insert

select
1 as OwnerType, iv.CharID as OwnerID, iv.ItemID as ID64, di.OptLevel, di.Variance, di.Data into #DEL_INV
from _Inventory as iv with ( nolock ), #DeleteItems as di
where iv.ItemID = di.ID64
update _Inventory set ItemID = 0 where ItemID in (select ID64 from #DeleteItems)

select
2 as OwnerType, ic.COSID as OwnerID, ic.ItemID as ID64, di.OptLevel, di.Variance, di.Data into #DEL_INVCOS
from _InvCOS as ic with ( nolock ), #DeleteItems as di
where ic.ItemID = di.ID64
update _InvCOS set ItemID = 0 where ItemID in (select ID64 from #DeleteItems)

select
3 as OwnerType, c.UserJID as OwnerID, c.ItemID as ID64, di.OptLevel, di.Variance, di.Data into #DEL_CHEST
from _Chest as c with ( nolock ), #DeleteItems as di
where c.ItemID = di.ID64
update _Chest set ItemID = 0 where ItemID in (select ID64 from #DeleteItems)

select
4 as OwnerType, gc.GuildID as OwnerID, gc.ItemID as ID64, di.OptLevel, di.Variance, di.Data into #DEL_GUILDCHEST
from _GuildChest as gc with ( nolock ), #DeleteItems as di
where gc.ItemID = di.ID64
update _GuildChest set ItemID = 0 where ItemID in (select ID64 from #DeleteItems)

if( @IsExistAvatarTable = 1 )
BEGIN
select
5 as OwnerType, ivAva.CharID as OwnerID, ivAva.ItemID as ID64, di.OptLevel, di.Variance, di.Data into #DEL_INVAVA
from _InventoryForAvatar as ivAva with ( nolock ), #DeleteItems as di
where ivAva.ItemID = di.ID64
update _InventoryForAvatar set ItemID = 0 where ItemID in (select ID64 from #DeleteItems)
END

update _ItemPool set InUse = 0 where ItemID in (select ID64 from #DeleteItems)
update _Items set Serial64 = 0 where ID64 in (select ID64 from #DeleteItems)

if( @@ERROR <> 0 )
BEGIN
ROLLBACK TRANSACTION
RaisError('Items deleted fail', 1, 16)
return -1
END

if( @IsExistAvatarTable = 1 )
BEGIN
-- _Log_SEEK_N_DESTROY_ITEM_FAST �α� ��̺� �̸�� Ѵ�.
insert into _Log_SEEK_N_DESTROY_ITEM_FAST
select getdate(), OwnerType, OwnerID, ID64, @CodeName, OptLevel, Variance, Data from #DEL_INV union all
select getdate(), OwnerType, OwnerID, ID64, @CodeName, OptLevel, Variance, Data from #DEL_INVCOS union all
select getdate(), OwnerType, OwnerID, ID64, @CodeName, OptLevel, Variance, Data from #DEL_CHEST union all
select getdate(), OwnerType, OwnerID, ID64, @CodeName, OptLevel, Variance, Data from #DEL_GUILDCHEST union all
select getdate(), OwnerType, OwnerID, ID64, @CodeName, OptLevel, Variance, Data from #DEL_INVAVA
END
ELSE
BEGIN
-- _Log_SEEK_N_DESTROY_ITEM_FAST �α� ��̺� �̸�� Ѵ�.
insert into _Log_SEEK_N_DESTROY_ITEM_FAST
select getdate(), OwnerType, OwnerID, ID64, @CodeName, OptLevel, Variance, Data from #DEL_INV union all
select getdate(), OwnerType, OwnerID, ID64, @CodeName, OptLevel, Variance, Data from #DEL_INVCOS union all
select getdate(), OwnerType, OwnerID, ID64, @CodeName, OptLevel, Variance, Data from #DEL_CHEST union all
select getdate(), OwnerType, OwnerID, ID64, @CodeName, OptLevel, Variance, Data from #DEL_GUILDCHEST
END

COMMIT TRANSACTION

drop table #DEL_INV
drop table #DEL_INVCOS
drop table #DEL_CHEST
drop table #DEL_GUILDCHEST
if( @IsExistAvatarTable = 1 )
BEGIN
drop table #DEL_INVAVA
END
drop table #DeleteItems

return 1
SET NOCOUNT OFF