Database | Proc | Application | Created | Links |
sybsystemprocs | sp_monitor_crt_mon_config | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_monitor_crt_mon_config 4 ** 5 ** Utility procedure to [re-]create control table, mon_config in tempdb. 6 ** Caller can optionally ask for table to be dropped and then recreate it. 7 ** If not, and if table already exists, we leave it alone. Else, create 8 ** a new one. 9 ** 10 ** Parameters: 11 ** @do_drop - Boolean; whether to drop table and then create it. 12 ** @callerID - To trace who is calling us. (Debugging hook) 13 ** 14 ** Returns: 15 ** @@error code from drop/create work, if any. 16 { 17 */ 18 create procedure sp_monitor_crt_mon_config( 19 @do_drop int = 0 20 , @callerID varchar(30) = NULL 21 ) 22 as 23 begin 24 declare @retval int 25 26 if (@callerID IS NOT NULL) 27 print "Calling sp_monitor_crt_mon_config from %1!", 28 @callerID 29 if (@do_drop = 1) 30 begin 31 if exists (select 1 from tempdb.dbo.sysobjects 32 where name = 'mon_config' 33 and uid = 1) 34 begin 35 drop table tempdb.dbo.mon_config 36 if (@@error != 0) 37 return @@error 38 end 39 end 40 else if exists (select 1 from tempdb.dbo.sysobjects 41 where name = 'mon_config' 42 and uid = 1) 43 begin 44 return 0 45 end 46 47 select monitor = convert(varchar(30) NULL, "") 48 , confignum = config 49 , configval = value 50 , configname = name 51 , enabled = getdate() 52 , sqlstmt = convert(varchar(1096) NULL, '') 53 , sqlseqno = convert(tinyint NULL, 0) 54 into tempdb.dbo.mon_config lock allpages 55 from master.dbo.sysconfigures 56 where 1 = 0 57 58 select @retval = @@error 59 if (@retval != 0) 60 return @retval 61 62 -- This index will avoid inserting duplicate rows into the control 63 -- table when [e.g.] "sp_monitor enable, procedure" is performed 64 -- repeatedly without an intermediate 'disable' command. 65 -- 66 create unique clustered index mon_config_clustind 67 on tempdb.dbo.mon_config(monitor, confignum) 68 with ignore_dup_key 69 if (@@error != 0) 70 return @@error 71 72 -- Insert into newly created control table a row to track tracing status 73 -- whose status is inititally OFF. 74 -- 75 insert tempdb.dbo.mon_config(monitor, confignum, configval, configname, 76 enabled) 77 values ('tracing', 0, 0, 'sp_monitor trace level', getdate()) 78 79 return @@error 80 81 end -- } 82
exec sp_procxmode 'sp_monitor_crt_mon_config', 'AnyMode' go Grant Execute on sp_monitor_crt_mon_config to public go
DEPENDENCIES |
PROCS AND TABLES USED reads table tempdb..sysobjects (1) reads table master..sysconfigures (1) CALLERS called by proc sybsystemprocs..sp_monitor_enable |