| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_monitor_list_montypes | | 31 Aug 14 | Defects Dependencies |
1 2 create procedure sp_monitor_list_montypes 3 as 4 begin 5 declare @retval int 6 , @montype varchar(30) 7 , @static_cfg_ok int 8 , @dynamic_cfg_ok int 9 , @msg varchar(100) 10 11 -- ============================================================= 12 -- First report the monitoring types found enabled either via 13 -- sp_monitor or sp_configure where all the required config 14 -- options and enabled status are ON. 15 -- 16 -- Load the list of monitoring types that we currently support. 17 -- 18 insert #mon_types(mon_type) values ('connection') 19 insert #mon_types(mon_type) values ('deadlock') 20 insert #mon_types(mon_type) values ('event') 21 insert #mon_types(mon_type) values ('procedure') 22 insert #mon_types(mon_type) values ('procstack') 23 insert #mon_types(mon_type) values ('statement') 24 25 -- Work through table of monitoring types, checking to see if the 26 -- required config options (static and dynamic) ones are properly 27 -- configured. If yes, update the 'enabled' status to record this. 28 -- 29 declare moncur cursor for 30 select mon_type from #mon_types 31 32 open moncur 33 34 select @retval = 0 35 while (@retval = 0) 36 begin 37 fetch moncur into @montype 38 if (@@sqlstatus != 0) 39 begin 40 if (@@error != 0) 41 select @retval = 1 42 break 43 end 44 45 -- Validate config options settings using utility sprocs. 46 -- 47 exec @static_cfg_ok = sp_monitor_verify_setup @montype, 0 48 49 exec @dynamic_cfg_ok = sp_monitor_verify_cfgval @montype, 0 50 51 if (@static_cfg_ok = 0) 52 begin 53 if (@dynamic_cfg_ok = 0) 54 begin 55 update #mon_types 56 set enabled = 1 57 where mon_type = @montype 58 end 59 end 60 else 61 begin 62 -- Record in the #temp table which static config 63 -- option was not ON, but is required. 64 -- 65 update #mon_types 66 set missing_cfg_option = @static_cfg_ok 67 where mon_type = @montype 68 end 69 70 -- Check for errors; only 1 row should be updated. 71 if ((@@error != 0) and (@@rowcount != 1)) 72 begin 73 select @retval = 1 74 break 75 end 76 end 77 78 close moncur 79 80 deallocate cursor moncur 81 82 -- exec sp_autoformat #mon_types 83 84 -- Record the name of the config option that needs to be 85 -- set statically, and its type. 86 -- 87 update #mon_types 88 set config_name = co.name 89 , type = cu.type 90 , run_value = cu.value 91 , cfg_value = co.value 92 , numcfgs_enabled_via_spmon = (select count(*) 93 from tempdb.dbo.mon_config mc 94 where mc.monitor = mt.mon_type) 95 96 from #mon_types mt 97 , master.dbo.sysconfigures co 98 , master.dbo.syscurconfigs cu 99 where mt.missing_cfg_option = co.config 100 and mt.missing_cfg_option = cu.config 101 and cu.config = cu.config 102 103 -- exec sp_autoformat #mon_types 104 105 -- Report the monitoring types that are all setup and ready to go 106 -- for monitoring, if any were so found, and if there were no errors 107 -- in previous logic. 108 -- 109 if ((@retval = 0) 110 and ((select count(*) from #mon_types 111 where enabled = 1 112 and missing_cfg_option = 0) != 0)) 113 begin 114 exec sp_getmessage 19634, @msg output 115 print @msg 116 print "" 117 118 -- For improved readability. 119 update #mon_types 120 set mon_type = mon_type + " monitoring" 121 where enabled = 1 122 123 exec sp_autoformat @fulltabname = #mon_types 124 , @selectlist = "mon_type" 125 , @whereclause = "where enabled = 1" 126 , @orderby = "order by mon_type" 127 end 128 129 return @retval 130 end 131
exec sp_procxmode 'sp_monitor_list_montypes', 'AnyMode' go Grant Execute on sp_monitor_list_montypes to public go
