Some useful Query

[~Untouchable~]

Some really useful queries to change things in your server(at least for me)
To change the mob slots(example you want to spawn niya generals in roc mountain instead of wing tribes then use this query):

 Spoiler

USE SRO_VT_SHARD
UPDATE [dbo].[Tab_RefTactics]SET dwObjID = replace(dwObjID, 'Mob ID you want to remove', 'Mob ID you want to spawn instead of removed one') WHERE dwObjID LIKE 'Repeat the mod ID you want to remove';

To set elixir stacks:

 Spoiler

UPDATE dbo._RefObjItem
SET MaxStack = 250
WHERE ID BETWEEN 2025 AND 2032 (change SET MaxStack= xxx to change the stack number of elixirs) and edit itemdata5000.txt in your media as the stack number how many you've set..

To change plus of drops:

 Spoiler

UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.933333
WHERE OptLevel = 1
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.944444
WHERE OptLevel = 2
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.955555
WHERE OptLevel = 3
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.966666
WHERE OptLevel = 4
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.966666
WHERE OptLevel = 5
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.967778
WHERE OptLevel = 6
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.977799
WHERE OptLevel = 7
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.979999
WHERE OptLevel = 8
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.999799
WHERE OptLevel = 9
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.999899
WHERE OptLevel = 10
UPDATE dbo._RefDropOptLvlSel
SET Prob = 0.999999
WHERE OptLevel = 11
UPDATE dbo._RefDropOptLvlSel
SET Prob = 1
WHERE OptLevel = 12

set them however you want..

To add a new drop:

 Spoiler

USE SRO_VT_SHARD
GO

DECLARE @MonsterID int
DECLARE @ItemID int
DECLARE @DropRatio real



SET @MonsterID = 24082 --Mob ID
SET @DropRatio = 0.00004 -- Rate of drop(%100 = 1 , %50 = 0.5 , %0 =0)
SET @ItemID = 3851 -- Item ID


INSERT _RefMonster_AssignedItemDrop
(
RefMonsterID,
RefItemID,
DropGroupType,
OptLevel,
DropAmountMin,
DropAmountMax,
DropRatio,
RefMagicOptionID1,
CustomValue1,
RefMagicOptionID2,
CustomValue2,
RefMagicOptionID3,
CustomValue3,
RefMagicOptionID4,
CustomValue4,
RefMagicOptionID5,
CustomValue5,
RefMagicOptionID6,
CustomValue6,
RefMagicOptionID7,
CustomValue7,
RefMagicOptionID8,
CustomValue8,
RefMagicOptionID9,
CustomValue9,
RentCodeName
)
VALUES(@MonsterID , @ItemID, 0, 0, 1, 1, @DropRatio, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0 , 0 , 'xxx')

To set unique spawn/respawn times:
Little explain: 60 secs=minimum delay spawn time 120 secs:maximum delay,also calculate time of them accordingly to second.So 60=1 min

 Spoiler

USE [SRO_VT_SHARD]
GO
Update Tab_RefNest Set dwDelayTimeMin=60 , dwDelayTimeMax=120 WHERE dwTacticsID = (SELECT dwTacticsID FROM Tab_RefTactics WHERE dwObjID =
(SELECT ID FROM _RefObjCommon WHERE Codename128 like 'MOB_CH_TIGERWOMAN'))

Update Tab_RefNest Set dwDelayTimeMin=60 , dwDelayTimeMax=120 WHERE dwTacticsID = (SELECT dwTacticsID FROM Tab_RefTactics WHERE dwObjID =
(SELECT ID FROM _RefObjCommon WHERE Codename128 like 'MOB_OA_URUCHI'))

Update Tab_RefNest Set dwDelayTimeMin=60 , dwDelayTimeMax=120 WHERE dwTacticsID = (SELECT dwTacticsID FROM Tab_RefTactics WHERE dwObjID =
(SELECT ID FROM _RefObjCommon WHERE Codename128 like 'MOB_KK_ISYUTARU'))

Update Tab_RefNest Set dwDelayTimeMin=60 , dwDelayTimeMax=120 WHERE dwTacticsID = (SELECT dwTacticsID FROM Tab_RefTactics WHERE dwObjID =
(SELECT ID FROM _RefObjCommon WHERE Codename128 like 'MOB_TK_BONELORD'))

Update Tab_RefNest Set dwDelayTimeMin=60 , dwDelayTimeMax=120 WHERE dwTacticsID = (SELECT dwTacticsID FROM Tab_RefTactics WHERE dwObjID =
(SELECT ID FROM _RefObjCommon WHERE Codename128 like 'MOB_RM_TAHOMET'))

Update Tab_RefNest Set dwDelayTimeMin=60 , dwDelayTimeMax=120 WHERE dwTacticsID = (SELECT dwTacticsID FROM Tab_RefTactics WHERE dwObjID =
(SELECT ID FROM _RefObjCommon WHERE Codename128 like 'MOB_EU_KERBEROS'))

Update Tab_RefNest Set dwDelayTimeMin=60 , dwDelayTimeMax=120 WHERE dwTacticsID = (SELECT dwTacticsID FROM Tab_RefTactics WHERE dwObjID =
(SELECT ID FROM _RefObjCommon WHERE Codename128 like 'MOB_AM_IVY'))

To remove mobs:

USE [SRO_VT_SHARD]
delete from tab_refnest


WHERE dwTacticsID in (SELECT dwTacticsID FROM Tab_RefTactics WHERE dwObjID in
(SELECT ID FROM _RefObjCommon WHERE Codename128 like '%MOB_SD%'))

MOB_SD=it will remove alexandria mobs..

To add a new mob/unique spawn:I don't know how to add different spawn places like the other uniques,gotta check some table to figure out this..

 Spoiler

USE SRO_VT_SHARD
DECLARE @UNIQUE VARCHAR (129)
DECLARE @CHARNAME VARCHAR (64)
DECLARE @mindealy INT
DECLARE @maxdelay int
DECLARE @INITRADIUS INT
DECLARE @RADIUS int
DECLARE @MAXNEST int = (SELECT MAX (dwNestID) FROM Tab_RefNest)+1
DECLARE @MAXHIVE int = (SELECT MAX (dwHiveID) FROM Tab_RefHive)+1
DECLARE @MAXTACTICS int = (SELECT MAX (dwTacticsID) FROM Tab_RefTactics)+1






SET @UNIQUE = 'MOB_JUPITER_YUNO' // The code of mob/unique,you can find them through RefObjCommon-CodeName128..
SET @CHARNAME = 'CHARNAME' // the place of mob spawn through dbo.char table(put your character anywhere you want to spawn mob/unique and log off then write the character name here
SET @mindealy = 60 // minimum delay
SET @maxdelay = 120 // maximum delay
SET @INITRADIUS = 100 // around the mob/unique
SET @RADIUS = 500 // the range of mob/unique








Declare @MOBID int SET @MOBID = (SELECT ID FROM _RefObjCommon WHERE CodeName128 = @UNIQUE)






INSERT INTO Tab_RefTactics (dwTacticsID,dwObjID,btAIQoS,nMaxStamina,btMaxStam inaVariance,nSightRange,btAggressType,AggressData, btChangeTarget,btHelpRequestTo,btHelpResponseTo,bt BattleStyle,BattleStyleData,btDiversionBasis,Diver sionBasisData1,DiversionBasisData2,DiversionBasisD ata3,DiversionBasisData4,DiversionBasisData5,Diver sionBasisData6,DiversionBasisData7,DiversionBasisD ata8,btDiversionKeepBasis,DiversionKeepBasisData1, DiversionKeepBasisData2,DiversionKeepBasisData3,Di versionKeepBasisData4,DiversionKeepBasisData5,Dive rsionKeepBasisData6,DiversionKeepBasisData7,Divers ionKeepBasisData8,btKeepDistance,KeepDistanceData, btTraceType,btTraceBoundary,TraceData,btHomingType ,HomingData,btAggressTypeOnHoming,btFleeType,dwCha mpionTacticsID,AdditionOptionFlag,szDescString128) VALUES
(@MAXTACTICS,@MOBID,0,500,50,200,0,0,2,2,2,0,0,5,0 ,0,0,0,0,30,0,0,4,0,0,0,0,0,0,0,0,0,0,0,1,500,0,0, 2,0,0,112,@UNIQUE)






INSERT INTO Tab_RefHive (dwHiveID,btKeepMonsterCountType,dwOverwriteMaxTot alCount,fMonsterCountPerPC,dwSpawnSpeedIncreaseRat e,dwMaxIncreaseRate,btFlag,GameWorldID,HatchObjTyp e,szDescString128) VALUES
(@MAXHIVE,0,1,0,0,0,0,1,1,@UNIQUE)












DECLARE @REGION INT SET @REGION = (SELECT (LatestRegion) FROM _Char WHERE CharName16 = @CHARNAME)
DECLARE @POSX INT SET @POSX = (SELECT (POSX) FROM _Char WHERE CharName16 = @CHARNAME)
DECLARE @POSY INT SET @POSY = (SELECT (POSY) FROM _Char WHERE CharName16 = @CHARNAME)
DECLARE @POSZ INT SET @POSZ = (SELECT (POSZ) FROM _Char WHERE CharName16 = @CHARNAME)





INSERT INTO Tab_RefNest (dwNestID,dwHiveID,dwTacticsID,nRegionDBID,fLocalP osX,fLocalPosY,fLocalPosZ,wInitialDir,nRadius,nGen erateRadius,nChampionGenPercentage,dwDelayTimeMin, dwDelayTimeMax,dwMaxTotalCount,btFlag,btRespawn,bt Type) VALUES
(@MAXNEST,@MAXHIVE,@MAXTACTICS,@REGION,@POSX,@POSY ,@POSZ,0,@RADIUS,@INITRADIUS,0,@mindealy,@maxdelay ,1,0,1,0)

To remove alchemy materials:

 Spoiler

USE [SRO_VT_SHARD]
GO

UPDATE dbo._RefDropItemAssign
SET Service = 0
WHERE RefItemID BETWEEN 6679 AND 5414

UPDATE dbo._RefDropItemAssign
SET Service = 0
WHERE RefItemID BETWEEN 6082 AND 6371

UPDATE dbo._RefDropItemAssign
SET Service = 0
WHERE RefItemID BETWEEN 10501 AND 10527

UPDATE dbo._RefDropItemAssign
SET Service = 0
WHERE RefItemID BETWEEN 11499 AND 11668

UPDATE dbo._RefDropItemAssign
SET Service = 0
WHERE RefItemID BETWEEN 12554 AND 12569

Feel free to ask question whatever you need,i'll try to help you as much as i can.

[sherio_3x]

are you have a Query to remove item from DB by item ID ?

[鳳凰城]

Originally Posted by sherio_3x

are you have a Query to remove item from DB by item ID ?

USE [SRO_VT_SHARD]
Exec [Dbo].[_SEEK_N_DESTROY_ITEM] ['your code here']
by Item ID , I don't think it's exist or , I can't find it in my hard disc.

[Menorhord]

You have Query Make Alchemy Success 100% ?

[Bocc1337]

i need a query to take drops off of mobs

[[NoReason]]

Originally Posted by sonsof2004

i need a query to take drops off of mobs

Off Of 3lek

select item ID Query

Select ID From _RefObjCommon Where Codename128 like 'ITEM CODE'

delete DROP FROM MONSTER

USE sro_vt_shard DELETE FROM _RefMonster_AssignedItemDrop where RefitemID like 'ITEM ID'

if you want to remove drop from specified monster use this query

DELETE FROM _RefMonster_AssignedItemDrop where refmonsterid='MONSTER ID' AND refitemid='ITEM ID'