- Joined
- Oct 27, 2013
- Messages
- 57
- Reaction score
- 10
Hi everyone! Decided to do my first public release, hope y'all like it. This SQL script will modify the existing up_ItemMoneyLog stored procedure to auto create it's table if needed.
Long explanation of this bellow, for all you leachers out there that just want the script, jump to the bottom now
This procedure uses a separate table for each day to prevent the tables becoming too large for the tools to work with, however there is no easy solution for the creation of this daily table.
There is several other solutions to this problem posted on the forums, such as altering it to use a single table (and thus potentially causing memory issues and crashes upon loading due to the tables huge size) or using a scheduled task to execute an SQL script every day at midnight to create a new table for the day, that works however it isn't very clean or contained, and theres the possibility that the scheduled task will launch late and end up missing log entries because of that.
This version uses dynamic SQL to automatically check if the table exists already, and if not, creates it for you, before inserting the log into the new table. This ensures there is never any missed log entries, as the table will always be created if it isn't there before any attempt to save a log entry.
Simply download the attached .SQL script , or copy and paste it from bellow, and execute it on your log database, it will alter the existing stored procedure automagically. Download attached in case the forum mangles the sql somehow ;o
Please note that your luna database account will need permission to create tables in order for this to work.
View attachment LogDB.zip
Long explanation of this bellow, for all you leachers out there that just want the script, jump to the bottom now
This procedure uses a separate table for each day to prevent the tables becoming too large for the tools to work with, however there is no easy solution for the creation of this daily table.
There is several other solutions to this problem posted on the forums, such as altering it to use a single table (and thus potentially causing memory issues and crashes upon loading due to the tables huge size) or using a scheduled task to execute an SQL script every day at midnight to create a new table for the day, that works however it isn't very clean or contained, and theres the possibility that the scheduled task will launch late and end up missing log entries because of that.
This version uses dynamic SQL to automatically check if the table exists already, and if not, creates it for you, before inserting the log into the new table. This ensures there is never any missed log entries, as the table will always be created if it isn't there before any attempt to save a log entry.
Simply download the attached .SQL script , or copy and paste it from bellow, and execute it on your log database, it will alter the existing stored procedure automagically. Download attached in case the forum mangles the sql somehow ;o
Please note that your luna database account will need permission to create tables in order for this to work.
View attachment LogDB.zip
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[up_ItemMoneyLog] (
@[I][B][URL="http://forum.ragezone.com/members/2000078159.html"]From[/URL][/B][/I]CHRIDX INT,
@[I][B][URL="http://forum.ragezone.com/members/2000078159.html"]From[/URL][/B][/I]CHRNAME VARCHAR(16),
@TOCHRIDX INT,
@TOCHRNAME VARCHAR(16),
@[I][B][URL="http://forum.ragezone.com/members/2000163149.html"]log[/URL][/B][/I]TYPE SMALLINT,
@[I][B][URL="http://forum.ragezone.com/members/2000078159.html"]From[/URL][/B][/I]TOTALMONEY BIGINT,
@[I][B][URL="http://forum.ragezone.com/members/2000178083.html"]TOTO[/URL][/B][/I]TALMONEY BIGINT,
@[I][B][URL="http://forum.ragezone.com/members/2000178905.html"]chang[/URL][/B][/I]EMONEY BIGINT,
@ITEMIDX INT,
@ITEMDBIDX INT,
@ITEMFROMPOS INT,
@ITEMTOPOS INT,
@ITEMDUR INT,
@CHREXPPOINT INT
)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @TABLE VARCHAR( 24 )
SET @TABLE = 'TB_ITEM_LOG_' + CONVERT( VARCHAR( 8 ), GETDATE(), 112 )
DECLARE @[I][B][URL="http://forum.ragezone.com/members/1333359122.html"]SQL[/URL][/B][/I]String VARCHAR(MAX)
IF NOT EXISTS(SELECT 1 FROM sys.Tables
WHERE Name = @TABLE AND Type = N'U')
BEGIN
SET @[I][B][URL="http://forum.ragezone.com/members/1333359122.html"]SQL[/URL][/B][/I]String = 'CREATE TABLE [dbo].[' + @table + ']
(
[LOGIDX] [INT] IDENTITY(1,1) NOT NULL,
[LOGTYPE] [SMALLINT] NOT NULL,
[FROMCHRIDX] [INT] NOT NULL,
[FROMCHRNAME] [VARCHAR](16) NOT NULL,
[TOCHRIDX] [INT] NOT NULL,
[TOCHRNAME] [VARCHAR](16) NOT NULL,
[FROMTOTALMONEY] [BIGINT] NOT NULL,
[TOTOTALMONEY] [BIGINT] NULL,
[CHANGEMONEY] [BIGINT] NULL,
[ITEMIDX] [INT] NULL,
[ITEMDBIDX] [INT] NULL,
[ITEMDUR] [BIGINT] NULL,
[ITEMFROMPOS] [INT] NULL,
[ITEMTOPOS] [INT] NULL,
[REGDATE] [DATETIME] NULL,
[CHREXPPOINT] [INT] NOT NULL) ON [PRIMARY]
ALTER TABLE [dbo].[' + @table + '] ADD CONSTRAINT [DF_TB_ITEM_LOG_RegDate ' + @table + '] DEFAULT (getdate()) FOR [RegDate]'
EXEC @[I][B][URL="http://forum.ragezone.com/members/1333359122.html"]SQL[/URL][/B][/I]String)
END
SET @[I][B][URL="http://forum.ragezone.com/members/1333359122.html"]SQL[/URL][/B][/I]String = 'insert ' + @TABLE +
' ( LogType, FromChrName, FromChrIdx, ToChrName, ToChrIdx, FromTotalMoney, ToTotalMoney, ChangeMoney,' +
' ItemIdx, ItemDBIdx, ItemFromPos, ItemToPos, ItemDur, ChrExpPoint ) VALUES (' +
CAST( @[I][B][URL="http://forum.ragezone.com/members/2000163149.html"]log[/URL][/B][/I]Type AS VARCHAR( 20 ) ) + ',' +
'''' + @[I][B][URL="http://forum.ragezone.com/members/2000078159.html"]From[/URL][/B][/I]ChrName + ''',' +
CAST( @[I][B][URL="http://forum.ragezone.com/members/2000078159.html"]From[/URL][/B][/I]ChrIdx AS VARCHAR( 20 ) ) + ',' +
'''' + @ToChrName + ''',' +
CAST( @ToChrIdx AS VARCHAR( 20 ) ) + ',' +
CAST( @[I][B][URL="http://forum.ragezone.com/members/2000078159.html"]From[/URL][/B][/I]TotalMoney AS VARCHAR( 20 ) ) + ',' +
CAST( @[I][B][URL="http://forum.ragezone.com/members/2000178083.html"]TOTO[/URL][/B][/I]talMoney AS VARCHAR( 20 ) ) + ',' +
CAST( @[I][B][URL="http://forum.ragezone.com/members/2000178905.html"]chang[/URL][/B][/I]eMoney AS VARCHAR( 20 ) ) + ',' +
CAST( @ItemIdx AS VARCHAR( 20 ) ) + ',' +
CAST( @ItemDBIdx AS VARCHAR( 20 ) ) + ',' +
CAST( @ItemFromPos AS VARCHAR( 20 ) ) + ',' +
CAST( @ItemToPos AS VARCHAR( 20 ) ) + ',' +
CAST( @ItemDur AS VARCHAR( 20 ) ) + ',' +
CAST( @ChrExpPoint AS VARCHAR( 20 ) ) + ')'
EXEC( @[I][B][URL="http://forum.ragezone.com/members/1333359122.html"]SQL[/URL][/B][/I]String )
RETURN
GO
Attachments
You must be registered for see attachments list
Last edited: