[SERVICE] SQL Queries / Procedures

06/30/2014 14:49 Syloxx#1
Hello Elitepvpers Community,

because many administrators are not experience enough to develop in SQL and don't wanna ask every 2nd guy to write a simple query iŽll open this thread.

In this thread a client can write some ideas with so many information's as possible and a voluntary develop the procedure / query for it then.

for sure iŽll develop also simple queries.

if u want some complex queries / procedures i can create them as well for a payment
06/30/2014 14:50 Media.#2
[Only registered and activated users can see links. Click Here To Register...]
06/30/2014 14:53 Syloxx#3
Quote:
Originally Posted by Media. View Post
[Only registered and activated users can see links. Click Here To Register...]
i guess u don't get the function of this thread ? ;)

a guy can post a idea and if someone wanna develop it then he do it
06/30/2014 14:55 Media.#4
I guess you don't get the function of the mentioned thread I posted. In fact it's exactly the same.
06/30/2014 14:57 Syloxx#5
Quote:
Originally Posted by Media. View Post
I guess you don't get the function of the mentioned thread I posted. In fact it's exactly the same.
u can request for php developer or sql developer etc but not individual queries now plz dont fill the thread already with ur spam
06/30/2014 15:06 Media.#6
Oh, so you mean someone can leave a request, and another one can offer his help?
Just like [Only registered and activated users can see links. Click Here To Register...]?

Looking at the other thread it actually says
[Only registered and activated users can see links. Click Here To Register...]
Which is for ALL requests, and of course to avoid a thread you just created.
06/30/2014 15:09 Syloxx#7
Quote:
Originally Posted by Media. View Post
Oh, so you mean someone can leave a request, and another one can offer his help?
Just like [Only registered and activated users can see links. Click Here To Register...]?

Looking at the other thread it actually says
[Only registered and activated users can see links. Click Here To Register...]
Which is for ALL requests, and of course to avoid a thread you just created.
Request People???
i guess u killed your self with that post
the function of this thread is simply

find a team or request ppl for the team so there is no topic like:
'HEY MENZ MEN LOOKING FOR GEMAMESTAR'

and how i said stop spam a mod will choose whether it belongs in there or not
06/30/2014 15:11 Media.#8
Quote:
Originally Posted by Syloxx View Post
Request People???

i guess u killed your self with that post
Are you aware that this is just a picture of the actual thread name?
If you spam the main section then you gotta expect spam in that thread.
06/30/2014 15:29 mane123#9
Syloxx no news for your server ?!

:(
06/30/2014 16:04 Ŧєคгlєรร#10
Quote:
Originally Posted by Media. View Post
Are you aware that this is just a picture of the actual thread name?
If you spam the main section then you gotta expect spam in that thread.
Media... This topic is mainly for small releases aka. requests for the queries - not team member, so I don't see any issue, that this thread is useless.

Quote:
Originally Posted by mane123 View Post
Syloxx no news for your server ?!
I think, that he has problem with ordering dedicated server as I have read on last posts.

@Thread:
Okay, so here's my really old query for cleaning vSRO database (2013, lol). It might be useful, if someone want to make his life easy and use just one line to clean whole database. Not sure is it working, just found that on my old disk, ah and warning - there might be small SQL Injection...

PHP Code:
-- Query Usage: exec _ClearDB DatabaseName, DatabaseType
-- Example: exec _ClearDB SRO_VT_ACCOUNT, ACC
-- Available options for DatabaseType: ACC - Account, SHR - Shard, LOG - Log
-- Supported: vSRO 188 
-- Author: Fearless, 3 Februrary 2013.

CREATE PROCEDURE [dbo].[_ClearDB]
@DBName varchar(32),
@DBType varchar(32)
AS

IF (@DBName != NULL or @DBType = NULL)
    print 'Wrong database name or settings!'
    else

IF @DBType = 'ACC'
BEGIN
    PRINT 'Detected database type: ACCOUNT'
    EXECUTE('USE ' + @DBName)
    PRINT 'Using' +@DBName+ ' as destination database'

    TRUNCATE TABLE [dbo].[__SiegeFortressStatus__]
    TRUNCATE TABLE [dbo].[_BlockedUser]
    TRUNCATE TABLE [dbo].[_CasGMChatLog]
    TRUNCATE TABLE [dbo].[_Notice]
    TRUNCATE TABLE [dbo].[_Punishment]
    TRUNCATE TABLE [dbo].[_ServiceManagerLog]
    TRUNCATE TABLE [dbo].[_ShardCurrentUser]
    TRUNCATE TABLE [dbo].[_SMCLog]
    TRUNCATE TABLE [dbo].[QuaySoEpoint]
    TRUNCATE TABLE [dbo].[SK_CharRenameLog]
    TRUNCATE TABLE [dbo].[SK_DownLevelLog]
    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_SubtractSilk_VAS]
    TRUNCATE TABLE [dbo].[SR_ShardCharNames]
    TRUNCATE TABLE [dbo].[TB_Net2e]
    TRUNCATE TABLE [dbo].[TB_Net2e_Bak]
    TRUNCATE TABLE [dbo].[tb_partnerInfo]
    TRUNCATE TABLE [dbo].[tb_paygate_trans]
    TRUNCATE TABLE [dbo].[TB_User]
    TRUNCATE TABLE [dbo].[TB_User_Bak]
    TRUNCATE TABLE [dbo].[Test_HN]
    PRINT +@DBName+ 'has been successfully cleaned!'
END

ELSE
    PRINT 'Wrong database type! Please check the query.'

IF @DBType = 'SHR'
BEGIN
    PRINT 'Detected database type: SHARD'
    EXECUTE('USE ' + @DBName)
    PRINT 'Using' +@DBName+ ' as destination database'

    TRUNCATE TABLE [dbo].[_Chest]
    TRUNCATE TABLE [dbo].[_ChestInfo]
    TRUNCATE TABLE [dbo].[_User]
    TRUNCATE TABLE [dbo].[_OpenMarket]
    DELETE FROM [dbo].[_AccountJID] 
    TRUNCATE TABLE [dbo].[_GuildWar]
    TRUNCATE TABLE [dbo].[_GuildMember]
    TRUNCATE TABLE [dbo].[_GuildChest]
    TRUNCATE TABLE [dbo].[_SiegeFortressStruct]
    TRUNCATE TABLE [dbo].[_SiegeFortressObject]
    TRUNCATE TABLE [dbo].[_SiegeFortressItemForge]
    DELETE FROM [dbo].[_SiegeFortress]
    INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (1)
    INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (3)
    INSERT INTO [dbo].[_SiegeFortress](FortressID) VALUES (6)
    UPDATE [dbo].[_AlliedClans] SET Ally1 = '0', Ally2 = '0', Ally3 = '0', Ally4 = '0', Ally5 = '0', Ally6 = '0', Ally7 = '0', Ally8 = '0'
    DELETE FROM [dbo].[_Guild] WHERE ID > 0
    DELETE FROM [dbo].[_AlliedClans] WHERE ID > 0
    TRUNCATE TABLE [dbo].[_InventoryForAvatar]
    TRUNCATE TABLE [dbo].[_TrainingCampSubMentorHonorPoint]
    TRUNCATE TABLE [dbo].[_BlockedWhisperers]
    TRUNCATE TABLE [dbo].[_TrainingCampMember]
    TRUNCATE TABLE [dbo].[_CharTrijobSafeTrade]
    DELETE FROM [dbo].[_CharTrijob] WHERE CharID > 0
    TRUNCATE TABLE [dbo].[_TimedJob]
    TRUNCATE TABLE [dbo].[_StaticAvatar]
    TRUNCATE TABLE [dbo].[_Inventory]
    TRUNCATE TABLE [dbo].[_Memo]
    TRUNCATE TABLE [dbo].[_FleaMarketNetwork]
    TRUNCATE TABLE [dbo].[_Friend]
    TRUNCATE TABLE [dbo].[_CharSkillMastery]
    TRUNCATE TABLE [dbo].[_CharSkill]
    TRUNCATE TABLE [dbo].[_InvCOS]
    DELETE FROM [dbo].[_CharCOS] WHERE ID > 0
    DELETE FROM [dbo].[_Char] WHERE CharID > 0
    TRUNCATE TABLE [dbo].[_CharCollectionBook]
    TRUNCATE TABLE [dbo].[_CharInstanceWorldData]
    TRUNCATE TABLE [dbo].[_CharNameList]
    TRUNCATE TABLE [dbo].[_CharNickNameList]
    TRUNCATE TABLE [dbo].[_CharQuest]
    TRUNCATE TABLE [dbo].[_ClientConfig]
    TRUNCATE TABLE [dbo].[_DeletedChar]
    TRUNCATE TABLE [dbo].[_GPHistory]
    TRUNCATE TABLE [dbo].[_InventoryForLinkedStorage]
    TRUNCATE TABLE [dbo].[_ItemPool]
    DELETE FROM [dbo].[_Items] WHERE ID64 > 0
    TRUNCATE TABLE [dbo].[_Skill_BaoHiem_TNET]
    TRUNCATE TABLE [dbo].[_TimedJobForPet]
    TRUNCATE TABLE [dbo].[_TrainingCampBuffStatus]
    TRUNCATE TABLE [dbo].[_TrainingCampHonorRank]
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (1)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (2)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (3)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (4)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (5)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (6)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (7)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (8)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (9)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (10)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (11)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (12)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (13)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (14)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (15)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (16)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (17)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (18)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (19)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (20)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (21)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (22)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (23)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (24)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (25)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (26)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (27)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (28)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (29)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (30)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (31)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (32)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (33)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (34)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (35)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (36)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (37)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (38)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (39)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (40)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (41)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (42)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (43)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (44)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (45)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (46)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (47)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (48)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (49)
    INSERT INTO [dbo].[_TrainingCampHonorRank](Ranking) VALUES (50)
    EXEC [dbo].[_TRAINING_CAMP_UPDATEHONORRANK]
    DELETE FROM [dbo].[_TrainingCamp]
    TRUNCATE TABLE [dbo].[Tab_RefRanking_HunterContribution]
    TRUNCATE TABLE [dbo].[Tab_RefRanking_RobberContribution]
    TRUNCATE TABLE [dbo].[Tab_RefRanking_TraderContribution]
END

ELSE
    PRINT 'Wrong database type! Please check the query.'

IF @DBType = 'LOG'
BEGIN
    PRINT 'Detected database type: LOG'
    EXECUTE('USE ' + @DBName)
    PRINT 'Using' +@DBName+ ' as destination database'

    TRUNCATE TABLE [dbo].[_LogCashItem]
    TRUNCATE TABLE [dbo].[_LogEventChar]
    TRUNCATE TABLE [dbo].[_LogEventItem]
    TRUNCATE TABLE [dbo].[_LogEventSiegeFortress]
    TRUNCATE TABLE [dbo].[_LogSchedule]
    TRUNCATE TABLE [dbo].[_LogServerEvent]
    PRINT +@DBName+ 'has been successfully cleaned!'
END

ELSE
    PRINT 'Wrong database type! Please check the query.' 
P.S. The queries are from rz, I have just glued them >_>
06/30/2014 16:12 Syloxx#11
here is a ban user by charname query if needen

Code:
CREATE PROCEDURE _BanUserByCharname
	@Charname varchar(16),
	@BanReason varchar(1024),
	@BanStartTime datetime,
	@BanEndTime datetime
AS

	DECLARE @JID INT,
			@StrUserID varchar(16),
			@Serial INT,
			@CharID INT
	
	SELECT @CharID = CharID FROM SRO_CH_SHARD.._Char WHERE CharName16=@Charname
	SELECT @JID = UserJID FROM SRO_CH_SHARD.._User WHERE CharID=@CharID
	SELECT @StrUserID = StrUserID FROM TB_User WHERE JID=@JID
	
	--Check is user exists
	IF (@CharID = 0 OR @CharID IS NULL)
	BEGIN
		PRINT 'Charname not exists!'
		RETURN
	END
					
	--Check if user is already blocked
	IF( EXISTS( SELECT UserJID FROM _BlockedUser WITH(NOLOCK) WHERE UserJID = @JID AND timeBegin < GETDATE()))
	BEGIN
		PRINT 'User already banned!'
		RETURN
	END
	
	INSERT INTO _Punishment VALUES (@JID , 1 , 'Database' , 64 , @Charname , @CharID , 0 , 'Database' , @BanReason , @BanStartTime , @BanStartTime , @BanEndTime , @BanEndTime , 1)

	--Generate Serial
	SET @Serial = @@IDENTITY
	IF( @@ROWCOUNT = 0 or @@ERROR <> 0 or @Serial = 0 or @Serial is null)
	BEGIN
		PRINT 'Generate Serial failed!'
		RETURN
	END
	
	INSERT INTO _BlockedUser VALUES (@JID , @StrUserID , 1 , @Serial , @BanStartTime , @BanEndTime)
06/30/2014 16:33 Media.#12
Quote:
Originally Posted by Ŧєคгlєรร View Post
Media... This topic is mainly for small releases aka. requests for the queries - not team member, so I don't see any issue, that this thread is useless.
If this has the sole purpose of a query collection there's already a thread like that [Only registered and activated users can see links. Click Here To Register...]
Either way this belong to the release section then.
06/30/2014 16:58 Ŧєคгlєรร#13
Quote:
Originally Posted by Media. View Post
If this has the sole purpose of a query collection there's already a thread like that [Only registered and activated users can see links. Click Here To Register...]
Either way this belong to the release section then.
Can you see requests in khaledlollol's thread? Because me not.