[Help] Server dupe wipe

[cherry]

Put together by me. I also have modified it to check users on logout and ban them and delete their dupes.
This is for entire server dupe check.
I would only do this when the server is down. If a user is online it will not affect them. If you have a lot of users this could take a while.
maybe it could be tweaked a little.

First you need to make this table. It will be where all the users items are decoded into.
CREATE TABLE ITEM_DECODED(
strUserID char(21) NULL DEFAULT ('-'),
strAccountID char(21) NULL DEFAULT ('-'),
Name varchar(50),
dwID int NULL,
strSerial int NULL,
Durability int NULL,
StackSize smallint NULL,
ii [int] NULL
)

Next make this table. It will be where all the duped items go that are found in the previous table.
CREATE TABLE DUPERS_LOGGED(
strUserID varchar(50) NULL DEFAULT ('-'),
strAccountID varchar(21) NULL DEFAULT ('-'),
Name char(50) NULL,
dwID int NULL,
strSerial int NULL,
time smalldatetime NULL DEFAULT (getdate()),
ii smallint NULL
)

Now you need these procedures
1.This will decode all items from userdata and put them into item_decoded
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[decode_all_items_1299]
--By cherry killingzones.com
AS
declare @StrUserID char(21)
truncate table dupers_logged
truncate table item_decoded
DECLARE Backup_Cursor CURSOR
FOR
SELECT struserID from userdata
OPEN Backup_Cursor
FETCH NEXT FROM Backup_Cursor INTO @StrUserID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @row int, @i int, @count int, @dwid int, @type varbinary(4), @StackSize int, @strSerial int, @name char(50)
SET @i = 1
set @name = ''
WHILE @i < 401
BEGIN
SELECT @dwid = cast(cast(substring(cast(substring(strItem, @i,4) as varbinary(4)), 4, 1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 3, 1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 2, 1)+substring(cast(substring(strItem, @i,4) as varbinary(4)), 1, 1) as varbinary(4)) as int),
@strSerial = cast(cast(substring(cast(substring(strSerial, @i,4) as varbinary(4)), 4, 1)+substring(cast(substring(strSerial, @i,4) as varbinary(4)), 3, 1)+substring(cast(substring(strSerial, @i,4) as varbinary(4)), 2, 1)+substring(cast(substring(strSerial, @i,4) as varbinary(4)), 1, 1) as varbinary(4)) as int),
@type = cast(substring(strItem, @i+4, 2) as varbinary(2)),
@StackSize = cast(cast(cast( substring(strItem, @i+7, 1) as varbinary(1))+cast(substring(strItem, @i+6, 1) as varbinary(1)) as varbinary(2)) as smallint)
FROM USERDATA
WHERE strUserID = @StrUserID
select @name = strname from item where num = @dwid
IF @dwid <> 0
INSERT INTO ITEM_DECODED (strUserID, Name, dwid, strSerial, Durability, StackSize, ii)
VALUES (@StrUserId, @Name, @dwid, @strSerial, @type, @StackSize, (@i-1) / 8)
SET @i = @i + 8
END
FETCH NEXT FROM Backup_Cursor INTO @StrUserID
END
CLOSE Backup_Cursor
DEALLOCATE Backup_Cursor
exec decode_all_items_inn_1299

2.This will decode all warehouse items and put them into item_decoded
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[decode_all_items_inn_1299]
--By cherry killingzones.com
AS
declare @StrAccountID char(21)
DECLARE Backup_Cursor CURSOR
FOR
SELECT straccountID from warehouse
OPEN Backup_Cursor
FETCH NEXT FROM Backup_Cursor INTO @StrAccountID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @row int, @i int, @count int, @dwid int, @type varbinary(4), @StackSize int, @strSerial int, @name char(50)
SET @i = 1
set @name = ''
WHILE @i < 1601
BEGIN
SELECT @dwid = cast(cast(substring(cast(substring(WarehouseData, @i,4) as varbinary(4)), 4, 1)+substring(cast(substring(WarehouseData, @i,4) as varbinary(4)), 3, 1)+substring(cast(substring(WarehouseData, @i,4) as varbinary(4)), 2, 1)+substring(cast(substring(WarehouseData, @i,4) as varbinary(4)), 1, 1) as varbinary(4)) as int),
@strSerial = cast(cast(substring(cast(substring(strSerial, @i,4) as varbinary(4)), 4, 1)+substring(cast(substring(strSerial, @i,4) as varbinary(4)), 3, 1)+substring(cast(substring(strSerial, @i,4) as varbinary(4)), 2, 1)+substring(cast(substring(strSerial, @i,4) as varbinary(4)), 1, 1) as varbinary(4)) as int),
@type = cast(substring(WarehouseData, @i+4, 2) as varbinary(2)),
@StackSize = cast(cast(cast( substring(WarehouseData, @i+7, 1) as varbinary(1))+cast(substring(WarehouseData, @i+6, 1) as varbinary(1)) as varbinary(2)) as smallint)
FROM WAREHOUSE
WHERE StrAccountID = @StrAccountID
select @name = strname from item where num = @dwid
IF @dwid <> 0
INSERT INTO ITEM_DECODED (straccountID, Name, dwid, strSerial, Durability, StackSize, ii)
VALUES (@StrAccountId, @Name, @dwid, @strSerial, @type, @StackSize, (@i-1) / 8)
SET @i = @i + 8
END
FETCH NEXT FROM Backup_Cursor INTO @StrAccountID
END
CLOSE Backup_Cursor
DEALLOCATE Backup_Cursor
exec wipe_decode_all_items_1299

3. This will find the user dupes in item_decoded and put them into dupers_logged table
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[wipe_decode_all_items_1299]
--By cherry killingzones.com
AS
declare @straccountid char(21), @struserid char(21), @strSerial bigint, @dwid int, @stacksize smallint, @ii smallint
DECLARE Backup_Cursor CURSOR
FOR
SELECT straccountid, struserid, strserial, dwid, stacksize, ii from item_decoded
OPEN Backup_Cursor
FETCH NEXT FROM Backup_Cursor INTO @straccountid, @struserid, @strSerial, @dwid, @stacksize, @ii
WHILE @@FETCH_STATUS = 0
BEGIN
declare @count int
SELECT @count = COUNT(*) FROM ITEM_DECODED WHERE strSerial = @strSerial and dwid = @dwid
IF @count > 1 and @strSerial != 0 and @dwid !=0
BEGIN
INSERT INTO DUPERS_LOGGED (straccountid,struserid,dwid,strserial,ii)
VALUES (@straccountid,@struserid,@dwid,@strSerial,@ii)
END
FETCH NEXT FROM Backup_Cursor INTO @straccountid, @struserid, @strSerial, @dwid, @stacksize, @ii
END
CLOSE Backup_Cursor
DEALLOCATE Backup_Cursor
exec encode_all_items_1299

4. This will delete all duped items on all users
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE procedure [dbo].[encode_all_items_1299]
--by cherry killingzones.com
AS
DECLARE @strUserID char(21),@ii int
DECLARE @strName varchar(50),@dwid int,@dur int,@StackSize smallint, @row int, @i int, @Cdwid varbinary(4), @cdur varchar(2), @cstack varchar(2)
DECLARE Backup_Cursor1 CURSOR
FOR
SELECT struserid,ii from dupers_logged where struserid!='-'
OPEN Backup_Cursor1
FETCH NEXT FROM Backup_Cursor1 INTO @StrUserID,@ii
WHILE @@FETCH_STATUS = 0
BEGIN
set @strName = ''
set @dwid = 0
set @dur = 0
set @StackSize = 0
set @i = @ii * 8 + 1
select @row=count( * ) from item_decoded where struserid=@StrUserID and ii < @ii
if @row=0
begin
Set @Cdwid = Substring(cast(@dwid as varbinary(4)), 4, 1) + Substring(cast(@dwid as varbinary(4)), 3, 1) + Substring(cast(@dwid as varbinary(4)), 2, 1) + Substring(cast(@dwid as varbinary(4)), 1, 1)
Set @Cdur = cast(Substring(cast(@dur as varbinary(2)), 2, 1)+Substring(cast(@dur as varbinary(2)), 1, 1) as varchar(2))
Set @Cstack = cast(Substring(cast(@StackSize as varbinary(2)), 2, 1)+Substring(cast(@StackSize as varbinary(2)), 1, 1) as varchar(2))
update UserData set strItem = cast( substring(strItem, 1, @i-1) + cast(cast(@Cdwid as varchar(4)) + @Cdur + @Cstack as varchar(8)) + substring(strItem, @i+8, 401-@i) as binary(400)) where strUserID = @strUserID
end
if @row>0
Begin
Set @Cdwid = Substring(cast(@dwid as varbinary(4)), 4, 1) + Substring(cast(@dwid as varbinary(4)), 3, 1) + Substring(cast(@dwid as varbinary(4)), 2, 1) + Substring(cast(@dwid as varbinary(4)), 1, 1)
Set @Cdur = cast(Substring(cast(@dur as varbinary(2)), 2, 1)+Substring(cast(@dur as varbinary(2)), 1, 1) as varchar(2))
Set @Cstack = cast(Substring(cast(@StackSize as varbinary(2)), 2, 1)+Substring(cast(@StackSize as varbinary(2)), 1, 1) as varchar(2))
update UserData set strItem = cast( substring(strItem, 1, @i-1) + cast(cast(@Cdwid as varchar(4)) + @Cdur + @Cstack as varchar(8)) + substring(strItem, @i+8, 401-@i) as binary(400)) where strUserID = @strUserID
end
FETCH NEXT FROM Backup_Cursor1 INTO @StrUserID,@ii
END
CLOSE Backup_Cursor1
DEALLOCATE Backup_Cursor1
exec encode_all_items_inn_1299

5. This will delete all warehouse duped items.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE procedure [dbo].[encode_all_items_inn_1299]
--by cherry killingzones.com
AS
DECLARE @strAccountID char(21),@ii int
DECLARE @strName varchar(50),@dwid int,@dur int,@StackSize smallint, @row int, @i int, @Cdwid varbinary(4), @cdur varchar(2), @cstack varchar(2)
DECLARE Backup_Cursor2 CURSOR
FOR
SELECT straccountid,ii from dupers_logged where straccountid!='-'
OPEN Backup_Cursor2
FETCH NEXT FROM Backup_Cursor2 INTO @strAccountID ,@ii
WHILE @@FETCH_STATUS = 0
BEGIN
set @strName = ''
set @dwid = 0
set @dur = 0
set @StackSize = 0
set @i = @ii * 8 + 1
select @row=count( * ) from item_decoded where straccountid=@strAccountID
if @row>0
begin
Set @Cdwid = Substring(cast(@dwid as varbinary(4)), 4, 1) + Substring(cast(@dwid as varbinary(4)), 3, 1) + Substring(cast(@dwid as varbinary(4)), 2, 1) + Substring(cast(@dwid as varbinary(4)), 1, 1)
Set @Cdur = cast(Substring(cast(@dur as varbinary(2)), 2, 1)+Substring(cast(@dur as varbinary(2)), 1, 1) as varchar(2))
Set @Cstack = cast(Substring(cast(@StackSize as varbinary(2)), 2, 1)+Substring(cast(@StackSize as varbinary(2)), 1, 1) as varchar(2))
update WareHouse set WareHouseData = cast( substring(WareHouseData, 1, @i-1) + cast(cast(@Cdwid as varchar(4)) + @Cdur + @Cstack as varchar(8)) + substring(WareHouseData, @i+8, 1601-@i) as binary(1600)) where strAccountID = @strAccountID
end
FETCH NEXT FROM Backup_Cursor2 INTO @strAccountID,@ii
END
CLOSE Backup_Cursor2
DEALLOCATE Backup_Cursor2
truncate table item_decoded
truncate table dupers_logged


Now this will detect dupes if one item is in the character and the other/others are in the warehouse. Even across different users/accounts.
To run this all your need to do is this

exec decode_all_items_1299

The rest is all done. It will be faster if you run on the local sql server. If you connect to the sql server remotely it will be faster as a job. It will cause lag so again I recommend you do it when server is down.
Credits to me Cherry

To view the content, you need to sign in or register

 

[cherry]

Re: Server dupe wipe

If you get errors it posted wrong. You just need spaces between some words like
CREATE PROCEDURE may come up CREATEPROCEDURE

 

[Jackle]

Re: Server dupe wipe

very very nice, thanks for this release

 

[amir7083]

Re: Server dupe wipe

Can you make a video of you making this step by step?

 

[yangwx]

Re: Server dupe wipe

OK ty。。。。

 

[yangwx]

Re: Server dupe wipe

OK ty。。。。

 

[Kr3w]

Re: Server dupe wipe

I like how you credit yourself only, when the actual hard part of the code (decrypting) was done by some turk who took his time and decoded the packing of the keys.

 

[spamman]

Re: Server dupe wipe

lol. Nobody even knew how to get the unique id until I posted my dupe checker. GoatCheez helped me do that. I see no turk who did it. It was all just leached off my first procedure, and turks renamed variables.