SQL Code: Search who has EXC Full Opts

[nkerz]

This is a code I found in MG forum.

It searchs for exc full item

RUN THIS IN QUERY ANALYZER:

select * from warehouse where (charindex (0xbf, items) %10=8) or (charindex (0xff, items) %10=8) or (charindex (0x7f, items) %10=8) or (charindex (0x3f, items) %10=8)
select * from character where (charindex (0xbf, inventory) %10=8) or (charindex (0xff, inventory) %10=8) or (charindex (0x7f, inventory) %10=8) or (charindex (0x3f, inventory) %10=8)

now, if you want to BLOCK char who have those items, u can change it a little bit:

update character set CtlCode = '1' where (charindex (0xbf, inventory) %10=8) or (charindex (0xff, inventory) %10=8) or (charindex (0x7f, inventory) %10=8) or (charindex (0x3f, inventory) %10=8)

this one only blocks who have those items in inventory.

you can play with it a little bit, maybe if you manage some complete code (checking and banning all) you can release it in here

[MentaL]

[nkerz]

let me correct one this in block code:

update character set CtlCode = '1' where ((charindex (0xbf, inventory) %10=8) or (charindex (0xff, inventory) %10=8) or (charindex (0x7f, inventory) %10=8) or (charindex (0x3f, inventory) %10=8)) and (CtlCode <> '8')

now it will block all except GMs
;)

[nkerz]

Ok, now I managed to code the 0x00000000 serial finder.

VAULT CODE:

DECLARE @Acc varchar(10), @Inv BINARY(1200), @ItemSerial BINARY(3), @Item BINARY(10), @cnt INTEGER
DECLARE LISTA CURSOR LOCAL FOR
SELECT AccountID FROM warehouse
OPEN LISTA
FETCH NEXT FROM LISTA INTO @Acc
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Inv = (SELECT items FROM [warehouse] WHERE (AccountID=@Acc))
SET @cnt = 1
WHILE (@cnt <= (LEN(@Inv)/2))
BEGIN
SET @Item = SUBSTRING(@Inv,@cnt,10)
SET @ItemSerial = SUBSTRING(@Item,5,3)

IF (@ItemSerial = 0x000000)
BEGIN
print 'Yep! We got a 0x0 serial!'
print 'Account: '+@Acc
print 'Item: '; print @Item
print '--------------------------'
END
SET @cnt = @cnt + 10
END
FETCH NEXT FROM LISTA INTO @Acc
END
CLOSE LISTA
DEALLOCATE LISTA

INVENTORY CODE:

DECLARE @Acc varchar(10), @Inv BINARY(760), @ItemSerial BINARY(3), @Item BINARY(10), @cnt INTEGER
DECLARE LISTA CURSOR LOCAL FOR
SELECT Name FROM Character
OPEN LISTA
FETCH NEXT FROM LISTA INTO @Acc
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Inv = (SELECT Inventory FROM [Character] WHERE (Name=@Acc))
SET @cnt = 1
WHILE (@cnt <= (LEN(@Inv)/2))
BEGIN
SET @Item = SUBSTRING(@Inv,@cnt,10)
SET @ItemSerial = SUBSTRING(@Item,5,3)

IF (@ItemSerial = 0x000000)
BEGIN
print 'Yep! We got a 0x0 serial!'
print 'CharName: '+@Acc
print 'Item: '; print @Item
print '--------------------------'
END
SET @cnt = @cnt + 10
END
FETCH NEXT FROM LISTA INTO @Acc
END
CLOSE LISTA
DEALLOCATE LISTA

maybe later a write something to automatically remove those items from vault/inv.

cya

[john_d]

actually this is wat i use...
it will detect all those item that is impossible to be drop in the server...

select * from warehouse
where (charindex (0xbf, items) %10=8)
or (charindex (0xff, items) %10=8)
or (charindex (0x7f, items) %10=8)
or (charindex (0x3f, items) %10=8)
or (charindex (0x9f, items) %10=8)
or (charindex (0x8f, items) %10=8)
or (charindex (0x9e, items) %10=8)
or (charindex (0x9d, items) %10=8)
or (charindex (0xbd, items) %10=8)
or (charindex (0xbc, items) %10=8)
or (charindex (0xba, items) %10=8)
or (charindex (0xbb, items) %10=8)
or (charindex (0xb7, items) %10=8)
or (charindex (0xaf, items) %10=8)
select * from character
where (charindex (0xbf, inventory) %10=8)
or (charindex (0xff, inventory) %10=8)
or (charindex (0x7f, inventory) %10=8)
or (charindex (0x3f, inventory) %10=8)
or (charindex (0x9f, inventory) %10=8)
or (charindex (0x8f, inventory) %10=8)
or (charindex (0x9e, inventory) %10=8)
or (charindex (0x9d, inventory) %10=8)
or (charindex (0xbd, inventory) %10=8)
or (charindex (0xbc, inventory) %10=8)
or (charindex (0xba, inventory) %10=8)
or (charindex (0xbb, inventory) %10=8)
or (charindex (0xb7, inventory) %10=8)
or (charindex (0xaf, inventory) %10=8)

[elche27]

Well can some1 PLEASE explain how it work this line:

(charindex (0xbf, inventory) %10=8)

Why (0xbf, inventory)?? this part lead u to where....on SQL
Why %10=8?

The codes are erfect but nkerz or john_d, can u explain a little, please? Thank in advance!

[bet0x]

Nice work, very nice querys to all :D

This is my simple interface to MG Dupe Detector on wharehouses... , its a little noob, but works for get a list and count how many dupes by account and block code status...


SELECT DISTINCT
COUNT(CopyLog.copy_acid) AS Dupes, CopyLog.copy_acid AS Existe, MEMB_INFO.bloc_code AS [Estado [Bloqueado 1/0]]],
MEMB_INFO.memb___id AS Usuario, [Character].Name AS Personaje
FROM MEMB_INFO INNER JOIN
CopyLog ON MEMB_INFO.memb___id = CopyLog.copy_acid COLLATE Chinese_PRC_CS_AS INNER JOIN
[Character] ON CopyLog.copy_acid = [Character].Name
GROUP BY CopyLog.copy_acid, MEMB_INFO.bloc_code, MEMB_INFO.memb___id, [Character].Name, CopyLog.copy_acid
ORDER BY CopyLog.copy_acid DESC, COUNT(CopyLog.copy_acid) DESC

[Pinoyrealm]

Just want to ask if somebody made Query that work in Season 2, i mean to find those items greater than 2 options

[themad]

Just want to ask if somebody made Query that work in Season 2, i mean to find those items greater than 2 options

I gues that would be me

select * from warehouse
where (charindex (0xbf, items) %16=8)
or (charindex (0xff, items) %16=8)
or (charindex (0x7f, items) %16=8)
or (charindex (0x3f, items) %16=8)
or (charindex (0x9f, items) %16=8)
or (charindex (0x8f, items) %16=8)
or (charindex (0x9e, items) %16=8)
or (charindex (0x9d, items) %16=8)
or (charindex (0xbd, items) %16=8)
or (charindex (0xbc, items) %16=8)
or (charindex (0xba, items) %16=8)
or (charindex (0xbb, items) %16=8)
or (charindex (0xb7, items) %16=8)
or (charindex (0xaf, items) %16=8)
select * from extwarehouse
where (charindex (0xbf, items) %16=8)
or (charindex (0xff, items) %16=8)
or (charindex (0x7f, items) %16=8)
or (charindex (0x3f, items) %16=8)
or (charindex (0x9f, items) %16=8)
or (charindex (0x8f, items) %16=8)
or (charindex (0x9e, items) %16=8)
or (charindex (0x9d, items) %16=8)
or (charindex (0xbd, items) %16=8)
or (charindex (0xbc, items) %16=8)
or (charindex (0xba, items) %16=8)
or (charindex (0xbb, items) %16=8)
or (charindex (0xb7, items) %16=8)
or (charindex (0xaf, items) %16=8)
select * from character
where (charindex (0xbf, inventory) %16=8)
or (charindex (0xff, inventory) %16=8)
or (charindex (0x7f, inventory) %16=8)
or (charindex (0x3f, inventory) %16=8)
or (charindex (0x9f, inventory) %16=8)
or (charindex (0x8f, inventory) %16=8)
or (charindex (0x9e, inventory) %16=8)
or (charindex (0x9d, inventory) %16=8)
or (charindex (0xbd, inventory) %16=8)
or (charindex (0xbc, inventory) %16=8)
or (charindex (0xba, inventory) %16=8)
or (charindex (0xbb, inventory) %16=8)
or (charindex (0xb7, inventory) %16=8)
or (charindex (0xaf, inventory) %16=8)

njoy

[mayojung]

i want script change my COLLATE

all table do you have ?

sorry my bad english

[themad]

i want script change my COLLATE

all table do you have ?

sorry my bad english

change the ccollate of what ?? and to what ?

[Pinoyrealm]

I gues that would be me

select * from warehouse
where (charindex (0xbf, items) %16=8)
or (charindex (0xff, items) %16=8)
or (charindex (0x7f, items) %16=8)
or (charindex (0x3f, items) %16=8)
or (charindex (0x9f, items) %16=8)
or (charindex (0x8f, items) %16=8)
or (charindex (0x9e, items) %16=8)
or (charindex (0x9d, items) %16=8)
or (charindex (0xbd, items) %16=8)
or (charindex (0xbc, items) %16=8)
or (charindex (0xba, items) %16=8)
or (charindex (0xbb, items) %16=8)
or (charindex (0xb7, items) %16=8)
or (charindex (0xaf, items) %16=8)
select * from extwarehouse
where (charindex (0xbf, items) %16=8)
or (charindex (0xff, items) %16=8)
or (charindex (0x7f, items) %16=8)
or (charindex (0x3f, items) %16=8)
or (charindex (0x9f, items) %16=8)
or (charindex (0x8f, items) %16=8)
or (charindex (0x9e, items) %16=8)
or (charindex (0x9d, items) %16=8)
or (charindex (0xbd, items) %16=8)
or (charindex (0xbc, items) %16=8)
or (charindex (0xba, items) %16=8)
or (charindex (0xbb, items) %16=8)
or (charindex (0xb7, items) %16=8)
or (charindex (0xaf, items) %16=8)
select * from character
where (charindex (0xbf, inventory) %16=8)
or (charindex (0xff, inventory) %16=8)
or (charindex (0x7f, inventory) %16=8)
or (charindex (0x3f, inventory) %16=8)
or (charindex (0x9f, inventory) %16=8)
or (charindex (0x8f, inventory) %16=8)
or (charindex (0x9e, inventory) %16=8)
or (charindex (0x9d, inventory) %16=8)
or (charindex (0xbd, inventory) %16=8)
or (charindex (0xbc, inventory) %16=8)
or (charindex (0xba, inventory) %16=8)
or (charindex (0xbb, inventory) %16=8)
or (charindex (0xb7, inventory) %16=8)
or (charindex (0xaf, inventory) %16=8)

njoy

Big thanks bro... I LOVE YOU!!!

[mayojung]

change the ccollate of what ?? and to what ?

i can change to Thai_CI_AS all table

please help me

[Pinoyrealm]

I run the query bro, and he gives me so many accounts and characters then i checked them using Czf Editor to see but they are items normal exc i mean not full option or items not greater than 2 option, i was thinking maybe you can fix some to give me accounts with greater than 2 option items

[themad]

try now

select * from warehouse where (charindex (0xff, items)%16=7)or (charindex (0x38, items)%16=7)or (charindex (0x3C, items)%16=7)or (charindex (0x3E, items)%16=7)or (charindex (0x3F, items)%16=7)or (charindex (0x3B, items)%16=7)or (charindex (0x39, items)%16=7)or (charindex (0x2B, items)%16=7)or (charindex (0x0B, items)%16=7)or (charindex (0x29, items)%16=7)or (charindex (0x28, items)%16=7)or (charindex (0x2A, items)%16=7)or (charindex (0x2E, items)%16=7)or (charindex (0x2C, items)%16=7)or (charindex (0x21, items)%16=7)or (charindex (0x07, items)%16=7)or (charindex (0x0F, items)%16=7)or (charindex (0x1E, items)%16=7)or (charindex (0x1D, items)%16=7)or (charindex (0x1C, items)%16=7)or (charindex (0x16, items)%16=7)or (charindex (0x17, items)%16=7)or (charindex (0x19, items)%16=7)or (charindex (0x7F, items)%16=7)
select * from ExtWarehouse where (charindex (0xff, items)%16=7)or (charindex (0x38, items)%16=7)or (charindex (0x3C, items)%16=7)or (charindex (0x3E, items)%16=7)or (charindex (0x3F, items)%16=7)or (charindex (0x3B, items)%16=7)or (charindex (0x39, items)%16=7)or (charindex (0x2B, items)%16=7)or (charindex (0x0B, items)%16=7)or (charindex (0x29, items)%16=7)or (charindex (0x28, items)%16=7)or (charindex (0x2A, items)%16=7)or (charindex (0x2E, items)%16=7)or (charindex (0x2C, items)%16=7)or (charindex (0x21, items)%16=7)or (charindex (0x07, items)%16=7)or (charindex (0x0F, items)%16=7)or (charindex (0x1E, items)%16=7)or (charindex (0x1D, items)%16=7)or (charindex (0x1C, items)%16=7)or (charindex (0x16, items)%16=7)or (charindex (0x17, items)%16=7)or (charindex (0x19, items)%16=7)or (charindex (0x7F, items)%16=7)
select * from Character where (charindex (0xff, Inventory)%16=7)or (charindex (0x38, Inventory)%16=7)or (charindex (0x3C, Inventory)%16=7)or (charindex (0x3E, Inventory)%16=7)or (charindex (0x3F, Inventory)%16=7)or (charindex (0x3B, Inventory)%16=7)or (charindex (0x39, Inventory)%16=7)or (charindex (0x2B, Inventory)%16=7)or (charindex (0x0B, Inventory)%16=7)or (charindex (0x29, Inventory)%16=7)or (charindex (0x28, Inventory)%16=7)or (charindex (0x2A, Inventory)%16=7)or (charindex (0x2E, Inventory)%16=7)or (charindex (0x2C, Inventory)%16=7)or (charindex (0x21, Inventory)%16=7)or (charindex (0x07, Inventory)%16=7)or (charindex (0x0F, Inventory)%16=7)or (charindex (0x1E, Inventory)%16=7)or (charindex (0x1D, Inventory)%16=7)or (charindex (0x1C, Inventory)%16=7)or (charindex (0x16, Inventory)%16=7)or (charindex (0x17, Inventory)%16=7)or (charindex (0x19, Inventory)%16=7)or (charindex (0x7F, Inventory)%16=7)

i will write a better method..but need to finish smh first

[HELL_IN_HEAVEN]

i tried, this dont work now with 1.02N version,

[progame3]

Can u give me sql script ot program for finding who is bugging boxs with shadow bug ?

[=Ady=]

so anyone got a cod to search full opt items for 1.02n?

[=Ady=]

someone?

[loberia]

nice work :P Work some SQL code work for me

[Vengeful]

a little problem:
if the item has 16 opt or more this code cant find him...
can some1 help?
i want code who can find item full opt and opt+16+

[=Ady=]

so..anyone?

[jim3481]

I need serial finder script for 0.99.88T

[xavi321]

I need serial finder script for 0.99.88T

try this for me is 10

for Inventory

select * from character where (charindex (0x02020202, Inventory) %16=4)

for Warehouse

select * from warehouse where (charindex (0x02020202, items) %10=4)

where 02020202 is the serial

[alijabari]

let me correct one this in block code:


now it will block all except GMs
;)

1 question , im i the Admin on my server , if i give someone item full option becouse he is VIP in my server ... that mean i will block and that character ?

[Not4You]

nice work :P Work some SQL code work
btw its cool , i like it