Database	Proc	Application	Created	Links
sybsystemprocs	sp_monitor_verify_setup		31 Aug 14	Defects Dependencies

1     
2     /*
3     ** This stored procedure verifies if everything is kosher to run the monitoring
4     ** stored procedure. If any of the parameters are not satisfied, 
5     ** an error is raised. If a particular monitoring type is being done (e.g.
6     ** 'deadlock') then the appropriate configuration option setting up stuff
7     ** like pipe buffers will be checked, and an error will be reported if the
8     ** right configuration is missing.
9     **
10    ** This routine does double-duty as both a verify-and-fail sproc, as well as
11    ** as a verify-and-return-value sproc. The latter mode is used by the 'list'
12    ** command. If the verification fails, and the caller wants an error, then an
13    ** error is raised, returning 1 for failure. If the caller does not want an
14    ** error, then the config item that is not properly statically configured
15    ** is returned to the caller.
16    **
17    ** Parameters:
18    **	@montype	- Type of entity being monitored whose setup
19    **			  and required cfg options' value is validated.
20    **	@raiserror	- Boolean to control whether to raise errors, or to
21    **			  only execute this code to collect and return config
22    **			  numnber (i.e. caller is in verify mode).
23    **
24    ** Returns:
25    **	0 in case of sucess.
26    **	1 in case of failure, and caller is ok with raising an error.
27    **	Config num, in case of failure, and caller only wants to verify.
28    {
29    */
30    create procedure sp_monitor_verify_setup(
31        @montype varchar(30) = NULL
32        , @raiserror int = 1
33    
34    ) as
35        begin
36            declare @cfg_value int
37                , @cfg_item varchar(100)
38                , @cfg_cmd varchar(100)
39                , @cfg_num int -- matching cfg_item
40                , @procval int
41    
42            if (@raiserror = 1)
43            begin
44                /*
45                ** Indicate that there is a problem if some of the key monitoring tables
46                ** are absent.
47                */
48                if not exists (select 1 from master.dbo.sysobjects
49                        where name IN ('monSysStatement'
50                                , 'monProcessSQLText'
51                                , 'monProcessActivity'
52                                , 'monDeadLock'
53                            ))
54                begin
55                    raiserror 19122, "MDA", "installmaster", "(mon_role)"
56                    return (1)
57                end
58            end
59    
60            -- In the following section, we check for individual config options that
61            -- need a value-setting (not boolean) for the particular type of monitoring
62            -- to be enabled. If the config option is NOT set to some non-zero value,
63            -- raise an error.
64            --
65            if (@montype IN ('connection', 'statement'))
66            begin
67                select @cfg_item = "max SQL text monitored"
68    
69                exec sp_monitor_getcfgval @cfg_item, @cfg_value output, 'static'
70    
71                if (@cfg_value = 0)
72                begin
73                    if (@raiserror = 1)
74                    begin
75                        raiserror 19262, "sp_monitor", @montype,
76                            @cfg_item, "1024", "1024"
77                        return 1
78                    end
79    
80                    exec sp_monitor_getcfgnum @cfg_item, @cfg_num output
81                    return (@cfg_num)
82                end
83            end
84    
85            else if (@montype IN ('procedure'))
86            begin
87                select @cfg_item = "statement pipe max messages"
88    
89                exec sp_monitor_getcfgval @cfg_item, @cfg_value output
90    
91                if (@cfg_value = 0)
92                begin
93                    if (@raiserror = 1)
94                    begin
95                        select @cfg_value = 1024
96                        select @cfg_cmd = "sp_configure '"
97                            + @cfg_item + "', "
98                            + convert(varchar, @cfg_value)
99                        raiserror 19480, "sp_monitor", @montype, @cfg_item
100                           , @cfg_value, @cfg_cmd
101                       return 1
102                   end
103   
104                   exec sp_monitor_getcfgnum @cfg_item, @cfg_num output
105                   return (@cfg_num)
106               end
107           end
108   
109           else if (@montype IN ('deadlock'))
110           begin
111               select @cfg_item = "deadlock pipe max messages"
112   
113               exec sp_monitor_getcfgval @cfg_item, @cfg_value output
114   
115               if (@cfg_value = 0)
116               begin
117                   if (@raiserror = 1)
118                   begin
119                       select @cfg_value = 200
120                       select @cfg_cmd = "sp_configure '"
121                           + @cfg_item + "', "
122                           + convert(varchar, @cfg_value)
123                       raiserror 19480, "sp_monitor", @montype, @cfg_item
124                           , @cfg_value, @cfg_cmd
125                       return 1
126                   end
127   
128                   exec sp_monitor_getcfgnum @cfg_item, @cfg_num output
129                   return (@cfg_num)
130               end
131           end
132   
133           return (0)
134       end -- }
135



exec sp_procxmode 'sp_monitor_verify_setup', 'AnyMode'
go

Grant Execute on sp_monitor_verify_setup to public
go

DEFECTS
MGTP 3 Grant to public master..sysobjects
MGTP 3 Grant to public sybsystemprocs..sp_monitor_verify_setup
MNER 3 No Error Check should check return value of exec	69
MNER 3 No Error Check should check return value of exec	80
MNER 3 No Error Check should check return value of exec	89
MNER 3 No Error Check should check return value of exec	104
MNER 3 No Error Check should check return value of exec	113
MNER 3 No Error Check should check return value of exec	128
MUCO 3 Useless Code Useless Brackets in create proc	30
MUCO 3 Useless Code Useless Begin-End Pair	35
MUCO 3 Useless Code Useless Brackets	42
MUCO 3 Useless Code Useless Brackets	56
MUCO 3 Useless Code Useless Brackets	65
MUCO 3 Useless Code Useless Brackets	71
MUCO 3 Useless Code Useless Brackets	73
MUCO 3 Useless Code Useless Brackets	81
MUCO 3 Useless Code Useless Brackets	85
MUCO 3 Useless Code Useless Brackets	91
MUCO 3 Useless Code Useless Brackets	93
MUCO 3 Useless Code Useless Brackets	105
MUCO 3 Useless Code Useless Brackets	109
MUCO 3 Useless Code Useless Brackets	115
MUCO 3 Useless Code Useless Brackets	117
MUCO 3 Useless Code Useless Brackets	129
MUCO 3 Useless Code Useless Brackets	133
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique (name, uid) Intersection: {name}	49
VUNU 3 Variable is not used @procval	40
MSUB 2 Subquery Marker	48
MTR1 2 Metrics: Comments Ratio Comments: 43%	30
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 11dec - 8exi + 2	30
MTR3 2 Metrics: Query Complexity Complexity: 51	30