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

Quote:

---

Originally Posted by mylove0990

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

help me?

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

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'.


!!

Code:

---

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 :<

http://i.hizliresim.com/xBnRR9.png



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

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

AddLogChar gives me these errors :

Code:

---

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'.

---

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

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

Error when executing query _AddLogChar Stored producer

thanks :drool:

thanksss :D

Epic release keep it up!

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

hm..... cant get trough it.

i have this problem can anyone help???
http://im73.gulfup.com/Qaomg2.png

How to level restrictions? Example Lvl 95 + Killed

How could buffer instead of goods?

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 .

Code:

---

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

---

http://i.imgur.com/D9LoDcn.png

help me to activate it on my server

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

help please error ejecute code sro_vt_log

- - - Updated - - -

Quote:

---

Originally Posted by 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 .

Code:

---

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

---

http://i.imgur.com/D9LoDcn.png

---

help please error ejecute code sro_vt_log

https://www.youtube.com/watch?v=QItGsW8KBCM

sql code

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

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