Plus Auto Notice(Sample + Source)

Msg 137, Level 15, State 1, Procedure _AddLogItem, Line 74
Must declare the scalar variable "@optlevel".
Msg 137, Level 15, State 2, Procedure _AddLogItem, Line 75
Must declare the scalar variable "@optlevel".

Edit : Please full procedure in txt.

I don't spoon feed you must learn...

declare @optlevel int
 
dont worry about that error, it will be fixed in the newer version im going to release with caosfox soon. basicly its done already just needs some more testing by caosfox then ill release it ^^
 
I have USED THIS QUERY MORE IS The NEWS SHOWS OF + 1 + 2 + 3 + 4 BECAUSE EVERYTHING IS TRUE

if(@strDesc_separado >= 8)


IS it BECAUSE THIS IS The Maximum + 8 AND + 1 APPEARS


Code:
0	_Shytara_ has increased ITEM_CH_BOW_09_C_RARE to [COLOR="#800080"][lvl 3][/COLOR]	2013-06-29 01:16:56.200
0	_Shytara_ has increased ITEM_CH_BOW_09_C_RARE to [COLOR="#800080"][lvl 4][/COLOR]	2013-06-29 01:59:32.107
 
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:
Code:
USE master
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
this querry enables CLR support for the db

second :
Code:
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:
Code:
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:
Code:
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)
Code:
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:
Code:
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:
Code:
    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


program
View attachment notice.rar
source
 
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:
Code:
USE master
GO
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
this querry enables CLR support for the db

second :
Code:
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:
Code:
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:
Code:
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)
Code:
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:
Code:
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:
Code:
    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


program
View attachment 133931
source

Added to first post :) thanks for your share
 
CREATE ASSEMBLY for assembly 'TextFileTips' failed because assembly 'TextFileTips' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. ,,, when i do this CREATE ASSEMBLY TextFileTips
FROM 'C:\MSSQLTips\TextFileTips.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
 
Back