Plus Auto Notice(Sample + Source)

[Comandante]

Hey there,

First of all #DirenGezi, #OccupyTurkey

And then

As you can see on the title it just sends a notice to server about alchemy actions When a player successfully upgrades his/her item its automatically recording to Log DB. It will be a bit buggy. I'm not going to make a full guide here but i think you guys can handle it with some brain


Btw i've used "auto notice's source codes by someone else". If you let me know his name i'll let others know who he is Log in with that old way before sending notice

Table

USE [SRO_VT_SHARDLOG]
GO


/****** Object:  Table [dbo].[PlusNotice]    Script Date: 14.06.2013 18:09:33 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


SET ANSI_PADDING ON
GO


CREATE TABLE [dbo].[PlusNotice](
    [Sent] [int] NOT NULL,
    [Message] [varchar](300) NOT NULL,
    [Date] [datetime] NOT NULL
) ON [PRIMARY]


GO


SET ANSI_PADDING OFF
GO


ALTER TABLE [dbo].[PlusNotice] ADD  CONSTRAINT [DF_PlusNotice_Sent]  DEFAULT ((0)) FOR [Sent]
GO


ALTER TABLE [dbo].[PlusNotice] ADD  CONSTRAINT [DF_PlusNotice_Date]  DEFAULT (getdate()) FOR [Date]
GO

To _AddLogItem procedure in log DB(be sure that you've changed "SRO_VT_SHARD" to your own shard db in procedure)

ALTER  procedure [dbo].[_AddLogItem]    @CharID  int,   
  @ItemRefID int,   
  @ItemSerial bigint,   
  @dwData int,   
  @TargetStorage tinyint,   
  @Operation tinyint,   
  @Slot_From tinyint,   
  @Slot_To tinyint,   
  @EventPos varchar(64),   
  @strDesc varchar(128), 
  @Gold        bigint        -- #ifdef EXTEND_GOLD_TYPE »ðÀÔ    
  as   
   declare @len_pos int   
   declare @len_desc int  
   declare @strDesc_separado varchar(128)  
   declare  @len_desc1 int 
   set @len_pos = len(@EventPos)   
   set @len_desc = len(@strDesc)   
   if (@len_pos > 0 and @len_desc > 0)   
   begin   
            insert _LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, EventPos, strDesc, Serial64, Gold)    -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼ 
            values(GetDate(), @CharID, @ItemRefID, @dwData, @TargetStorage, @Operation, @Slot_From, @Slot_To, @EventPos, @strDesc, @ItemSerial, @Gold)        -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼ 
   end   
   else if (@len_pos > 0 and @len_desc = 0)   
   begin   
            insert _LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, EventPos, Serial64, Gold)            -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼ 
            values(GetDate(), @CharID, @ItemRefID, @dwData, @TargetStorage, @Operation, @Slot_From, @Slot_To, @EventPos, @ItemSerial, @Gold)                -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼  
   end   
   else if (@len_pos = 0 and @len_desc > 0)   
   begin   
            insert _LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, strDesc, Serial64, Gold)            -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼ 
            values(GetDate(), @CharID, @ItemRefID, @dwData, @TargetStorage, @Operation, @Slot_From, @Slot_To, @strDesc, @ItemSerial, @Gold)                    -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼  
   end   
   else if (@len_pos = 0 and @len_desc = 0)   
   begin   
            insert _LogEventItem (EventTime, CharID, ItemRefID, dwData, TargetStorage, Operation, Slot_From, Slot_To, Serial64, Gold)                        -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼ 
            values(GetDate(), @CharID, @ItemRefID, @dwData, @TargetStorage, @Operation, @Slot_From, @Slot_To, @ItemSerial, @Gold)                            -- #ifdef EXTEND_GOLD_TYPE ±³Ã¼ 
   end   
   -- À¯·á ¾ÆÀÌÅÛ ±¸ÀÔÀ̸é!   
   -- #define LOG_ITEMEVENT_BUY_CASHITEM  (BYTE)35   
   if (@Operation = 35)   
   begin   
    insert _LogCashItem (RefItemID, CharID, Cnt, EventTime, Serial64)   
    values(@ItemRefID, @CharID, @dwData, GetDate(), @ItemSerial)   
   end 
   if (@Operation = 90 OR @Operation = 160)   
   begin   
   set @len_desc1 = CHARINDEX (']',@strDesc)  
  if(@len_desc1 = 9)   
     begin 
     set @strDesc = SUBSTRING(@strDesc,0,9) 
     set @strDesc_separado = SUBSTRING(@strDesc, 8, 1) 
    end 
   else if(@len_desc1 = 10) 
        begin 
        set @strDesc = SUBSTRING(@strDesc,0,10) 
        set @strDesc_separado = SUBSTRING(@strDesc, 8, 2) 
        end 
        if(@strDesc_separado >= 8) 
        begin 
        --set @strDesc_separado = @len_desc1 
      insert _LogItemPlus(CharID, Serial64, Plus, Data, Lido) values(@CharID, @ItemSerial, @strDesc_separado, GetDate(), '0')   
    end  
   end 
   if(@Operation = 90)
   begin
    declare @iname varchar(129)
    declare @iid int
    declare @cname varchar(64)
    select @iid = RefItemID from SRO_VT_SHARD.dbo._Items where Serial64 = @ItemSerial
    select @iname = CodeName128 from SRO_VT_SHARD.dbo._RefObjCommon where id = @iid
    select @cname = CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID
    insert PlusNotice(Sent, Message)values(0,@cname + ' has increased ' + @iname + ' to [lvl ' + CAST(@strDesc_separado AS NVARCHAR) + ']')
  end

Change log:

v1.0.1:
-Sql connection issue fixed

v1.0.2:
-added remote connection

v1.0.3:
-empty/old notice spam fixed

v1.0.4:
-Packet sending issue fixed

V1.0.5:
-Manuel gateway port selection

And the download links of source and the sample:

Sample:

To view the content, you need to sign in or register

Source:

To view the content, you need to sign in or register

Password:

ragezone

Virustotal:

To view the content, you need to sign in or register

I suggest you guys to read this thread before starting this one: http://forum.ragezone.com/f722/db-log-plus-weapons-globals-934558/

P.S: Found the auto notice base source developer which i created the whole system on it. Credits of it goes to Devsome.com

Hope you enjoy and hope it works for you

V3 under this spoiler​

​

​

Spoiler

Credits goes to

Caosfox ​

and lemoniscool

Don't forget to give thanks to them under this thread like their comments

Hello Ragezoners.
(first of all, my english sucks, so live with it)

Finaly is time for the notice writer v3.

requeriments:
sql 2008 r2 (maybe works with 2005 or 2012, i dont have them so not tested it. dont ask me how to make it work on another version, im not going to install 2005 or 2012)
.net framework 3.5 minimum, but recommned 4.0+
time and patience, young padawan

How it works:
is rater simply, the "plus notice" querry writes a small text file on the hard drive with "xxxx as sucess blahblahlblah to +2544" (you define it on the plus notice querry),
next the notice writer monitors that file (folder in this case) for any new file created (is on real time and uses near no processor power).
When a new file is created, reads it and send the message in game (all of that process needs only 0.5 o 0.6 seconds, so is almost real time).
this method DONT use any table in the db

Now install guide:
1) download all the files in the attach
2) create a folder in the root hard drive of your silkroad server (root must be C and name it MSSQLTips (so is C:\MSSQLTips),
3) copy TextFileTips.dll inside that folder
4) do a full db backup, only in case something goes wrong
5) now at SQL Server Management Studio execute this querrys:
first:

USE master
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

this querry enables CLR support for the db

second :

USE SRO_VT_SHARDLOG
GO
ALTER DATABASE SRO_VT_SHARDLOG SET TRUSTWORTHY ON
GO

needed for CLR compatibility (rename SRO_VT_SHARDLOG to your log db)

third:

CREATE ASSEMBLY TextFileTips
FROM 'C:\MSSQLTips\TextFileTips.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

this querry installs the CLR code

5) execute this querry on your log db to create 2 new functions:

CREATE FUNCTION [dbo].[WriteTextFile](
@text [nvarchar](4000), 
@path [nvarchar](4000), 
@append [bit])
RETURNS [bit] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [TextFileTips].[ReadWriteFileTips].[WriteTextFile]
GO
CREATE PROCEDURE [dbo].[ReadTextFile]
 @path [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TextFileTips].[ReadWriteFileTips].[ReadTextFile]
GO

6) if any of the steps 5 or 6 gives a weird errors like :
error :
Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature.
solution:
execute this querry (use your log db)

EXEC sp_dbcmptlevel 'SRO_VT_SHARDLOG', 100

error:
if sql says you dont hve authorization on db, is a normal error of backups dbs created on one machine and restored on another (remember, all ours dbs are backups from a original one)
to fix this, simply follow this steps:

Select the database, right click it, select PROPERTIES. A pop up window opens up and in the upper left corner you'll see "SELECT A PAGE". select FILE. 
Now in the main section on the top you'll see OWNER. Select the button next to the empty box. another pop up box opens up, select browse and then you'll have to select (NT AUTHORITY\SYSTEM)

fix the error and repeat the steps

7) modify the addlogitem proc, how? like this:
on your proc, at the inserting the message to the table, comment it and add:

    declare @azar INT
    declare @randomfile varchar(50) 
    SELECT top 1 @azar = (ABS(CHECKSUM(NEWID())) % 10000) FROM Master.dbo.Syscolumns
    select @randomfile = 'C:\MSSQLTips\' + convert(varchar, @azar) + '.txt'
    SELECT dbo.WriteTextFile(@veremos, @randomfile, 0)

in this querry @azar generates a random number, @veremos is the var where the "xxxx as sucessfull incresing xxx to lvl xxx" (or w/e your msg is) only that, no need date, time or anything more
@randomfile = creates a text file with the random name, you ask why need to do this? easy, if 2 or more players plus at same time and if you use fixed names,
only 1 message is send, the first. this way all msgs are send (unless you are too unlucky and the random numbers are the same, but you have 1 in 10000 chances of that
and last command writes the text file (dont change paths)

8)done, now simply run the notice.exe and enjoy

virustotal detects 2 "troyan" things, but is the file monitoring routines at folder MSSQLTips

To view the content, you need to sign in or register

program
View attachment 133931
source

To view the content, you need to sign in or register

 

[sladlejrhfpq]

if it really works, will be epic, gonna test, thanks for the release.

 

[Comandante]

Actually its so simple i prefer you guys to edit it a bit especially the timer its sending notice every 10 secs and gets the newest plus message (ordering by date)

 

[denise456]

Good Job

 

[Comandante]

Good Job

Thank you.

And a suggestion: it's getting CodeName128 from _refObjCommon as item's name. As you know it's not the real name of item it's just name string. So i suggest you guys to alter _refObjCommon table and add a column which will contain item's real name and edit procedure to select that column instead of codename128

 

[MaDenGo]

   if(@Operation = 90)   begin
    declare @iname varchar(129)
    declare @iid int
    declare @cname varchar(64)
    select @iid = RefItemID from SRO_VT_SHARD.dbo._Items where Serial64 = @ItemSerial
    select @iname = CodeName128 from SRO_VT_SHARD.dbo._RefObjCommon where id = @iid
    select @cname = CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID
    insert PlusNotice(Sent, Message)values(0,@cname + ' has increased ' + @iname + ' to [lvl ' + CAST(@strDesc_separado AS NVARCHAR) + ']')
    end

When i add those lines on AddtimedJop

Msg 137, Level 15, State 2, Procedure _AddTimedJob, Line 19
Must declare the scalar variable "@Operation".
Msg 137, Level 15, State 2, Procedure _AddTimedJob, Line 23
Must declare the scalar variable "@ItemSerial".
Msg 137, Level 15, State 2, Procedure _AddTimedJob, Line 26
Must declare the scalar variable "@strDesc_separado".

 

[Comandante]

   if(@Operation = 90)   begin
    declare @iname varchar(129)
    declare @iid int
    declare @cname varchar(64)
    select @iid = RefItemID from SRO_VT_SHARD.dbo._Items where Serial64 = @ItemSerial
    select @iname = CodeName128 from SRO_VT_SHARD.dbo._RefObjCommon where id = @iid
    select @cname = CharName16 from SRO_VT_SHARD.dbo._Char where CharID = @CharID
    insert PlusNotice(Sent, Message)values(0,@cname + ' has increased ' + @iname + ' to [lvl ' + CAST(@strDesc_separado AS NVARCHAR) + ']')
    end

When i add those lines on AddtimedJop

Msg 137, Level 15, State 2, Procedure _AddTimedJob, Line 19
Must declare the scalar variable "@Operation".
Msg 137, Level 15, State 2, Procedure _AddTimedJob, Line 23
Must declare the scalar variable "@ItemSerial".
Msg 137, Level 15, State 2, Procedure _AddTimedJob, Line 26
Must declare the scalar variable "@strDesc_separado".

you must add that to _AddLogItem in log DB as you can see on my first post ) take a look to this thread too http://forum.ragezone.com/f722/db-log-plus-weapons-globals-934558/

 

[MaDenGo]

you must add that to _AddLogItem in log DB as you can see on my first post ) take a look to this thread too http://forum.ragezone.com/f722/db-log-plus-weapons-globals-934558/

aha ok my fault
but on this
on Type your Host = server ip or SQL Server name
btw i tried both

what's going on ?

 

[Comandante]

aha ok my fault
but on this View attachment 132323
on Type your Host = server ip or SQL Server name
btw i tried both
View attachment 132324
what's going on ?

Again as you can see there is no username or password field for SQL that means you have to use that in localhost of server and yes SQL servername

+

An issue fixed

 

[DieEyes]

@Comandante did you re-uploaded another one i got same problem ?

Yes you have to download new one.

 

[Comandante]

In all threads which "change log" added i've changed links. Tell me your problem again?

 

[Caosfox]

yup, some small things need a little edition, like the annoying Integrated Security = True replaced for user id and password (to run from any other pc)

 

[DieEyes]

one more bug keep spamming at game.

[Notice]
[Notice]
[Notice]
[Notice]

 

[Comandante]

yup, some small things need a little edition, like the annoying Integrated Security = True replaced for user id and password (to run from any other pc)

Added

one more bug keep spamming at game.

[Notice]
[Notice]
[Notice]
[Notice]

Empty notice or sending one notice for several times?

 

[DieEyes]

Empty!

 

[Comandante]

Empty!

Actually it's normal while PlusNotice table is empty check it out and be sure that its not empty

 

[DieEyes]

Yeah was empty, when i try to plus now something and its success i get error, System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.

Maybe i create rows with null let me check.

 

[Comandante]

Yeah was empty, when i try to plus now something and its success i get error, System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.

Maybe i create rows with null let me check.

Never ever allow nulls. Sent column is used by application to check if its already sent(coz it sends notice every 10 sec so there must be a limit) Message column is the body of application which contains notice and date column is used by application to send messages in an order (newest first)

Important: Default values are important. Take a look to first post. I've added them.

 

[Caosfox]

working perfect, only need to change some small values on proc (varchar got small and miss the last part of the msg)

 

[Comandante]

working perfect, only need to change some small values on proc (varchar got small and miss the last part of the msg)
View attachment 132326

Do not add "+" symbol to that message that's why i call it as lvl instead of opt That symbol is invisible while sending notice