"Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWriter!

[Rango]

why my strdesc is null. I used SP on first page

0 461933 320888 3 2014-01-02 14:09:00.057 ?? ?(0x5faa) (546, 127) NULL

so Tool writenoice dont send msg and crash

the same problem

[Rango]

help me?

[Rango]

why my strdesc is null. I used SP on first page

0 461933 320888 3 2014-01-02 14:09:00.057 ?? ?(0x5faa) (546, 127) NULL

so Tool writenoice dont send msg and crash

[GusToma]

Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 39
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 44
Incorrect syntax near '@killername'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 45
Incorrect syntax near '@killername'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 48
Incorrect syntax near '@desc'.
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 50
Must declare the scalar variable "@jobString".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 62
Must declare the scalar variable "@jobType".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 64
Must declare the scalar variable "@jobString".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 69
Must declare the scalar variable "@jobDesc".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 73
Must declare the scalar variable "@jobDesc".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 76
Must declare the scalar variable "@jobType".
Msg 156, Level 15, State 1, Procedure _AddLogChar, Line 80
Incorrect syntax near the keyword 'END'.


!!

[lewy1000]

USE [LOG_DB]
GO
/****** Object:  StoredProcedure [dbo].[_AddLogChar]    Script Date: 02/07/2014 01:52:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER   procedure [dbo].[_AddLogChar] 
 @CharID		int,
 @eventID		tinyint,
 @data1		int,
 @data2		int,
@strPos		varchar(64),
 @desc		varchar(128)
as

IF  @eventID != 20)
BEGIN
	declare @Len_pos 	int
	declare @Len_desc	int
	set @Len_pos = len(@strPos)
	set @Len_desc = len @desc)

	if  @Len_pos > 0 and @Len_desc > 0)
	begin	
		insert _LogEventChar values @CharID, GetDate(), @eventID, @data1, @data2, @strPos, @desc)	
	end
	else if  @Len_pos > 0 and @Len_desc = 0)
	begin 	
		insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, EventPos) values @CharID, GetDate(), @eventID, @data1, @data2, @strPos)
	end
	else if  @Len_pos = 0 and @Len_desc > 0)
	begin 	
		insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2, strDesc) values @CharID, GetDate(), @eventID, @data1, @data2, @desc)
	end
	else
	begin
		insert _LogEventChar (CharID, EventTime, EventID, Data1, Data2) values @CharID, GetDate(), @eventID, @data1, @data2)
	end
END
IF  @eventID = 20) -- PVP
BEGIN
    IF   @desc LIKE '%Trader, Neutral, no freebattle team%'    -- Trader
        OR @desc LIKE '%Hunter, Neutral, no freebattle team%'    -- Hunter
        OR @desc LIKE '%Robber, Neutral, no freebattle team%'    -- Thief
        OR @desc like '%no job, Neutral, %no job, Neutral%'    -- Free PVP
    )
    BEGIN
        -- Get killer name
        DECLARE @killername VARCHAR(512) 
		SET @killername = @desc
        DECLARE @killeriD INT 
		SET @killeriD = 0
        SELECT @killername = REPLACE @killername, LEFT @killername, CHARINDEX('(', @killername)), '')
        SELECT @killername = REPLACE @killername, RIGHT @killername, CHARINDEX(')', REVERSE @killername))), '')
        SELECT @killeriD = CharID FROM [SRO_VT_SHARD_INIT].[dbo].[_Char] WHERE CharName16 = @killername
        -- Get job type
        DECLARE @jobString VARCHAR(10) 
		SET @jobString= LTRIM(RTRIM(SUBSTRING @desc, 5, 7)))
        DECLARE @jobType INT 
		SET @jobType= CASE
            WHEN @jobString LIKE 'Trader' THEN 1
            WHEN @jobString LIKE 'Robber' THEN 2
            WHEN @jobString LIKE 'Hunter' THEN 3
            ELSE 0 END
        -- Delete original log
        DELETE FROM _LogEventChar WHERE CharID = @CharID AND EventID = 20
            AND (strDesc LIKE '%Trader, Neutral, no freebattle team%'
            OR strDesc LIKE '%Hunter, Neutral, no freebattle team%'
            OR strDesc LIKE '%Robber, Neutral, no freebattle team%'
            OR @desc like '%no job, Neutral, %no job, Neutral%')
        -- Get additional info for notice message
        DECLARE @CharName VARCHAR(64) 
		SET @CharName = (SELECT CharName16 FROM [SRO_VT_SHARD_INIT].[dbo].[_Char] WHERE CharID = @CharID)
        DECLARE @jobDesc VARCHAR(32) 
		SET @jobDesc= CASE WHEN @jobType BETWEEN 1 AND 3 THEN 'Job Conflict' ELSE 'Free PVP' END
        DECLARE @strDesc VARCHAR(512)
        IF   @jobString LIKE 'Trader' OR @jobString LIKE 'Robber' OR @jobString LIKE 'Hunter')
        BEGIN
            -- If it's a Job Kill, then write character nicknames
            DECLARE @killerNickName VARCHAR(64) 
			SET @killerNickName = (SELECT NickName16 FROM [SRO_VT_SHARD_INIT].[dbo].[_Char] WHERE CharID = @killeriD)
            DECLARE @CharnickName VARCHAR(64) 
			SET @CharnickName= (SELECT NickName16 FROM [SRO_VT_SHARD_INIT].[dbo].[_Char] WHERE CharID = @CharID)
            SET @strDesc = '[' + @killerNickName + '] has just killed [' + @CharnickName + '] in [' + @jobDesc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
        END
        ELSE BEGIN
            -- If it's normal PVP Kill, write real character names
            SET @strDesc = '[' + @killername + '] has just killed [' + @CharName + '] in [' + @jobDesc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
        END
        -- Update the log
        INSERT INTO _LogEventPVP VALUES (0, @killeriD, @CharID, @jobType, GETDATE(), @strPos, @strDesc)
        -- Flag notice if it's not sent more than 5 minutes
        UPDATE _LogEventPVP SET isSent = 1 WHERE CharID = @CharID AND EventTime < DATEADD(MINUTE, -5, GETDATE())
    END
END

Script works great, but when I kill some1 then nothing added to DB :<

[ApecixX]

What is the cause of this error? Locations, please help me to correct the information I am writing Waiting

[MeGZONE]

My ERROR:


Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@EventID".
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@desc".
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@desc".
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '@killername'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '@killername'.
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@killername".
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@desc'.
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@jobString".
Msg 137, Level 15, State 2, Line 23
Must declare the scalar variable "@CharID".
Msg 137, Level 15, State 2, Line 29
Must declare the scalar variable "@CharID".
Msg 137, Level 15, State 2, Line 30
Must declare the scalar variable "@jobType".
Msg 137, Level 15, State 2, Line 32
Must declare the scalar variable "@jobString".
Msg 137, Level 15, State 2, Line 36
Must declare the scalar variable "@CharID".
Msg 137, Level 15, State 2, Line 37
Must declare the scalar variable "@CharnickName".
Msg 137, Level 15, State 2, Line 41
Must declare the scalar variable "@killername".
Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@CharID".
Msg 137, Level 15, State 2, Line 46
Must declare the scalar variable "@CharID".


and Attachs SP

[Showtek]

AddLogChar gives me these errors :

Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 81
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 86
Incorrect syntax near '@killername'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 87
Incorrect syntax near '@killername'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 90
Incorrect syntax near '@desc'.
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 92
Must declare the scalar variable "@jobString".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 104
Must declare the scalar variable "@jobType".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 106
Must declare the scalar variable "@jobString".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 111
Must declare the scalar variable "@jobDesc".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 115
Must declare the scalar variable "@jobDesc".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 118
Must declare the scalar variable "@jobType".
Msg 156, Level 15, State 1, Procedure _AddLogChar, Line 122
Incorrect syntax near the keyword 'END'.

[Royalblade]

Seriously you guys are all stupid as fuck.
The shit is just missing a few brackets... can you even set those yourself?

[DarSage]

:junglejane: ok I'm new to this, so I only get that Systema Copy and execute these queries Explain to me please

[RenePunik]

Error when executing query _AddLogChar Stored producer

[LethalSRO]

thanks

[asker7r]

thanksss :D

[Razor1337]

Epic release keep it up!

[Muhab Ashraf]

hey @Witchy Moo , i'm working on 2005 i edit the _AddLogChar but i still can't fix it ! i get many errors also edited declares as u said
can u take a look and try to fix it or just tell me how !
here is my error msgs
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 45
Incorrect syntax near '@killername'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 46
Incorrect syntax near '@killername'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 46
Incorrect syntax near 'REVERSE'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 50
Incorrect syntax near '@desc'.
Msg 156, Level 15, State 1, Procedure _AddLogChar, Line 52
Incorrect syntax near the keyword 'WHEN'.
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 66
Must declare the scalar variable "@jobType".
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 68
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 71
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure _AddLogChar, Line 72
Incorrect syntax near '('.
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 73
Must declare the scalar variable "@killerNickName".
Msg 137, Level 15, State 2, Procedure _AddLogChar, Line 80
Must declare the scalar variable "@jobType".
Msg 156, Level 15, State 1, Procedure _AddLogChar, Line 84
Incorrect syntax near the keyword 'END'.


the full _AddLogChar in attachment

Fixed Thanks

[tschulian]

hm..... cant get trough it.

[hancook1st]

i have this problem can anyone help???

[ilowe]

How to level restrictions? Example Lvl 95 + Killed

[ilowe]

How could buffer instead of goods?

[Eroticus]

To everyone who get many sql errors and his brain has allot of blue screens :D

this is one is working just change eroticuskingz0r to SRO_VT_SHARD or your database name

CRTL +H > SEARCH "eroticuskingz0r"

Change to "MYDATABASENAME" (SRO_VT_SHARD)


add this script after last END .

IF (@EventID = 20) -- PVP
BEGIN
IF   @desc LIKE '%Trader, Neutral, no freebattle team%' -- Trader
OR  @desc LIKE '%Hunter, Neutral, no freebattle team%' -- Hunter
OR  @desc LIKE '%Robber, Neutral, no freebattle team%' -- Thief
OR  @desc like '%no job, Neutral, %no job, Neutral%' -- Free PVP
)
BEGIN
-- Get killer name
DECLARE  @killername VARCHAR(512) =  @desc
DECLARE  @killeriD INT = 0
SELECT  @killername = REPLACE  @killername, LEFT  @killername, CHARINDEX('(',  @killername)), '')
SELECT  @killername = REPLACE  @killername, RIGHT  @killername, CHARINDEX(')', REVERSE  @killername))), '')
SELECT  @killeriD = CharID FROM [eroticuskingz0rDB].[dbo].[_Char] WHERE CharName16 =  @killername
-- Get job type
DECLARE  @jobString VARCHAR(10) = LTRIM(RTRIM(SUBSTRING  @desc, 5, 7)))
DECLARE  @jobType INT = CASE
WHEN  @jobString LIKE 'Trader' THEN 1
WHEN  @jobString LIKE 'Robber' THEN 2
WHEN  @jobString LIKE 'Hunter' THEN 3
ELSE 0 END
-- Delete original log
DELETE FROM _LogEventChar WHERE CharID =  @CharID AND EventID = 20
AND (strDesc LIKE '%Trader, Neutral, no freebattle team%'
OR strDesc LIKE '%Hunter, Neutral, no freebattle team%'
OR strDesc LIKE '%Robber, Neutral, no freebattle team%'
OR  @desc like '%no job, Neutral, %no job, Neutral%')
-- Get additional info for notice message
DECLARE  @CharName VARCHAR(64) = (SELECT CharName16 FROM [eroticuskingz0rDB].[dbo].[_Char] WHERE CharID =  @CharID)
DECLARE  @jobDesc VARCHAR(32) = CASE WHEN  @jobType BETWEEN 1 AND 3 THEN 'Job Conflict' ELSE 'Free PVP' END
DECLARE @strDesc VARCHAR(512)
IF   @jobString LIKE 'Trader' OR  @jobString LIKE 'Robber' OR  @jobString LIKE 'Hunter')
BEGIN
-- If it's a Job Kill, then write character nicknames
DECLARE  @killerNickName VARCHAR(64) = (SELECT NickName16 FROM [eroticuskingz0rDB].[dbo].[_CharNickNameList] WHERE CharID =  @killeriD)
DECLARE  @CharnickName VARCHAR(64) = (SELECT NickName16 FROM [eroticuskingz0rDB].[dbo].[_CharNickNameList] WHERE CharID =  @CharID)
SET @strDesc = '[' +  @killerNickName + '] has just killed [' +  @CharnickName + '] in [' +  @jobDesc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
END
ELSE BEGIN
-- If it's normal PVP Kill, write real character names
SET @strDesc = '[' +  @killername + '] has just killed [' +  @CharName + '] in [' +  @jobDesc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
END
-- Update the log
INSERT INTO _LogEventPVP VALUES (0,  @killeriD,  @CharID,  @jobType, GETDATE(), @strPos, @strDesc)
-- Flag notice if it's not sent more than 5 minutes
UPDATE _LogEventPVP SET isSent = 1 WHERE CharID =  @CharID AND EventTime < DATEADD(MINUTE, -5, GETDATE())
END
END

[infor]

help me to activate it on my server

[DarSage]

them'm honest I'm a little rough with this part of Add_Charlog that more should I add and where in the DB
someone can take their time and explain well .. everything fits my DB but kill 10 times in the game is not active and not recorded in logeventchar

[infor]

help please error ejecute code sro_vt_log

- - - Updated - - -

To everyone who get many sql errors and his brain has allot of blue screens :D

this is one is working just change eroticuskingz0r to SRO_VT_SHARD or your database name

CRTL +H > SEARCH "eroticuskingz0r"

Change to "MYDATABASENAME" (SRO_VT_SHARD)


add this script after last END .

IF (@EventID = 20) -- PVP
BEGIN
IF   @desc LIKE '%Trader, Neutral, no freebattle team%' -- Trader
OR  @desc LIKE '%Hunter, Neutral, no freebattle team%' -- Hunter
OR  @desc LIKE '%Robber, Neutral, no freebattle team%' -- Thief
OR  @desc like '%no job, Neutral, %no job, Neutral%' -- Free PVP
)
BEGIN
-- Get killer name
DECLARE  @killername VARCHAR(512) =  @desc
DECLARE  @killeriD INT = 0
SELECT  @killername = REPLACE  @killername, LEFT  @killername, CHARINDEX('(',  @killername)), '')
SELECT  @killername = REPLACE  @killername, RIGHT  @killername, CHARINDEX(')', REVERSE  @killername))), '')
SELECT  @killeriD = CharID FROM [eroticuskingz0rDB].[dbo].[_Char] WHERE CharName16 =  @killername
-- Get job type
DECLARE  @jobString VARCHAR(10) = LTRIM(RTRIM(SUBSTRING  @desc, 5, 7)))
DECLARE  @jobType INT = CASE
WHEN  @jobString LIKE 'Trader' THEN 1
WHEN  @jobString LIKE 'Robber' THEN 2
WHEN  @jobString LIKE 'Hunter' THEN 3
ELSE 0 END
-- Delete original log
DELETE FROM _LogEventChar WHERE CharID =  @CharID AND EventID = 20
AND (strDesc LIKE '%Trader, Neutral, no freebattle team%'
OR strDesc LIKE '%Hunter, Neutral, no freebattle team%'
OR strDesc LIKE '%Robber, Neutral, no freebattle team%'
OR  @desc like '%no job, Neutral, %no job, Neutral%')
-- Get additional info for notice message
DECLARE  @CharName VARCHAR(64) = (SELECT CharName16 FROM [eroticuskingz0rDB].[dbo].[_Char] WHERE CharID =  @CharID)
DECLARE  @jobDesc VARCHAR(32) = CASE WHEN  @jobType BETWEEN 1 AND 3 THEN 'Job Conflict' ELSE 'Free PVP' END
DECLARE @strDesc VARCHAR(512)
IF   @jobString LIKE 'Trader' OR  @jobString LIKE 'Robber' OR  @jobString LIKE 'Hunter')
BEGIN
-- If it's a Job Kill, then write character nicknames
DECLARE  @killerNickName VARCHAR(64) = (SELECT NickName16 FROM [eroticuskingz0rDB].[dbo].[_CharNickNameList] WHERE CharID =  @killeriD)
DECLARE  @CharnickName VARCHAR(64) = (SELECT NickName16 FROM [eroticuskingz0rDB].[dbo].[_CharNickNameList] WHERE CharID =  @CharID)
SET @strDesc = '[' +  @killerNickName + '] has just killed [' +  @CharnickName + '] in [' +  @jobDesc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
END
ELSE BEGIN
-- If it's normal PVP Kill, write real character names
SET @strDesc = '[' +  @killername + '] has just killed [' +  @CharName + '] in [' +  @jobDesc + '] mode on [' + CONVERT(NVARCHAR(30), GETDATE(), 0) + ']'
END
-- Update the log
INSERT INTO _LogEventPVP VALUES (0,  @killeriD,  @CharID,  @jobType, GETDATE(), @strPos, @strDesc)
-- Flag notice if it's not sent more than 5 minutes
UPDATE _LogEventPVP SET isSent = 1 WHERE CharID =  @CharID AND EventTime < DATEADD(MINUTE, -5, GETDATE())
END
END

help please error ejecute code sro_vt_log

[Eroticus]

sql code

https://onedrive.live.com/?cid=f77bb...FEC5D16%212934

[foremus95]

Hi, i want to send the notice for only specific player, for example who are in job mode. How can i do that