Find Item by Serial

[lemoniscool]

With this sql query you will be able to get the Location (Inventory, Storage, Pet), CharID, JID and ItemCode of an item with the serial that was specified.
Can come very handy if used inside a php script which gets input out of a html form textarea where you specify 1 Serial at a time and then use this query inside a loopto recieve all those infos on multiple items very easily and fast!
sorted into a table it may even look good. i wrote this query to ease the work when someone was scammed or his account was "hacked" ^^

Compatible with MSSQL 2005/2008 (didnt test 2012)

USE SRO_VT_ACCOUNT

         DECLARE @Serial bigint
             declare @CID int
             declare @AID int
             declare @ItemCode varchar(50)
             declare @loc varchar(50)
             set @loc = 'inventory'
         ---------------------------------------------------
             SET @Serial = YOUR_SERIAL_HERE
         ---------------------------------------------------
             SET @CID = (select CharID from SRO_VT_SHARD.dbo._Inventory where ItemID = (select ID64 from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))
             if @CID is null
             begin
             SET @AID = (select UserJID from SRO_VT_SHARD.dbo._Chest where ItemID = (select ID64 from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))
             SET @loc = 'chest'
             end
             else if @AID is null
             begin
             SET @AID = (select UserJID from SRO_VT_SHARD.dbo._User where CharID = (select OwnerCharID from SRO_VT_SHARD.dbo._CharCOS where ID = (select COSID from SRO_VT_SHARD.dbo._InvCOS where ItemID = (select ID64 from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))))
             SET @loc = 'pet'
             end
             else
             begin
             SET @AID = (select UserJID from SRO_VT_SHARD.dbo._User where CharID = @CID)
             end
             SET @ItemCode = (select CodeName128 from SRO_VT_SHARD.dbo._RefObjCommon where ID = (select RefItemID from SRO_VT_SHARD.dbo._Items where Serial64 like @Serial))
             SELECT top 1 tbuser.StrUserID as AccID, chart.CharName16 as CharName, @Serial as Serial, @ItemCode as ItemCode, @loc as Location
             FROM TB_User as tbuser
             JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
             JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
             JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
             WHERE usert.UserJID = @AID

greetz

 

[Caosfox]

cool, i test it later

 

[loveme]

What is the purpose of this ?

 

[Crue]

Thank's Dude

 

[wiesel123]

What is the purpose of this ?

i wrote this query to ease the work when someone was scammed or his account was "hacked" ^^ thats what he wrote.. and he felt to share it with us..

 

[loveme]

where it will query ?

 

[Caosfox]

changed it for my own tastes, really nice querry

 

[loveme]

querty and erorr

Msg 105, Level 15, State 1, Line 31
Unclosed quotation mark after the character string ');

 

[lemoniscool]

querty and erorr

Msg 105, Level 15, State 1, Line 31
Unclosed quotation mark after the character string ');

oh yeah forgot to remove it, i copied it out of my php script ^^ its changed on the first post

 

[Daniiel5]

oh yeah forgot to remove it, i copied it out of my php script ^^ its changed on the first post

Msg 208, Level 16, State 1, Line 26
Invalid object name 'TB_User'.

 

[NourAyman]

Nice thread :

 

[lemoniscool]

Msg 208, Level 16, State 1, Line 26
Invalid object name 'TB_User'.

add USE SRO_VT_ACCOUNT at the top of the query (in case your database names are different make sure to change them)

 

[loveme]

SQL query will do it?

 

[lemoniscool]

what do you mean loveme?

 

[loveme]

like this?

 

[lemoniscool]

you know what an item serial is? and if so, did you specify it? if you specify a valid item serial it will display values

example:

 

[lemoniscool]

oh you "have better" .. so what? if you dont need it, you can ignore this thread others might need it
and there is nothing "old" about this query except its syntax but thats so people that use mssql 2005 can still use it =)

 

[thebigbody]

doesn't work with 2012

 

[lemoniscool]

what are the error messages 2012 throws?
as the first post says its tested on 2005 and 2008 i dont have 2012 ^^

 

[thebigbody]

what are the error messages 2012 throws?
as the first post says its tested on 2005 and 2008 i dont have 2012 ^^

the problem is it show nothing
hope anyone can fix it ^^

You must be registered to see links

btw is there anyway to find the item with serial but not with SMC
coz SMC show nothing not found and when i search it by my self in dbo,_Items i found it
but sure dont know who its owner from _Items