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

It's not working for me the job nicknames, if I switch to real char names it works.~

/edit

It works now, I changed to this

Quote:

---

DECLARE @KillerNickName VARCHAR(200)
DECLARE @CharNickName VARCHAR(200)
select @KillerNickName = NickName16 FROM [SRO_VT_SHARD].[dbo].[_CharNickNameList] WHERE CharID = @KillerID
select @CharNickName = NickName16 FROM [SRO_VT_SHARD].[dbo].[_CharNickNameList] WHERE CharID = @CharID

---

It wasn't working because players have more than 1 alias associated to their charid.. so DB doesnt know which one to choose, now it chooses the 1st name from the list.
Isn't there a way to check which one they're using now? If not then I'll just use real char names.

And now found out that it sends a notice when the user kills or gets killed by a Job mob...

Wish people did more testing before releasing, but thanks.

Here's a dirty hack to not show monster kills while job, adapt it to your own noticewriter.

Quote:

---

IF (@veremosjob LIKE '%Monster%')
BEGIN
SELECT @@VERSION
END
ELSE BEGIN
SELECT dbo.WriteTextFile(@veremosjob, @randomfile, 0)
END
-- Update the log
INSERT INTO _LogEventPVP VALUES (0, @KillerID, @CharID, @JobType, GETDATE(), @strPos, @strDesc)
UPDATE _LogEventPVP SET isSent = 1 WHERE CharID = @CharID AND EventTime < DATEADD(MINUTE, -5, GETDATE())
SELECT dbo.WriteTextFile(@veremos, @randomfile, 0)

---

Also would it be possible to make something that checks if the kill notice is the same as the last one and not post if it is, to avoid flood, instead of 5 minute timer.

Quote:

---

Originally Posted by isozy

It's not working for me the job nicknames, if I switch to real char names it works.~

/edit

It works now, I changed to this


It wasn't working because players have more than 1 alias associated to their charid.. so DB doesnt know which one to choose, now it chooses the 1st name from the list.
Isn't there a way to check which one they're using now? If not then I'll just use real char names.

---

The query was made under SQL2008 environment. For SQL-2005 You have to first "DECLARE", then another line doing "SET", because doing DECLARE at the same line assigning variable only works in SQL-2008.

About getting charracter nickname, do "SELECT TOP (1)" instead of "SELECT", but we wouldn't know which of the name the player is using right now because there are no ID column, I forgot that a player can have multiple nicknames and selecting all of those could give an empty result for single variable.

cheers ;)

Quote:

---

Originally Posted by isozy

And now found out that it sends a notice when the user kills or gets killed by a Job mob...

Wish people did more testing before releasing, but thanks.

Here's a dirty hack to not show monster kills while job, adapt it to your own noticewriter.



Also would it be possible to make something that checks if the kill notice is the same as the last one and not post if it is, to avoid flood, instead of 5 minute timer.

---

About this I wouldn't know, I didn't include monsters in the code. Good tips though :)

Quote:

---

Originally Posted by Witchy Moo

The query was made under SQL2008 environment. For SQL-2005 You have to first "DECLARE", then another line doing "SET", because doing DECLARE at the same line assigning variable only works in SQL-2008.

About getting charracter nickname, do "SELECT TOP (1)" instead of "SELECT", but we wouldn't know which of the name the player is using right now because there are no ID column, I forgot that a player can have multiple nicknames and selecting all of those could give an empty result for single variable.

cheers ;)

About this I wouldn't know, I didn't include monsters in the code. Good tips though :)

---

I'm using 2008 R2... I'm sure I speak english well enough for you to understand me, so let's try again.

1. It wasn't working because players have more than ONE NAME for their jobs... example if you have two characters, 1 hunter and 1 thief, you will have 2 names for the same CHARID.
1.1 Database doesn't know which name to use for CHARID because theres more than one, therefore doesn't send the notice.
1.2 Because I used DECLARE and SET it started sending the notice, I assume it just chose a random name from the list.

2. You didn't include monsters in the code, let me remind you of the fundamentals of Silkroad jobs. When you go do a trade or you go steal a trade being a Thief, if you have merchandise there will be NPC hunters/thiefs killing you.
2.1 If you die by one of those NPC thiefs or hunters that spawn while you are on JOB it will send out a notice saying you got killed by the NPC.

Quote:

---

Originally Posted by isozy

I'm using 2008 R2... I'm sure I speak english well enough for you to understand me, so let's try again.

---

Sure

Quote:

---

Originally Posted by isozy

1. It wasn't working because players have more than ONE NAME for their jobs... example if you have two characters, 1 hunter and 1 thief, you will have 2 names for the same CHARID.

---

The query works in general, with 1 name, you just said "it doesn't work" as in general meaning. As you said in your first post:

Quote:

---

Originally Posted by isozy

It's not working for me the job nicknames, if I switch to real char names it works.~

/edit

---

Right there.

Quote:

---

Originally Posted by isozy

1.1 Database doesn't know which name to use for CHARID because theres more than one, therefore doesn't send the notice.

---

Yes, I noticed that. that's why I replied with this:

Quote:

---

Originally Posted by Witchy Moo

...but we wouldn't know which of the name the player is using right now because there are no ID column, I forgot that a player can have multiple nicknames and selecting all of those could give an empty result for single variable...

---

So it's up to you guys how you wanna hack the query.

Quote:

---

Originally Posted by isozy

1.2 Because I used DECLARE and SET it started sending the notice, I assume it just chose a random name from the list.

---

Changing the DECLARE @variable TYPE = value to DECLARE and SET don't have anything to do with the goal of sending the notices. You did changed the single line in to multiple lines and you said "it works" after that and not before, which makes me think multiple lines made the query works in your server, which also makes me think you're using SQL 2005 since single line declaration doesn't work in SQL 2005. Or maybe I'm just too stupid to understand what you meant by that.

Quote:

---

Originally Posted by isozy

2. You didn't include monsters in the code, let me remind you of the fundamentals of Silkroad jobs. When you go do a trade or you go steal a trade being a Thief, if you have merchandise there will be NPC hunters/thiefs killing you.
2.1 If you die by one of those NPC thiefs or hunters that spawn while you are on JOB it will send out a notice saying you got killed by the NPC.

---

Like I said, good tips, good info, thanks for reminding. You can do as you pleased with the query codes. Perhaps you might even share with us when you got it working under your modifications :)

cheers

Nah, I was hoping you could help, I'm not very experienced I just hack my way in.

Quote:

---

Changing the DECLARE @variable TYPE = value to DECLARE and SET don't have anything to do with the goal of sending the notices. You did changed the single line in to multiple lines and you said "it works" after that and not before, which makes me think multiple lines made the query works in your server, which also makes me think you're using SQL 2005 since single line declaration doesn't work in SQL 2005. Or maybe I'm just too stupid to understand what you meant by that.

---

It didn't work with multiple names and the way I did it made it work with multiple names, we just need a way to verify which one is the current one.

Quote:

---

Originally Posted by isozy

Nah, I was hoping you could help, I'm not very experienced I just hack my way in.



It didn't work with multiple names and the way I did it made it work with multiple names, we just need a way to verify which one is the current one.

---

Well, I'm just too dumb to do that on my own :ehh:, so, by all means, do that, and perhaps it'll be nice if you can share after.

cheers

Quote:

---

Originally Posted by Witchy Moo

Well, I'm just too dumb to do that on my own :ehh:, so, by all means, do that, and perhaps it'll be nice if you can share after.

cheers

---

I didnt work pls help me

http://u1312.hizliresim.com/1j/k/vmq8j.png

AddLogChar

Quote:

---

USE [SRO_VT_LOG]
GO
/****** Object: StoredProcedure [dbo].[_AddLogChar] Script Date: 12/17/2013 20:25:35 ******/
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
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
return -1
end
else if (@len_pos > 0 and @len_desc = 0)
begin
return -2
end
else if (@len_pos = 0 and @len_desc > 0)
begin
return -3
end
else
begin
return -4
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 [SRO_VT_SHARD].[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 [SRO_VT_SHARD].[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 [SRO_VT_SHARD].[dbo].[_CharNickNameList] WHERE CharID = @KillerID)
DECLARE @CharNickName VARCHAR(64) = (SELECT NickName16 FROM [SRO_VT_SHARD].[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

---

Quote:

---

Originally Posted by ahmetagcan

I didnt work pls help me

---

First:
change this:

Code:

---

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       
                return -1
        end
        else if  @Len_pos > 0 and @Len_desc = 0)
        begin       
                return -2
        end
        else if  @Len_pos = 0 and @Len_desc > 0)
        begin       
                return -3
        end
        else
        begin       
                return -4
        end

---

to this:

Code:

---

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       
                return -1
        end
        else if  @Len_pos > 0 and @Len_desc = 0)
        begin       
                return -2
        end
        else if  @Len_pos = 0 and @Len_desc > 0)
        begin       
                return -3
        end
        else
        begin       
                return -4
        end
END

---

Second, make sure your SR_GameServer is LOGGING, again and again and again I repeat, if you use Evangelion SR_GameServer and you disable the log inside the .ini file, it will not log anything at all. So make sure it's working. How? well you seems to put "-1" to all conditions there so check out the _LogEventItem table, maybe there are some records there, if not, means MAYBE your server is not logging anything.

good luck

Quote:

---

Originally Posted by Witchy Moo

First:
change this:

Code:

---

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       
                return -1
        end
        else if  @Len_pos > 0 and @Len_desc = 0)
        begin       
                return -2
        end
        else if  @Len_pos = 0 and @Len_desc > 0)
        begin       
                return -3
        end
        else
        begin       
                return -4
        end

---

to this:

Code:

---

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       
                return -1
        end
        else if  @Len_pos > 0 and @Len_desc = 0)
        begin       
                return -2
        end
        else if  @Len_pos = 0 and @Len_desc > 0)
        begin       
                return -3
        end
        else
        begin       
                return -4
        end
END

---

Second, make sure your SR_GameServer is LOGGING, again and again and again I repeat, if you use Evangelion SR_GameServer and you disable the log inside the .ini file, it will not log anything at all. So make sure it's working. How? well you seems to put "-1" to all conditions there so check out the _LogEventItem table, maybe there are some records there, if not, means MAYBE your server is not logging anything.

good luck

---

I changed new error

http://u1312.hizliresim.com/1j/l/vn38h.png

Quote:

---

Originally Posted by ahmetagcan

I changed new error

http://u1312.hizliresim.com/1j/l/vn38h.png

---

you forgot "(" sign before "@len_pos" on every line. I think it's pretty CLEAR in your SQL window that the RED underline appears under the wrong statement. Well things like this happens when you just copy paste without reading it.

People should stop copy paste stuff. YOU WILL NEVER LEARN ANYTHING OUT OF IT ! SQL can be really simple, just give it some time.

I got his error when I try to modify _AddLogChar SP

Quote:

---

Mens. 137, Nivel 15, Estado 2, Línea 1
Must declare the scalar variable "@EventID".
Mens. 137, Nivel 15, Estado 2, Línea 3
Must declare the scalar variable "@Desc".
Mens. 137, Nivel 15, Estado 2, Línea 10
Must declare the scalar variable "@Desc".
Mens. 137, Nivel 15, Estado 2, Línea 12
Must declare the scalar variable "@KillerName".
Mens. 137, Nivel 15, Estado 2, Línea 13
Must declare the scalar variable "@KillerName".
Mens. 137, Nivel 15, Estado 2, Línea 14
Must declare the scalar variable "@KillerName".
Mens. 137, Nivel 15, Estado 2, Línea 16
Must declare the scalar variable "@Desc".
Mens. 137, Nivel 15, Estado 2, Línea 18
Must declare the scalar variable "@JobString".
Mens. 137, Nivel 15, Estado 2, Línea 23
Must declare the scalar variable "@CharID".
Mens. 137, Nivel 15, Estado 2, Línea 29
Must declare the scalar variable "@CharID".
Mens. 137, Nivel 15, Estado 2, Línea 30
Must declare the scalar variable "@JobType".
Mens. 137, Nivel 15, Estado 2, Línea 32
Must declare the scalar variable "@JobString".
Mens. 137, Nivel 15, Estado 2, Línea 36
Must declare the scalar variable "@CharID".
Mens. 137, Nivel 15, Estado 2, Línea 37
Must declare the scalar variable "@CharNickName".
Mens. 137, Nivel 15, Estado 2, Línea 41
Must declare the scalar variable "@KillerName".
Mens. 137, Nivel 15, Estado 2, Línea 44
Must declare the scalar variable "@CharID".
Mens. 137, Nivel 15, Estado 2, Línea 46
Must declare the scalar variable "@CharID".

---

I am stuck in that any help will be thankful

Quote:

---

Originally Posted by pandahot

I got his error when I try to modify _AddLogChar SP

I am stuck in that any help will be thankful

---

Ah, I recon you use MSSQL-2005, or didn't u?

Single line declaration and variable setting doesn't work in MSSQL-2005, it works only in MSSQL-2008 and above.

So the solution is you have to change all declarations that appears like this:

Code:

---

DECLARE @variableName VARTYPE = Value

---

into this:

Code:

---

DECLARE @variableName VARTYPE
SET @variableName = Value

---

For example, in the first declaration in that SP (getting killer name), you will find this:

Code:

---

DECLARE @killername VARCHAR(512) = @desc

---

Change that to:

Code:

---

DECLARE @killername VARCHAR(512)
SET @killername = @desc

---

Do the same for all other declarations in that SP and re-run it :)

good luck

can you give me this noticewriter source code?

Quote:

---

Originally Posted by B1QB0SS

can you give me this noticewriter source code?

---

you can't create your own ? b1qb0ss ?