DatabaseProcApplicationCreatedLinks
sybsystemprocssp_monitor_list_montypes  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace 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   

DEFECTS
 PERR 6 Parsing Error Could not find definition for table tempdb..mon_config 93
 MEST 4 Empty String will be replaced by Single Space 116
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 MTYP 4 Assignment type mismatch @montype: varchar(10) = varchar(30) 49
 MULT 4 Using literal database 'tempdb' tempdb..mon_config 93
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: @fulltabname 123
 QCSC 4 Costly 'select count()', use 'exists()' 110
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 CUSU 3 Cursor updated through 'searched update': risk of halloween rows moncur 55
 CUSU 3 Cursor updated through 'searched update': risk of halloween rows moncur 65
 CUUP 3 Cursor updated: cursor should contain 'for update' clause moncur 55
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public master..syscurconfigs  
 MNER 3 No Error Check should check @@error after insert 18
 MNER 3 No Error Check should check @@error after insert 19
 MNER 3 No Error Check should check @@error after insert 20
 MNER 3 No Error Check should check @@error after insert 21
 MNER 3 No Error Check should check @@error after insert 22
 MNER 3 No Error Check should check @@error after insert 23
 MNER 3 No Error Check should check return value of exec 47
 MNER 3 No Error Check should check @@error after update 55
 MNER 3 No Error Check should check @@error after update 65
 MNER 3 No Error Check should check @@error after update 87
 MNER 3 No Error Check should check return value of exec 114
 MNER 3 No Error Check should check @@error after update 119
 MNER 3 No Error Check should check return value of exec 123
 MUCO 3 Useless Code Useless Begin-End Pair 4
 MUCO 3 Useless Code Useless Brackets 35
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 109
 MZMB 3 Zombie: use of non-existent object tempdb..mon_config 93
 QIWC 3 Insert with not all columns specified missing 7 columns out of 8 18
 QIWC 3 Insert with not all columns specified missing 7 columns out of 8 19
 QIWC 3 Insert with not all columns specified missing 7 columns out of 8 20
 QIWC 3 Insert with not all columns specified missing 7 columns out of 8 21
 QIWC 3 Insert with not all columns specified missing 7 columns out of 8 22
 QIWC 3 Insert with not all columns specified missing 7 columns out of 8 23
 QNAJ 3 Not using ANSI Inner Join 96
 CUPD 2 Updatable Cursor Marker (updatable by default) 30
 MSUB 2 Subquery Marker 110
 MTR1 2 Metrics: Comments Ratio Comments: 30% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 11dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 70 2

DEPENDENCIES
PROCS AND TABLES USED
reads table master..syscurconfigs (1)  
reads table master..sysconfigures (1)  
calls proc sybsystemprocs..sp_monitor_verify_cfgval  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
      reads table master..sysconfigures (1)  
      reads table master..syscurconfigs (1)  
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   reads table master..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_004 
   reads table tempdb..systypes (1)  
   calls proc sybsystemprocs..sp_autoformat  
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   reads table master..systypes (1)  
calls proc sybsystemprocs..sp_getmessage  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
read_writes table tempdb..#mon_types (1) 
calls proc sybsystemprocs..sp_monitor_verify_setup  
   calls proc sybsystemprocs..sp_monitor_getcfgnum  
      reads table master..sysconfigures (1)  
   calls proc sybsystemprocs..sp_monitor_getcfgval  
   reads table master..sysobjects (1)  

CALLERS
called by proc sybsystemprocs..sp_monitor_list