[ Release ] New Sql DB quires Collection .

Page 2 of 3 1 2 3
05/09/2012 02:50 FoxRayz#16
Quote:
Originally Posted by ahmed4ever2u View Post
here you are another good one :)
GM account ( ip & [GM stats] & Some Gold ) [Main]


1st Edit [main]
Honor Fix

2nd Edit [not main] :
all users to jangan :)




edit 3 : ( main )
Clean grap pets inventory ( kral for hackers )


Edit 4 : ( main )
cleaning db from trashes & invalid data
Sweeet :D i will have use for atleast 2 of this ones
05/09/2012 15:27 Imagine*#17
Quote:
Originally Posted by ahmed4ever2u View Post
here you are another good one :)
GM account ( ip & [GM stats] & Some Gold ) [Main]


1st Edit [main]
Honor Fix

2nd Edit [not main] :
all users to jangan :)




edit 3 : ( main )
Clean grap pets inventory ( kral for hackers )


Edit 4 : ( main )
cleaning db from trashes & invalid data
i will use them ;)
05/09/2012 20:02 rushcrush#18
Quote:
Originally Posted by tamer15 View Post
well , thank you

but i would ask blackrogue got skilldatax.enc ?
no........
05/10/2012 17:00 Neko*#19
New Quires are added ....

Thanks to , Ahmed4ever2u , and [Only registered and activated users can see links. Click Here To Register...]

-change character's mastery's at a certain level..
Credits ; nukertube , By ღ ∂ropp


Change Character User Name , A.K.A. ID.
Credits ; nukertube , By ღ ∂ropp

-Moving Uniques from its spawns (E.G : DemonShitan )
Credits : nukertube , By ღ ∂ropp




-ADD a GM account ( ip & [GM stats] & Some Gold )
by : Ahmed4ever2u



-Honor ranks Fix.
by : Ahmed4ever2u


-Teleport All users to Jangan.
by : ahmed4ever2u





-Clean grap pets inventory .
By : Ahmed4ever2u



-cleaning db from trashes & invalid data.
by : ahmed4ever2u
05/11/2012 16:14 Imagine*#20
Quote:
Originally Posted by Neko* View Post
New Quires are added ....

Thanks to , Ahmed4ever2u , and [Only registered and activated users can see links. Click Here To Register...]

-change character's mastery's at a certain level..
Credits ; nukertube , By ღ ∂ropp


Change Character User Name , A.K.A. ID.
Credits ; nukertube , By ღ ∂ropp

-Moving Uniques from its spawns (E.G : DemonShitan )
Credits : nukertube , By ღ ∂ropp




-ADD a GM account ( ip & [GM stats] & Some Gold )
by : Ahmed4ever2u



-Honor ranks Fix.
by : Ahmed4ever2u


-Teleport All users to Jangan.
by : ahmed4ever2u





-Clean grap pets inventory .
By : Ahmed4ever2u



-cleaning db from trashes & invalid data.
by : ahmed4ever2u
more useful query's ;)
05/11/2012 17:13 ღ ∂ Ropp#21
Here's another query for you, it's to ban players via database, easily with 1 query.


PHP Code:
--By Leandro ( nukertube ) --
USE [SRO_VT_SHARD]           
GO
--------------------------------                           
DECLARE @ChrID INT;            
DECLARE @CharName VARCHAR(64); 
DECLARE @AccJID INT;           
---------------------------------
SET @CharName =    'CharNameHere'---
---------------------------------
SET @ChrID=(SELECT CharID FROM _Char WHERE CharName16=@CharName)
SET @AccJID=(SELECT UserJID FROM _User WHERE CharID=@ChrID)
-----------------------------------------------------------------
USE SRO_VT_ACCOUNT
DECLARE @ID VARCHAR (64)
DECLARE @Reason VARCHAR(128)
DECLARE @Begin DATETIME
DECLARE @End DATETIME
SET @ID = (Select STRuserID FROM TB_User Where JID = @AccJID)

---------------------------------
SET @Reason    =    'Ban Reason Here'
SET @Begin    =    '2012-05-01 00:00:00.000' --    Time Begin    --
SET @End    =    '2040-01-01 00:00:00.000' --    Time End    --
---------------------------------
---------------------------

INSERT [dbo].[_Punishment]
(
[UserJID],
[Type],
[Executor],
[Shard],
[CharName],
[CharInfo],
[PosInfo] ,
[Guide],
[Description],
[RaiseTime],
[BlockStartTime],
[BlockEndTime],
[PunishTime],
[Status]
)
VALUES 
(
@AccJID,
1,
1,
0,
@CharName,
1,
1,
@Reason,
@Reason,
@Begin,
@Begin,
@End,
@End,
0);
----------------------------------------------------------
DECLARE @ReasonID INT
SET @ReasonID =
(Select SerialNo FROM _Punishment Where UserJID = @AccJID)
----------------------------------------------------------
INSERT [dbo].[_BlockedUser]
(
[UserJID],
[UserID],
[Type],
[SerialNo],
[TimeBegin],
[TimeEnd])
VALUES
(
@AccJID,
@ID,
1,
@ReasonID,
@Begin,
@End);

-----------------------------------------------------------------
SELECT StrUserID FROM TB_User WHERE JID = @AccJID
-----------------------------------------------------------------
--By Leandro ( nukertube ) -- 
05/13/2012 02:12 BestOnWorld#22
you must put Name Of Credits this is query
!!
05/13/2012 02:20 Neko*#23
Quote:
Originally Posted by BestOnWorld View Post
you must put Name Of Credits this is query
!!

Well , i didn't give the credits to my self , and this was my own Sql Quires library , so i didn't save the credits with each one.. , and i added some btw :D

Could you please tell me which query is yours ?
05/13/2012 03:26 BestOnWorld#24
Quote:
Originally Posted by Neko* View Post
Well , i didn't give the credits to my self , and this was my own Sql Quires library , so i didn't save the credits with each one.. , and i added some btw :D

Could you please tell me which query is yours ?
no problem i was think you give Credits to your self
Added 120 Skills By Me ;)

and this is Query to Search in RefObjCommon
05/14/2012 14:23 IceAmStiel#25
Last 100 Drops from Mobs (with the use of the SHARDLOG DB), for Homepages or w/e ->

PHP Code:
USE SRO_VT_SHARD
BEGIN
    SELECT TOP 100 
        chart.CharName16 as Player, 
        elog.ItemRefID as ItemID, 
        ref.CodeName128 as ItemCode,
        CASE WHEN CHARINDEX('Opt',elog.strDesc) != 0 THEN SUBSTRING(elog.strDesc,(PATINDEX('%+%',elog.strDesc)),2) ELSE '+0' END as '+Amount',
        SUBSTRING(elog.strDesc,CHARINDEX('MOB',elog.strDesc),(CHARINDEX(',',elog.strDesc, CHARINDEX('MOB',elog.strDesc)))-CHARINDEX('MOB',elog.strDesc)) as Monster,
        CASE
            WHEN chart.CharName16 collate SQL_Latin1_General_CP1_CI_AS like '%'+(SUBSTRING(elog.strDesc,CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))+1,(CHARINDEX(']',elog.strDesc, CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))+1))-CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))-1))+'%'
                THEN '-'
            WHEN CHARINDEX('Var',elog.strDesc) != 0
                THEN UPPER(SUBSTRING(elog.strDesc,CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))+1,(CHARINDEX(']',elog.strDesc, CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))+1))-CHARINDEX('[',elog.strDesc,CHARINDEX('Var',elog.strDesc))-1))
            ELSE '-' 
        END as Info,
        elog.EventTime as 'Date'
    FROM _Items as items
        JOIN /*#*/SRO_VT_SHARDLOG.dbo._LogEventItem/*#*/ as elog on items.Serial64 = elog.Serial64
        JOIN _Char as chart on elog.CharID = chart.CharID
        JOIN _RefObjCommon as ref on elog.ItemRefID = ref.ID
    WHERE 
        elog.strDesc like '%MOB%' 
        AND elog.strDesc not like '%NPC%' 
        AND ref.CodeName128 not like '%ARCHEMY%'
        AND chart.CharName16 not like '%[GM]%'
    ORDER BY elog.EventTime desc
END 
I'm Caipi, if I cud I'd rename my nick here but unfortunately.. ;o


A list of the online players also through the SHARDLOG DB:

PHP Code:
USE SRO_VT_SHARDLOG
SELECT         
            chart.CharName16 as 'Player', 
            CASE 
                WHEN MAX(elog.EventID) = 4 THEN 'Online' 
                WHEN MAX(elog.EventID) = 6 THEN 'Offline' 
                ELSE '-' 
            END as 'Status',
            CASE 
                WHEN MAX(elog.EventID) = 6 THEN '0'
                WHEN LEN(MAX(elog.EventTime)) > 0 THEN DATEDIFF(MINUTE,cast(MAX(elog.EventTime) as datetime),GETDATE()) 
                ELSE '-' 
            END as 'Minutes',
            MAX(elog.EventTime) as Date
FROM        
            /*#*/_LogEventChar/*#*/ as elog
            JOIN /*#*/SRO_VT_SHARD.dbo._Char/*#*/ as chart on elog.CharID = chart.CharID    
WHERE        
            (elog.EventID = 4 OR elog.EventID = 6)
GROUP BY    chart.CharName16
HAVING        MAX(elog.EventID) = 4
ORDER BY    MAX(elog.EventID) asc 
05/14/2012 14:27 Kape7#26
Quote:
Originally Posted by IceAmStiel View Post

I'm Caipi, if I cud I'd rename my nick here but unfortunately.. ;o
PM any admin about it, I asked them to change my nick and they did it pretty fast, around 1 year ago or so.
05/25/2012 09:51 Mytzanujrboss#27
thank you for all these awesome queries
05/27/2012 06:26 xxnukertube#28
ღ ∂ Ropp is trying to stay with my credits?
05/27/2012 07:52 ermvrs#29
thanks its useful
10/20/2013 13:20 prometeus#30
UPDATE dbo._RefSkill
SET Service = 0
WHERE ID BETWEEN 3437 AND 3440

Fix Fire Shield - Emperror 6 Lv
Page 2 of 3 1 2 3