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

[isozy]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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

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.

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.

 

[Witchy Moo]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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

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

 

[isozy]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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.

 

[Witchy Moo]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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

Sure

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:

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

/edit

Right there.

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:

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

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.

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

 

[isozy]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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

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.

 

[Witchy Moo]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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

 

[ahmetagcan]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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

AddLogChar

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

 

[Witchy Moo]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

I didnt work pls help me

First:
change this:

declare [USER=1333463735]Len[/USER]_pos 	int
	declare [USER=1333463735]Len[/USER]_desc	int
	set [USER=1333463735]Len[/USER]_pos = len(@strPos)
	set [USER=1333463735]Len[/USER]_desc = len(@Desc)
	if  [USER=1333463735]Len[/USER]_pos > 0 and [USER=1333463735]Len[/USER]_desc > 0)
	begin	
		return -1
	end
	else if  [USER=1333463735]Len[/USER]_pos > 0 and [USER=1333463735]Len[/USER]_desc = 0)
	begin	
		return -2
	end
	else if  [USER=1333463735]Len[/USER]_pos = 0 and [USER=1333463735]Len[/USER]_desc > 0)
	begin	
		return -3
	end
	else
	begin	
		return -4
	end

to this:

IF (@EventID != 20)
BEGIN
declare [USER=1333463735]Len[/USER]_pos 	int
	declare [USER=1333463735]Len[/USER]_desc	int
	set [USER=1333463735]Len[/USER]_pos = len(@strPos)
	set [USER=1333463735]Len[/USER]_desc = len(@Desc)
	if  [USER=1333463735]Len[/USER]_pos > 0 and [USER=1333463735]Len[/USER]_desc > 0)
	begin	
		return -1
	end
	else if  [USER=1333463735]Len[/USER]_pos > 0 and [USER=1333463735]Len[/USER]_desc = 0)
	begin	
		return -2
	end
	else if  [USER=1333463735]Len[/USER]_pos = 0 and [USER=1333463735]Len[/USER]_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

 

[ahmetagcan]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

First:
change this:

declare [USER=1333463735]Len[/USER]_pos 	int
	declare [USER=1333463735]Len[/USER]_desc	int
	set [USER=1333463735]Len[/USER]_pos = len(@strPos)
	set [USER=1333463735]Len[/USER]_desc = len(@Desc)
	if  [USER=1333463735]Len[/USER]_pos > 0 and [USER=1333463735]Len[/USER]_desc > 0)
	begin	
		return -1
	end
	else if  [USER=1333463735]Len[/USER]_pos > 0 and [USER=1333463735]Len[/USER]_desc = 0)
	begin	
		return -2
	end
	else if  [USER=1333463735]Len[/USER]_pos = 0 and [USER=1333463735]Len[/USER]_desc > 0)
	begin	
		return -3
	end
	else
	begin	
		return -4
	end

to this:

IF (@EventID != 20)
BEGIN
declare [USER=1333463735]Len[/USER]_pos 	int
	declare [USER=1333463735]Len[/USER]_desc	int
	set [USER=1333463735]Len[/USER]_pos = len(@strPos)
	set [USER=1333463735]Len[/USER]_desc = len(@Desc)
	if  [USER=1333463735]Len[/USER]_pos > 0 and [USER=1333463735]Len[/USER]_desc > 0)
	begin	
		return -1
	end
	else if  [USER=1333463735]Len[/USER]_pos > 0 and [USER=1333463735]Len[/USER]_desc = 0)
	begin	
		return -2
	end
	else if  [USER=1333463735]Len[/USER]_pos = 0 and [USER=1333463735]Len[/USER]_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

 

[Witchy Moo]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

I changed new error

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.

 

[turkplaya]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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

 

[pandahot]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

I got his error when I try to modify _AddLogChar SP

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

 

[Witchy Moo]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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:

DECLARE [USER=1333445324]vari[/USER]ableName VARTYPE = Value

into this:

DECLARE [USER=1333445324]vari[/USER]ableName VARTYPE
SET [USER=1333445324]vari[/USER]ableName = Value

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

DECLARE [USER=434304]killername[/USER] VARCHAR(512) = [USER=275999]des[/USER]c

Change that to:

DECLARE [USER=434304]killername[/USER] VARCHAR(512)
SET [USER=434304]killername[/USER] = [USER=275999]des[/USER]c

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

good luck

 

[B1QB0SS]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

can you give me this noticewriter source code?

 

[Crue]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

can you give me this noticewriter source code?

you can't create your own ? b1qb0ss ?

 

[Witchy Moo]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

can you give me this noticewriter source code?

I cannot, because it's already in the FIRST PAGE of this topic.... duh? :blink:

 

[mylove0990]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

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

 

[mylove0990]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

All OKE But when many people PVP , Windown noitece GLOBAL is full
I Want edit to this notifice show in a newwindow
Can u Fix it

 

[BadFist]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

well..is eazy and same time hard...

 

[Witchy Moo]

Re: "Simplified" [Job/FreePVP Kill] Fetching (vSRO 1.88) - Now works with NoticeWrite

All OKE But when many people PVP , Windown noitece GLOBAL is full
I Want edit to this notifice show in a newwindow
Can u Fix it

No I cannot, I'm not the one who created PlusNotice, I simply modified it to work with my Stored Procedure, if you have questions related to PlusNotice features (such as changing notice to system window and so on so forth), you can post in original thread of PlusNotice, right here.