SQL Code: Search who has EXC Full Opts

nkerz · Apr 10, 2005

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

nkerz · Apr 10, 2005

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 · Apr 11, 2005

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 :thumbup:

john_d · Apr 13, 2005

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 · May 13, 2005

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 · May 19, 2005

Nice work, very nice querys to all

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 · May 23, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

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

themad · May 23, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

-Jumong- said:
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

Code:

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 · May 23, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

i want script change my COLLATE

all table do you have ?

sorry my bad english

themad · May 23, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

mayojung said:
i want script change my COLLATE

all table do you have ?

sorry my bad english

change the ccollate of what ?? and to what ?

Pinoyrealm · May 23, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

themad said:

I gues that would be me

Code:

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 · May 23, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

themad said:
change the ccollate of what ?? and to what ?

i can change to Thai_CI_AS all table

please help me

Pinoyrealm · May 23, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

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 · May 23, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

try now

Code:

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 · May 24, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

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

progame3 · Jun 15, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

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

=Ady= · Jul 26, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

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

=Ady= · Jul 28, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

someone?

loberia · Jul 28, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

nice work

Work some SQL code work for me

Vengeful · Aug 7, 2007

Re: [Release] SQL Code: Search who has EXC Full Opts

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+

Welcome!

SQL Code: Search who has EXC Full Opts

Newbie Spellweaver

Newbie Spellweaver

Newbie Spellweaver

Custom Title Activated

Junior Spellweaver

TitansTech / SCFMT

Junior Spellweaver

Junior Spellweaver

Newbie Spellweaver

Junior Spellweaver

Junior Spellweaver

Newbie Spellweaver

Junior Spellweaver

Junior Spellweaver

Experienced Elementalist

Newbie Spellweaver

Elite Diviner

Elite Diviner

Newbie Spellweaver

Newbie Spellweaver

About Us

Online statistics

Forum statistics

RaGEZONE Sponsor