Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

[SQL] Query to add Unique spot very fast

Experienced Elementalist
Joined
Nov 22, 2011
Messages
255
Reaction score
111
I know this is too easy to add mobs or unique for many people here but this may help others ..

you must have identity enabled at tables (Tab_RefNest and Tab_RefHive and Tab_RefTactics) or this will never works

For VSRO files :
PHP:
USE SRO_VT_SHARD /* Add Unique Spot By Arabianfox */

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

/* put your settings here */
SET @UNIQUE = 'MOB_JUPITER_YUNO' -- Unique code name from _RefobjCommon table
SET @CHARNAME = 'CHARNAME' -- Character name from _Char table to get mob position X Y Z
SET @mindealy = 60  -- Minimum time before appears in seconds
SET @maxdelay = 120  -- Maximum time before appears in seconds
SET @INITRADIUS = 100  -- Radius around spot unique can generated at
SET @RADIUS = 500  -- Radius around spot Unique can move in
/* end of settings area */

/* get ID of mob by code name */
Declare @MOBID int SET @MOBID = (SELECT ID FROM _RefObjCommon WHERE CodeName128 = @UNIQUE)

SET IDENTITY_INSERT Tab_RefTactics ON
INSERT INTO Tab_RefTactics (dwTacticsID,dwObjID,btAIQoS,nMaxStamina,btMaxStaminaVariance,nSightRange,btAggressType,AggressData,btChangeTarget,btHelpRequestTo,btHelpResponseTo,btBattleStyle,BattleStyleData,btDiversionBasis,DiversionBasisData1,DiversionBasisData2,DiversionBasisData3,DiversionBasisData4,DiversionBasisData5,DiversionBasisData6,DiversionBasisData7,DiversionBasisData8,btDiversionKeepBasis,DiversionKeepBasisData1,DiversionKeepBasisData2,DiversionKeepBasisData3,DiversionKeepBasisData4,DiversionKeepBasisData5,DiversionKeepBasisData6,DiversionKeepBasisData7,DiversionKeepBasisData8,btKeepDistance,KeepDistanceData,btTraceType,btTraceBoundary,TraceData,btHomingType,HomingData,btAggressTypeOnHoming,btFleeType,dwChampionTacticsID,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)
SET IDENTITY_INSERT Tab_RefTactics OFF

SET IDENTITY_INSERT Tab_RefHive ON
INSERT INTO Tab_RefHive (dwHiveID,btKeepMonsterCountType,dwOverwriteMaxTotalCount,fMonsterCountPerPC,dwSpawnSpeedIncreaseRate,dwMaxIncreaseRate,btFlag,GameWorldID,HatchObjType,szDescString128) VALUES
(@MAXHIVE,0,1,0,0,0,0,1,1,@UNIQUE)
SET IDENTITY_INSERT Tab_RefHive OFF

/* get position coordinates of your character */

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)

SET IDENTITY_INSERT Tab_RefNest ON
INSERT INTO Tab_RefNest (dwNestID,dwHiveID,dwTacticsID,nRegionDBID,fLocalPosX,fLocalPosY,fLocalPosZ,wInitialDir,nRadius,nGenerateRadius,nChampionGenPercentage,dwDelayTimeMin,dwDelayTimeMax,dwMaxTotalCount,btFlag,btRespawn,btType) VALUES
(@MAXNEST,@MAXHIVE,@MAXTACTICS,@REGION,@POSX,@POSY,@POSZ,0,@RADIUS,@INITRADIUS,0,@mindealy,@maxdelay,1,0,1,0)
SET IDENTITY_INSERT Tab_RefNest OFF


print ''
print ''
print 'Done!'

Because query will not work as it with BR files due to the difference between tables so:

This one for BR files (Tested and works fine with BR files) :

PHP:
USE BR_SHARD_DB /* Add Unique Spot By Arabianfox for BR files */

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

/* put your settings here */
SET @UNIQUE = 'MOB_RM_SPIRITS_CLON3' -- Unique code name from _RefobjCommon table
SET @CHARNAME = 'CHARNAME' -- Character name from _Char table to get mob position X Y Z
SET @mindealy = 3600  -- Minimum time before appears in seconds
SET @maxdelay = 3601  -- Maximum time before appears in seconds
SET @INITRADIUS = 100  -- Radius around spot unique can generated at
SET @RADIUS = 500  -- Radius around spot Unique can move in
/* end of settings area */

/* get ID of mob by code name */
Declare @MOBID int SET @MOBID = (SELECT ID FROM _RefObjCommon WHERE CodeName128 = @UNIQUE)

SET IDENTITY_INSERT Tab_RefTactics ON
INSERT INTO Tab_RefTactics (dwTacticsID,dwObjID,btAIQoS,nMaxStamina,btMaxStaminaVariance,nSightRange,btAggressType,AggressData,btChangeTarget,btHelpRequestTo,btHelpResponseTo,btBattleStyle,BattleStyleData,btDiversionBasis,DiversionBasisData1,DiversionBasisData2,DiversionBasisData3,DiversionBasisData4,DiversionBasisData5,DiversionBasisData6,DiversionBasisData7,DiversionBasisData8,btDiversionKeepBasis,DiversionKeepBasisData1,DiversionKeepBasisData2,DiversionKeepBasisData3,DiversionKeepBasisData4,DiversionKeepBasisData5,DiversionKeepBasisData6,DiversionKeepBasisData7,DiversionKeepBasisData8,btKeepDistance,KeepDistanceData,btTraceType,btTraceBoundary,TraceData,btHomingType,HomingData,btAggressTypeOnHoming,btFleeType,dwChampionTacticsID,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)
SET IDENTITY_INSERT Tab_RefTactics OFF

SET IDENTITY_INSERT Tab_RefHive ON
INSERT INTO Tab_RefHive (dwHiveID,btKeepMonsterCountType,dwOverwriteMaxTotalCount,fMonsterCountPerPC,dwSpawnSpeedIncreaseRate,dwMaxIncreaseRate,btFlag,GameWorldID,HatchObjType,szDescString128) VALUES
(@MAXHIVE,0,1,0,0,0,0,1,1,@UNIQUE)
SET IDENTITY_INSERT Tab_RefHive OFF

/* get position coordinates of your character */

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)

SET IDENTITY_INSERT Tab_RefNest ON
INSERT INTO Tab_RefNest (dwNestID,dwHiveID,dwTacticsID,nRegionDBID,fLocalPosX,fLocalPosY,fLocalPosZ,wInitialDir,nRadius,nGenerateRadius,nChampionGenPercentage,dwDelayTimeMin,dwDelayTimeMax,dwMaxTotalCount,btFlag) VALUES
(@MAXNEST,@MAXHIVE,@MAXTACTICS,@REGION,@POSX,@POSY,@POSZ,0,@RADIUS,@INITRADIUS,0,@mindealy,@maxdelay,1,0)
SET IDENTITY_INSERT Tab_RefNest OFF


print ''
print ''
print 'Done!'

For the people have Identity error

From SQL main menu choose Tools >> Options and go to Designers and remove check from "Prevent saving changes that require table re-creation as shown here:



Then go to tables (Tab_RefNest and Tab_RefHive and Tab_RefTactics) Right click and choose Design



Then Right click on [dwTacticsID for tactics table , dwHiveID for hive table , dwNestID for nest table] and choose "set primary key"



Then scroll down at column properties you will find "Identity specification" property >> choose "is identity" and choose "yes" as you can see here


please let me know if you have any problems or suggestions

thanks
 
Last edited:
Junior Spellweaver
Joined
May 2, 2012
Messages
127
Reaction score
9
Good job it may help some people
edit : BTW can you make a query to insert lines from Itemdata or charcterdata to (_RefObjCommon+_RefObjItem or _RefObjChar) ?
 
Skilled Illusionist
Joined
Feb 13, 2012
Messages
388
Reaction score
68
i can't set identity on in 2008 r2 sql why ?

when you want insert use this
Code:
SET IDENTITY_INSERT [dbo].[_RefHive] ON
and when you done inserting execute this
Code:
SET IDENTITY_INSERT [dbo].[_RefHive] OFF
just change _RefHive to your desired table name
 
Junior Spellweaver
Joined
May 9, 2012
Messages
109
Reaction score
92
Msg 8106, Level 16, State 1, Line 25
Table 'Tab_RefTactics' does not have the identity property. Cannot perform SET operation.
 
Experienced Elementalist
Joined
Sep 27, 2011
Messages
285
Reaction score
229
Msg 8106, Level 16, State 1, Line 25
Table 'Tab_RefTactics' does not have the identity property. Cannot perform SET operation.

Simply means that your table doesn't have an identity column :) -> you don't need to enable identity_insert
 
Initiate Mage
Joined
Apr 9, 2012
Messages
67
Reaction score
7
Not work for me :(
Code:
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '+'.
Msg 137, Level 15, State 2, Line 27
Must declare the scalar variable "@MAXTACTICS".
Msg 137, Level 15, State 2, Line 32
Must declare the scalar variable "@MAXHIVE".
Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@MAXNEST".
 
Banned
Banned
Joined
Oct 11, 2011
Messages
222
Reaction score
252
hey any help me ??



PHP:
Msg 8106, Level 16, State 1, Line 25
Table 'Tab_RefTactics' does not have the identity property. Cannot perform SET operation.
 
Banned
Banned
Joined
Oct 11, 2011
Messages
222
Reaction score
252
PHP:
(1 row(s) affected)

(1 row(s) affected)
Msg 515, Level 16, State 2, Line 43
Cannot insert the value NULL into column 'nRegionDBID', table 'SRO_VT_SHARD.dbo.Tab_RefNest'; column does not allow nulls. INSERT fails.
The statement has been terminated.
 
 
Done!

Solved :þ

Wrong #CharName hhhhhhhhhhhhhhhh
 
Last edited:
Back
Top