Auto Table Creation For LOGDB

[HaXXoR75]

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

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

 

[monkeyslippery]

Hi, good release but the server crash

 

[HaXXoR75]

Hi, good release but the server crash

Hi, can you provide some more information please? What server crashes? What errors does it give? Have you ensured your database user has the correct permission to be able to access sys.Tables as well as create new tables in the log database? It should also be mentioned that this will only work in sql 2005+, although no one should be using anything bellow 2008 r2 at this point..