[AmIMad]

How to Clean shard log db

Quote:
USE [LOG_DB]
GO
TRUNCATE TABLE [dbo].[_LogCashItem]
TRUNCATE TABLE [dbo].[_LogEventChar]
TRUNCATE TABLE [dbo].[_LogEventItem]
TRUNCATE TABLE [dbo].[_LogEventSiegeFortress]
TRUNCATE TABLE [dbo].[_LogSchedule]
TRUNCATE TABLE [dbo].[_LogServerEvent]

How to Clean shard db

Quote:
USE SRO_VT_SHARD
GO
truncate table _CharSkill
truncate table _CharSkillMastery
truncate table _GuildMember
truncate table _BlockedWhisperers
truncate table _Inventory
truncate table _InventoryForAvatar
truncate table _InventoryForLinkedStorage
truncate table _TrainingCampMember
truncate table _TrainingCampSubMentorHonorPoint
truncate table _TrainingCampHonorRank
truncate table _TrainingCampBuffStatus
delete from _TrainingCamp
truncate table _StaticAvatar
truncate table _User
truncate table _Friend
truncate table _Memo
truncate table _TimedJob
truncate table _CharTrijobSafeTrade
delete from _CharTrijob
truncate table _InvCOS
delete from _CharCOS where ID > 0
delete from _Char where charid > 0
truncate table _Chest
truncate table _ChestInfo
truncate table _ItemPool
delete from _Items where ID64 > 0
delete from _AccountJID
truncate table _GuildChest
update _AlliedClans set Ally1 = 0, Ally2 = 0, Ally3 = 0, Ally4 = 0, Ally5 = 0, Ally6 = 0, Ally7 = 0, Ally8 = 0
truncate table _GuildWar
delete from _Guild where ID > 0
delete from _AlliedClans where ID > 0
truncate table _CharNameList
truncate table _CharQuest
truncate table _ClientConfig
truncate table _DeletedChar
truncate table _SiegeFortressStruct
truncate table _SiegeFortressStoneState
truncate table _SiegeFortressRequest
truncate table _SiegeFortressObject
truncate table _SiegeFortressItemForge
truncate table _SiegeFortressBattleRecord
delete from _SiegeFortress
truncate table _OpenMarket

How to delete the accounts from database

Quote:
USE [SRO_VT_ACCOUNT]
GO
TRUNCATE TABLE [dbo].[TB_User]
TRUNCATE TABLE [dbo].[TB_User_Bak]
TRUNCATE TABLE [dbo].[SK_CharRenameLog]
TRUNCATE TABLE [dbo].[_Punishment]
TRUNCATE TABLE [dbo].[_ServiceManagerLog]
TRUNCATE TABLE [dbo].[Test_HN]
TRUNCATE TABLE [dbo].[_BlockedUser]
TRUNCATE TABLE [dbo].[_CasGMChatLog]
TRUNCATE TABLE [dbo].[_Notice]
TRUNCATE TABLE [dbo].[_SMCLog]
TRUNCATE TABLE [dbo].[QuaySoEpoint]
TRUNCATE TABLE [dbo].[SK_ITEM_GuardLog]
TRUNCATE TABLE [dbo].[SK_ItemSaleLog]
TRUNCATE TABLE [dbo].[SK_PackageItemSaleLog]
TRUNCATE TABLE [dbo].[SK_PK_UpdateLog]
TRUNCATE TABLE [dbo].[SK_ResetSkillLog]
TRUNCATE TABLE [dbo].[SK_Silk]
TRUNCATE TABLE [dbo].[SK_SilkBuyList]
TRUNCATE TABLE [dbo].[SK_SilkGoods]
TRUNCATE TABLE [dbo].[SK_SubtractSilk_VAS]
TRUNCATE TABLE [dbo].[SR_ShardCharNames]
TRUNCATE TABLE [dbo].[TB_Net2e]
TRUNCATE TABLE [dbo].[TB_Net2e_Bak]
TRUNCATE TABLE [dbo].[tb_paygate_trans]

Fix Privileged IP table

Quote:
USE [SRO_VT_ACCOUNT]
GO
/****** Object: Table [dbo].[_PrivilegedIP] Script Date: 09/13/2011 08:35:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP TABLE _PrivilegedIP
GO
CREATE TABLE [dbo].[_PrivilegedIP](
[IP1] [tinyint] NOT NULL,
[IP2] [tinyint] NOT NULL,
[IP3] [tinyint] NOT NULL,
[IP4] [tinyint] NOT NULL,
[IP5] [tinyint] NOT NULL,
[IP6] [tinyint] NOT NULL,
[IP7] [tinyint] NOT NULL,
[IP8] [tinyint] NOT NULL
) ON [PRIMARY]
GO
[CODE]
Delete pk stats from all players
Quote:
USE [SRO_VT_SHARD]
UPDATE _Char SET
DailyPK= 0,
TotalPK= 0,
PKPenaltyPoint= 0

Add new drop for unique or mob

Quote:
USE SRO_VT_SHARD_INIT
GO

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



SET @MonsterID = [Desired Monster ID] -- Check RefObjCommon to find the desired ID
SET @DropRatio = 0.5 -- 100% = 1 / 50% = 0.5 
SET @ItemID = [Desired Item ID] -- Check RefObjCommon to find the desired 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')

For 5 page inventory pet fix
[CODE]
Quote:
USE [SRO_VT_SHARD] 
GO 
/****** Object: StoredProcedure [dbo].[_AddNewCOS] Script Date: 02/10/2012 11:32:15 ******/ 
SET ANSI_NULLS OFF 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

ALTER PROCEDURE [dbo].[_AddNewCOS] 
@OwnerCharID int, 
@RefObjID int, 
@Level int, 
@HP int, 
@MP int, 
@HGP int, 
@KeeperNPC int, 
@State int, 
@InventorySize int, 
@itemID int, 
@PickItemConfig tinyint, 
@RentEndTime smalldatetime 
as 

begin tran 

declare @COS_ID int 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
-- pet_skill_item (ÃÖ¼±È£) 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 

declare @MaxInventorySize tinyint 
select @MaxInventorySize = InventorySize 
from _RefObjCommon, _RefObjChar 
where _RefObjCommon.ID = @RefObjID and _RefObjCommon.Link = _RefObjChar.ID 

if( @@rowcount = 0 or @MaxInventorySize < @InventorySize ) 
begin 
rollback tran 
return -5 
end 

insert into _CharCOS values (@OwnerCharID, @RefObjID, @HP, @MP, @KeeperNPC, @State, NULL, @Level, 0, @HGP, @PickItemConfig, @RentEndTime) 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
set @COS_ID = @@identity 
if (@COS_ID = 0 OR @@error <> 0) 
begin 
rollback tran 
return -1 
end 

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
-- pet_skill_item (&Atilde;&Ouml;¼±&Egrave;£) 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
if (@MaxInventorySize > 0) 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
begin 
declare @Slot int 
set @Slot = 0 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
-- pet_skill_item (&Atilde;&Ouml;¼±&Egrave;£) 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
while (@Slot < @MaxInventorySize) 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 
begin 
insert into _InvCOS values(@COS_ID, @Slot, 0) 
if (@@error <> 0) 
begin 
-- &Agrave;&Icirc;&ordm;¥&Aring;&auml;¸® »&yacute;¼&ordm; ½&Ccedil;&AElig;&ETH;! 
rollback tran 
return -2 
end 

set @Slot = @Slot + 1 
end 
end 

/* --´&otilde; &Agrave;&Igrave;»&oacute; &Agrave;&Igrave; &Auml;&Atilde;·³&iquest;&iexcl;´&Acirc; µ¥&Agrave;&Igrave;&Aring;&Iacute;¸¦ ³&Ouml;&Aacute;&ouml; ¾&Ecirc;´&Acirc;´&Ugrave;! 
UPDATE _Char SET EngagedCOS = @COS_ID WHERE CharID = @OwnerCharID 
IF (@@ERROR <> 0 OR @@ROWCOUNT = 0) 
BEGIN 
ROLLBACK TRANSACTION 
RETURN -3 
END 
*/ 
if( @itemID <> 0 ) 
begin 
update _Items set Data = @COS_ID where ID64 = @itemID 
if( @@error <> 0 or @@rowcount = 0 ) 
begin 
rollback transaction 
return -4 
end 

--BEGIN ADDITIONAL 112 SLOT IN GRAB PETS FOR 7684 DAYS 
declare @Serial64 bigint 
select @Serial64 = Serial64 from _Items where Data = @COS_ID 
exec _AddTimedJobForPet @COS_ID,5,22926,1992999999,1111708465,1,112,0,0,0, 0,0,@Serial64 
--BEGIN ADDITIONAL 112 SLOT IN GRAB PETS FOR 7684 DAYS 

end 

commit tran 

return @COS_ID