· 8 years ago · Mar 28, 2018, 10:10 AM
1-- Database : lin2log
2
3USE [lin2log]
4GO
5
6CREATE TABLE [dbo].[log_insert] (
7 [log_file] nvarchar(255) COLLATE Korean_Wansung_CI_AS NOT NULL,
8 [log_table] nvarchar(50) COLLATE Korean_Wansung_CI_AS NOT NULL,
9 [rowsprocessed] int NOT NULL,
10 [log_year] int NOT NULL,
11 [log_month] int NOT NULL,
12 [log_day] int NOT NULL,
13 [log_hour] int NOT NULL,
14 [log_ip] int NOT NULL,
15 [log_svr] nvarchar(20) COLLATE Korean_Wansung_CI_AS NOT NULL,
16 [log_inout] nvarchar(20) COLLATE Korean_Wansung_CI_AS NOT NULL,
17 [process_time] int NULL,
18 [inserted] int NULL
19)
20ON [PRIMARY]
21GO
22
23CREATE PROCEDURE [dbo].[lin_BulkInsert]
24
25AS
26SELECT 0
27GO
28
29CREATE PROCEDURE [dbo].[lin_CheckLogTimeTable2]
30
31AS
32SELECT 0
33GO
34
35CREATE PROCEDURE [dbo].[lin_DropLogTable]
36
37AS
38SELECT 0
39GO
40
41CREATE PROCEDURE [dbo].[lin_GetWorldSnap]
42
43AS
44SELECT 0
45GO
46
47CREATE PROCEDURE [dbo].[lin_MakeChatLogTable]
48
49AS
50SELECT 0
51GO
52
53CREATE PROCEDURE [dbo].[lin_MakeItemLogTable]
54
55AS
56SELECT 0
57GO
58
59CREATE PROCEDURE [dbo].[lin_MakeLogTable]
60
61AS
62SELECT 0
63GO
64
65CREATE PROCEDURE [dbo].[lin_SetInserted]
66
67AS
68SELECT 0
69GO
70
71ALTER PROCEDURE dbo.lin_BulkInsert
72(
73 @log_table varchar(512),
74 @log_file varchar(128)
75)
76AS
77
78set nocount on
79
80declare @sql varchar(1024)
81
82set @sql = ' set nocount on'
83 + ' BULK INSERT ' + @log_table + ' FROM ''' + @log_file + ''' WITH ( MAXERRORS = 65535, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'' ) '
84 + ' select @@ROWCOUNT '
85exec ( @sql )
86GO
87
88/********************************************
89lin_CheckLogTimeTable2
90 do check whether real time log table is exist or not
91INPUT
92 @strDate varchar(16),
93 @nWorld int
94OUTPUT
95
96return
97made by
98 young
99date
100 2002-11-11
101********************************************/
102ALTER PROCEDURE [DBO].[lin_CheckLogTimeTable2]
103(
104 @strDate varchar(16),
105 @nWorld int
106)
107AS
108SET NOCOUNT ON
109
110declare @table_name varchar(60)
111declare @table2_name varchar(60)
112declare @view_name varchar(60)
113declare @sql varchar(2048)
114
115-- check log_realtime
116set @table_name = @strDate + 'log_realtime_' + cast (@nWorld as varchar)
117set @sql = 'select * from sysobjects (nolock) where name = '''+ @table_name + ''''
118
119exec ( @sql)
120if ( @@ROWCOUNT = 0)
121begin
122 set @sql = 'exec lin_MakeLogTable ''' + @table_name + ''''
123 exec (@sql)
124end
125
126-- check log_audit
127set @table_name = @strDate + 'log_audit_' + cast (@nWorld as varchar)
128set @sql = 'select * from sysobjects (nolock) where name = '''+ @table_name + ''''
129exec ( @sql)
130if ( @@ROWCOUNT = 0)
131begin
132 set @sql = 'exec lin_MakeLogTable ''' + @table_name + ''''
133 exec (@sql)
134end
135
136-- check log_data ( store 0~12 hour log)
137set @table_name = @strDate + 'log_data_' + cast (@nWorld as varchar)
138set @sql = 'select * from sysobjects (nolock) where name = '''+ @table_name + ''''
139exec ( @sql)
140if ( @@ROWCOUNT = 0)
141begin
142 set @sql = 'exec lin_MakeLogTable ''' + @table_name + ''''
143 exec (@sql)
144end
145
146-- check log_data2 ( store 12~24 hour log)
147set @table2_name = @strDate + 'log_data2_' + cast (@nWorld as varchar)
148set @sql = 'select * from sysobjects (nolock) where name = '''+ @table2_name + ''''
149exec ( @sql)
150if ( @@ROWCOUNT = 0)
151begin
152 set @sql = 'exec lin_MakeLogTable ''' + @table2_name + ''''
153 exec (@sql)
154end
155
156-- check log_data0 ( view )
157set @view_name = @strDate + 'log_data0_' + cast (@nWorld as varchar)
158set @sql = 'select * from sysobjects (nolock) where name = '''+ @view_name + ''''
159exec ( @sql)
160if ( @@ROWCOUNT = 0)
161begin
162-- set @sql = 'exec lin_MakeLogTable ''' + @table_name + ''''
163 set @sql = 'CREATE VIEW dbo.' + @view_name + ' ( '
164 + ' act_time, log_id, actor, actor_account, target, target_account, location_x, location_y, location_z, '
165 + 'etc_str1, etc_str2, etc_str3, '
166 + 'etc_num1, etc_num2, etc_num3, etc_num4, etc_num5, etc_num6, etc_num7, etc_num8, etc_num9, etc_num10, '
167 + 'STR_actor, STR_actor_account, STR_target, STR_target_account, item_id '
168 + ' ) AS '
169 + ' SELECT act_time, log_id, actor, actor_account, target, target_account, location_x, location_y, location_z, '
170 + ' etc_str1, etc_str2, etc_str3, '
171 + ' etc_num1, etc_num2, etc_num3, etc_num4, etc_num5, etc_num6, etc_num7, etc_num8, etc_num9, etc_num10, '
172 + ' STR_actor, STR_actor_account, STR_target, STR_target_account, item_id '
173 + ' from ' + @table_name + ' (nolock) UNION '
174 + ' SELECT act_time, log_id, actor, actor_account, target, target_account, location_x, location_y, location_z, '
175 + ' etc_str1, etc_str2, etc_str3, '
176 + ' etc_num1, etc_num2, etc_num3, etc_num4, etc_num5, etc_num6, etc_num7, etc_num8, etc_num9, etc_num10, '
177 + ' STR_actor, STR_actor_account, STR_target, STR_target_account, item_id '
178 + ' from ' + @table2_name + ' (nolock) '
179 exec (@sql)
180end
181
182-- check chat
183set @table_name = @strDate + 'log_chat_' + cast (@nWorld as varchar)
184set @sql = 'select * from sysobjects (nolock) where name = '''+ @table_name + ''''
185exec ( @sql)
186if ( @@ROWCOUNT = 0)
187begin
188 set @sql = 'exec lin_MakeChatLogTable ''' + @table_name + ''''
189 exec (@sql)
190end
191GO
192
193ALTER PROCEDURE dbo.lin_DropLogTable
194 @drop_date datetime,
195 @drop_world int
196AS
197
198if @drop_date is null
199begin
200 set @drop_date = getdate()
201 set @drop_date = dateadd(d, -4, getdate())
202end
203
204DECLARE @nyear int
205DECLARE @nmonth int
206DECLARE @nday int
207DECLARE @stryear varchar(10)
208DECLARE @strmonth varchar(10)
209DECLARE @strday varchar(10)
210DECLARE @str_report varchar(32)
211DECLARE @logdate int
212
213set @nyear = datepart(yyyy, @drop_date)
214set @nmonth = datepart(mm, @drop_date)
215set @nday = datepart(dd, @drop_date)
216
217set @stryear = cast(@nyear as varchar)
218if @nmonth < 10
219 set @strmonth = '0' + cast(@nmonth as varchar)
220else
221 set @strmonth = cast (@nmonth as varchar)
222
223if @nday < 10
224 set @strday = '0' + cast(@nday as varchar)
225else
226 set @strday = cast (@nday as varchar)
227
228set @str_report = @stryear + '/' + @strmonth + '/' + @strday
229set @logdate = cast(@stryear + @strmonth + @strday as int)
230
231------------- now.. we have year, month, day string
232DECLARE @table_from varchar(60)
233declare @sql varchar (1024)
234
235
236set @table_from = 'L' + @stryear + '_' + @strmonth + '_' + @strday + '_log_data0_' + cast ( @drop_world as varchar)
237set @sql = ' drop view ' + @table_from
238
239exec (@sql)
240
241
242set @table_from = 'L' + @stryear + '_' + @strmonth + '_' + @strday + '_log_data_' + cast ( @drop_world as varchar)
243set @sql = ' drop table ' + @table_from
244
245exec (@sql)
246
247
248set @table_from = 'L' + @stryear + '_' + @strmonth + '_' + @strday + '_log_data2_' + cast ( @drop_world as varchar)
249set @sql = ' drop table ' + @table_from
250
251exec (@sql)
252
253/*
254set @table_from = 'L' + @stryear + '_' + @strmonth + '_' + @strday + '_log_chat_' + cast ( @drop_world as varchar)
255set @sql = ' drop table ' + @table_from
256
257exec (@sql)
258*/
259GO
260
261/******************************************************************************
262#Name: lin_GetWorldSnap
263#Desc: do make snap shot table of user_data, user_item, pledge, user_nobless
264
265#Argument:
266 Input: @db_server varchar(30) server name
267 @user_id varchar(30) login id
268 @user_pass varchar(30) password
269 @world_id int world id
270 @dtnow varchar(8) yyyyMMdd
271 Output:
272#Return:
273#Result Set:
274
275#Remark:
276#Example: exec lin_GetWorldSnap 'l2db2', 'gamma', '********', 8
277#See:
278
279#History:
280 Create flagoftiger 2004-06-14
281 Modify btwinuni 2005-05-16 add: pledge
282 Modify btwinuni 2005-09-29 add: user_nobless
283 Modify btwinuni 2005-10-27 add parameter: dtnow
284******************************************************************************/
285ALTER PROCEDURE [DBO].[lin_GetWorldSnap]
286 @db_server varchar(30),
287 @user_id varchar(30),
288 @user_pass varchar(30),
289 @world_id int,
290 @dtnow varchar(8) = ''
291AS
292SET NOCOUNT ON
293SET ANSI_WARNINGS ON
294SET ANSI_NULLS ON
295
296declare @tmp_user_item nvarchar(50)
297declare @tmp_user_data nvarchar(50)
298declare @tmp_pledge nvarchar(50)
299declare @tmp_user_nobless nvarchar(50)
300declare @drop_user_item nvarchar(50)
301declare @drop_user_data nvarchar(50)
302declare @drop_pledge nvarchar(50)
303declare @drop_user_nobless nvarchar(50)
304declare @dtnow2 nvarchar(8)
305declare @sql varchar(4000)
306
307if @dtnow = ''
308begin
309 set @dtnow = convert(varchar, getdate(), 112)
310end
311
312set @dtnow2 = convert(varchar, DATEADD(d, -2, cast(@dtnow as datetime)), 112)
313
314-- set table name
315set @tmp_user_item = 'S' + left(@dtnow,4) + '_' + substring(@dtnow,5,2) + '_' + right(@dtnow,2) + '_snap_item_' + cast(@world_id as varchar)
316set @tmp_user_data = 'S' + left(@dtnow,4) + '_' + substring(@dtnow,5,2) + '_' + right(@dtnow,2) + '_snap_data_' + cast(@world_id as varchar)
317set @tmp_pledge = 'S' + left(@dtnow,4) + '_' + substring(@dtnow,5,2) + '_' + right(@dtnow,2) + '_snap_pledge_' + cast(@world_id as varchar)
318set @tmp_user_nobless = 'S' + left(@dtnow,4) + '_' + substring(@dtnow,5,2) + '_' + right(@dtnow,2) + '_snap_nobless_' + cast(@world_id as varchar)
319
320-- set drop table name
321set @drop_user_item = 'S' + left(@dtnow2,4) + '_' + substring(@dtnow2,5,2) + '_' + right(@dtnow2,2) + '_snap_item_' + cast(@world_id as varchar)
322set @drop_user_data = 'S' + left(@dtnow2,4) + '_' + substring(@dtnow2,5,2) + '_' + right(@dtnow2,2) + '_snap_data_' + cast(@world_id as varchar)
323set @drop_pledge = 'S' + left(@dtnow2,4) + '_' + substring(@dtnow2,5,2) + '_' + right(@dtnow2,2) + '_snap_pledge_' + cast(@world_id as varchar)
324set @drop_user_nobless = 'S' + left(@dtnow2,4) + '_' + substring(@dtnow2,5,2) + '_' + right(@dtnow2,2) + '_snap_nobless_' + cast(@world_id as varchar)
325
326
327--------------------------------------------------------------------------------------------------------------------------------------------
328-- user_item snap shot
329--------------------------------------------------------------------------------------------------------------------------------------------
330
331-- check table whether @drop_user_item is exists or not
332set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @drop_user_item + ']'') and objectproperty(id, N''IsUserTable'') = 1)'
333 + ' begin'
334 + ' drop table dbo.' + @drop_user_item
335 + ' end'
336exec (@sql)
337
338-- check table whether @tmp_user_item is exists or not
339set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @tmp_user_item + ']'') and objectproperty(id, N''IsUserTable'') = 1)'
340 + ' begin'
341 + ' drop table dbo.' + @tmp_user_item
342 + ' end'
343exec (@sql)
344
345
346set @sql = ' select * into dbo.' + @tmp_user_item
347 + ' from OPENROWSET ( ''SQLOLEDB'', ''' + @db_server + ''';''' + @user_id + ''';''' + @user_pass + ''', ''select * from lin2world.dbo.tmp_user_item (nolock) where char_id > 0 '') '
348exec (@sql )
349
350set @sql = 'CREATE CLUSTERED INDEX IX_' + @tmp_user_item + '_1 on dbo.' + @tmp_user_item + ' (char_id asc, item_type asc, enchant desc ) with fillfactor = 90 '
351exec (@sql)
352set @sql = 'CREATE NONCLUSTERED INDEX IX_' + @tmp_user_item + '_2 on dbo.' + @tmp_user_item + ' (item_type asc, enchant desc ) with fillfactor = 90 '
353exec (@sql)
354
355
356
357--------------------------------------------------------------------------------------------------------------------------------------------
358-- user_data snap shot
359--------------------------------------------------------------------------------------------------------------------------------------------
360
361-- check table whether @drop_user_data is exists or not
362set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @drop_user_data + ']'') and objectproperty(id, N''IsUserTable'') = 1)'
363 + ' begin'
364 + ' drop table dbo.' + @drop_user_data
365 + ' end'
366exec (@sql)
367
368-- check table whether 'tmp_user_data' is exists or not
369set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @tmp_user_data + ']'') and objectproperty(id, N''IsUserTable'') = 1)'
370 + ' begin'
371 + ' drop table dbo.' + @tmp_user_data
372 + ' end'
373exec (@sql)
374
375
376set @sql = ' select * into dbo.' + @tmp_user_data
377 + ' from OPENROWSET ( ''SQLOLEDB'', ''' + @db_server + ''';''' + @user_id + ''';''' + @user_pass + ''', ''select * from lin2world.dbo.tmp_user_data (nolock)'') '
378exec (@sql )
379
380set @sql = 'CREATE CLUSTERED INDEX IX_' + @tmp_user_data + '_1 on dbo.' + @tmp_user_data + ' (exp desc) with fillfactor = 90 '
381exec (@sql )
382set @sql = 'CREATE NONCLUSTERED INDEX IX_' + @tmp_user_data + '_2 on dbo.' + @tmp_user_data + ' (race asc, exp desc) with fillfactor = 90 '
383exec (@sql )
384set @sql = 'CREATE NONCLUSTERED INDEX IX_' + @tmp_user_data + '_3 on dbo.' + @tmp_user_data + ' (class asc, exp desc) with fillfactor = 90 '
385exec (@sql )
386
387
388
389--------------------------------------------------------------------------------------------------------------------------------------------
390-- pledge snap shot
391--------------------------------------------------------------------------------------------------------------------------------------------
392
393-- check table whether @drop_pledge is exists or not
394set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @drop_pledge + ']'') and objectproperty(id, N''IsUserTable'') = 1)'
395 + ' begin'
396 + ' drop table dbo.' + @drop_pledge
397 + ' end'
398exec (@sql)
399
400-- check table whether '@tmp_pledge' is exists or not
401set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @tmp_pledge + ']'') and objectproperty(id, N''IsUserTable'') = 1)'
402 + ' begin'
403 + ' drop table dbo.' + @tmp_pledge
404 + ' end'
405exec (@sql)
406
407
408set @sql = ' select * into dbo.' + @tmp_pledge
409 + ' from OPENROWSET ( ''SQLOLEDB'', ''' + @db_server + ''';''' + @user_id + ''';''' + @user_pass + ''', ''select * from lin2world.dbo.tmp_pledge (nolock)'') '
410exec (@sql )
411
412set @sql = 'CREATE CLUSTERED INDEX IX_' + @tmp_pledge + '_1 on dbo.' + @tmp_pledge + ' (pledge_id) with fillfactor = 90 '
413exec (@sql )
414
415
416--------------------------------------------------------------------------------------------------------------------------------------------
417-- user_nobless snap shot
418--------------------------------------------------------------------------------------------------------------------------------------------
419
420-- check table whether @drop_user_nobless is exists or not
421set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @drop_user_nobless + ']'') and objectproperty(id, N''IsUserTable'') = 1)'
422 + ' begin'
423 + ' drop table dbo.' + @drop_user_nobless
424 + ' end'
425exec (@sql)
426
427-- check table whether '@tmp_user_nobless' is exists or not
428set @sql = 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @tmp_user_nobless + ']'') and objectproperty(id, N''IsUserTable'') = 1)'
429 + ' begin'
430 + ' drop table dbo.' + @tmp_user_nobless
431 + ' end'
432exec (@sql)
433
434
435set @sql = ' select * into dbo.' + @tmp_user_nobless
436 + ' from OPENROWSET ( ''SQLOLEDB'', ''' + @db_server + ''';''' + @user_id + ''';''' + @user_pass + ''', ''select * from lin2world.dbo.tmp_user_nobless (nolock)'') '
437exec (@sql )
438
439set @sql = 'CREATE CLUSTERED INDEX IX_' + @tmp_user_nobless + '_1 on dbo.' + @tmp_user_nobless + ' (char_id) with fillfactor = 90 '
440exec (@sql )
441GO
442
443/********************************************
444lin_MakeChatLogTable
445 do make whether log table is exist or not
446INPUT
447 @table_name varchar(60)
448OUTPUT
449
450return
451made by
452 young
453date
454 2003-09-19
455********************************************/
456ALTER PROCEDURE [DBO].[lin_MakeChatLogTable]
457(
458 @table_name varchar(60)
459)
460AS
461SET NOCOUNT ON
462
463declare @sql varchar(1024)
464
465set @sql = 'CREATE TABLE dbo.' + @table_name + ' ('
466 + 'act_time datetime NULL ,'
467 + 'log_id smallint NULL ,'
468 + 'actor int NULL , '
469 + 'target int NULL , '
470 + 'location_x int NULL , '
471 + 'location_y int NULL , '
472 + 'location_z int NULL , '
473 + 'say varchar (256) NULL , '
474 + 'STR_actor varchar (32) NULL , '
475 + 'STR_target varchar (32) NULL '
476 + ' )'
477
478exec (@sql)
479
480set @sql = 'CREATE CLUSTERED INDEX IX_' + @table_name + '_ACTOR on dbo.' + @table_name + ' (log_id, actor ) '
481exec (@sql)
482
483set @sql = 'CREATE NONCLUSTERED INDEX IX_' + @table_name + '_TARGET on dbo.' + @table_name + ' ( log_id, target ) '
484exec (@sql)
485GO
486
487/********************************************
488lin_MakeItemLogTable
489 do make whether log table is exist or not
490INPUT
491 @table_name varchar(60)
492OUTPUT
493
494return
495made by
496 young
497date
498 2002-11-11
499********************************************/
500ALTER PROCEDURE [DBO].[lin_MakeItemLogTable]
501(
502 @table_name varchar(60)
503)
504AS
505SET NOCOUNT ON
506
507declare @sql varchar(1024)
508
509set @sql = 'CREATE TABLE dbo.' + @table_name + ' ('
510 + 'act_time datetime NULL ,'
511 + 'log_id smallint NULL ,'
512 + 'item_id int NULL , '
513 + 'item_type int NULL , '
514 + 'actor varchar(32) NULL , '
515 + 'actor_account varchar(32) NULL , '
516 + 'target varchar(32) NULL , '
517 + 'target_account varchar(32) NULL , '
518 + 'location_x int NULL , '
519 + 'location_y int NULL , '
520 + 'location_z int NULL , '
521 + 'etc_num1 int NULL , '
522 + 'etc_num2 int NULL , '
523 + 'etc_num3 int NULL , '
524 + 'etc_num4 int NULL , '
525 + 'etc_num5 int NULL , '
526 + 'etc_num6 int NULL , '
527 + 'etc_num7 int NULL , '
528 + 'etc_num8 int NULL '
529 + ' )'
530
531exec (@sql)
532
533
534set @sql = 'CREATE INDEX IX_' + @table_name + '_1 on dbo.' + @table_name + ' (log_id) '
535exec (@sql)
536
537set @sql = 'CREATE INDEX IX_' + @table_name + '_2 on dbo.' + @table_name + ' (item_id) '
538exec (@sql)
539
540set @sql = 'CREATE INDEX IX_' + @table_name + '_3 on dbo.' + @table_name + ' (item_type) '
541exec (@sql)
542GO
543
544/********************************************
545lin_MakeLogTable
546 do make whether log table is exist or not
547INPUT
548 @table_name varchar(60)
549OUTPUT
550
551return
552made by
553 young
554date
555 2002-11-11
556********************************************/
557ALTER PROCEDURE [DBO].[lin_MakeLogTable]
558(
559 @table_name varchar(60)
560)
561AS
562SET NOCOUNT ON
563
564declare @sql varchar(1024)
565
566set @sql = 'CREATE TABLE dbo.' + @table_name + ' ('
567 + 'act_time datetime NULL ,'
568 + 'log_id smallint NULL ,'
569 + 'actor int NULL , '
570 + 'actor_account int NULL , '
571 + 'target int NULL , '
572 + 'target_account int NULL , '
573 + 'location_x int NULL , '
574 + 'location_y int NULL , '
575 + 'location_z int NULL , '
576 + 'etc_str1 varchar (200) NULL , '
577 + 'etc_str2 varchar (50) NULL , '
578 + 'etc_str3 varchar (50) NULL , '
579 + 'etc_num1 float NULL , '
580 + 'etc_num2 float NULL , '
581 + 'etc_num3 int NULL , '
582 + 'etc_num4 int NULL , '
583 + 'etc_num5 int NULL , '
584 + 'etc_num6 int NULL , '
585 + 'etc_num7 int NULL , '
586 + 'etc_num8 int NULL , '
587 + 'etc_num9 int NULL , '
588 + 'etc_num10 int NULL,'
589 + 'STR_actor varchar (48) NULL , '
590 + 'STR_actor_account varchar (32) NULL , '
591 + 'STR_target varchar (48) NULL , '
592 + 'STR_target_account varchar (32) NULL, '
593 + 'item_id int NULL'
594 + ' )'
595
596exec (@sql)
597
598set @sql = 'CREATE CLUSTERED INDEX IX_' + @table_name + '_ACTOR on dbo.' + @table_name + ' ( log_id, actor ) WITH FILLFACTOR = 90 ON [PRIMARY] '
599exec (@sql)
600
601set @sql = 'CREATE NONCLUSTERED INDEX IX_' + @table_name + '_ITEMTYPE on dbo.' + @table_name + ' ( actor , log_id ) WITH FILLFACTOR = 90 ON [PRIMARY] '
602exec (@sql)
603
604set @sql = 'CREATE NONCLUSTERED INDEX IX_' + @table_name + '_ITEMID on dbo.' + @table_name + ' ( item_id ) WITH FILLFACTOR = 90 ON [PRIMARY] '
605exec (@sql)
606GO
607
608/********************************************
609lin_SetInserted
610 insert or update log file as inserted
611INPUT
612 @log_file nvarchar(255),
613 @log_table nvarchar(50),
614 @log_year int,
615 @log_month int,
616 @log_day int,
617 @log_hour int,
618 @log_ip int,
619 @log_svr nvarchar(20),
620 @log_inout nvarchar(20),
621 @rowsprocessed int
622OUTPUT
623
624return
625made by
626 young
627date
628 2002-10-14
629********************************************/
630ALTER PROCEDURE [DBO].[lin_SetInserted]
631(
632 @log_file nvarchar(255),
633 @log_table nvarchar(50),
634 @log_year int,
635 @log_month int,
636 @log_day int,
637 @log_hour int,
638 @log_ip int,
639 @log_svr nvarchar(20),
640 @log_inout nvarchar(20),
641 @rowsprocessed int,
642 @process_time int = 0
643)
644AS
645SET NOCOUNT ON
646
647insert into log_insert( log_file, log_table, rowsprocessed, log_year, log_month, log_day, log_hour, log_ip, log_svr, log_inout, inserted, process_time )
648values
649(@log_file, @log_table, @rowsprocessed, @log_year, @log_month, @log_day, @log_hour, @log_ip, @log_svr, @log_inout, 1, @process_time )
650GO