[GUIDE] Auto Create Table for LOGDB

[gozex]

Long time ago i was create tutorial fix LOGDB
but it's not perfect way to fix
However now i'll Show you

Create New Procedure for Create Table TB_ITEM_LOG_+Auto Date

Example :

CREATE PROCEDURE [dbo].[PROCEDURE_NAME]
    AS
    BEGIN
        
    DECLARE [USER=864679]table[/USER] VARCHAR( 20 )
    SET [USER=864679]table[/USER] = 'TB_ITEM_LOG_' + CONVERT( VARCHAR( 8 ), GETDATE(), 112 )
        
    DECLARE [USER=1333359122]SQL[/USER]String NVARCHAR(MAX)
    Set [USER=1333359122]SQL[/USER]String = 'CREATE TABLE '  [USER=864679]table[/USER]+
    '(
    [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]'


EXEC  [USER=1333359122]SQL[/USER]String)
END

Then you should create script Bat for Exec this Procedure

sqlcmd -Q "exec PROC_NAME" -S SERVER_NAME -d DB_NAME -o C:\EXEC_LOG.txt

After All Success, Create Task Scheduler for Bat Script

Press Start Button > Type Task Scheduler
on Right Side Create Task
at General Tab Type Name of Task
go Trigger Tab > New > at Settings Select Daily and Set Time 12:00:00AM > Ok
go Action Tab > New > Select Bat Script > Ok
go Settings Tab > Uncheck Stop the task if it runs longer than 3Days


Good Luck

 

[pokchop1]

Interesting... I assume this can be configured similar to money logs and other logs?

 

[gozex]

hmm maybe,
that table only used in proc
up_itemmoneylog , tp_item_log_extract, tp_item_log_select, tp_item_log_size_select

table with date is used for Recovery Client