DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setsuspect_threshold  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** 17260, "Can't run %1! from within a transaction." 
6     ** 17421, "No such database -- run sp_helpdb to list databases."
7     ** 17422, "The 'master' database's options can not be changed."
8     ** 17428, "You must be in the 'master' database in order to change
9     **	database options."                                
10    ** 18523, "'%1!': Not allowed for System databases."
11    ** 18524, "'%1!': Permission denied. This operation requires System Administrator (sa_role) role."                                
12    ** 18526, "'%1!': The new values will become effective during the next recovery of the database."
13    ** 18528, sp_setsuspect_threshold: Suspect Threshold value '%1!' is not valid
14    ** 18529, sp_setsuspect_threshold: Not allowed because '%1!' has database level suspect granularity 
15    */
16    
17    create procedure sp_setsuspect_threshold
18        @dbname varchar(255) = NULL,
19        @threshold int = NULL
20    as
21        declare @dbid int, /* dbid of the database */
22            @attrib_id int,
23            @action int,
24            @object_type varchar(2),
25            @msg varchar(1024),
26            @cur_threshold int,
27            @cfg_threshold int,
28            @default_threshold int,
29            @sptlang int,
30            @class int,
31            @whichone int, /* which language? */
32            @cfg_susgran int,
33            @sysdbid int
34    
35        select @attrib_id = 1 /* attribute is SUSPECT THRESHOLD */
36        select @object_type = 'D'
37        select @default_threshold = 20
38        select @cur_threshold = 20
39        select @cfg_threshold = 20
40        select @class = 10
41        select @cfg_susgran = 0
42    
43        if @@trancount = 0
44        begin
45            set chained off
46        end
47    
48        set transaction isolation level 1
49    
50        select @sptlang = @@langid, @whichone = 0
51    
52        if @@langid != 0
53        begin
54            if not exists (
55                    select * from master.dbo.sysmessages where error
56                        between 17050 and 17069
57                        and langid = @@langid)
58                select @sptlang = 0
59        end
60    
61    
62        /*
63        **  If no @dbname given, assume the current database
64        */
65    
66        if @dbname is null
67            select @dbname = db_name()
68    
69        /*
70        **  Verify the database name and get the @dbid 
71        */
72        select @dbid = db_id(@dbname)
73    
74        /*
75        **  If @dbname not found, say so.
76        */
77        if @dbid is NULL
78        begin
79            /*
80            ** 17421, "No such database -- run sp_helpdb to list databases."
81            */
82            exec sp_getmessage 17421, @msg output
83            print "sp_setsuspect_threshold: '%1!'", @msg
84            return (1)
85        end
86    
87        if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "sybsystemdb")
88        begin
89            /*
90            ** 18523, "%1!:Not allowed for System databases."
91            */
92            raiserror 18523, "sp_setsuspect_threshold"
93            return (1)
94        end
95    
96        /*
97        ** If only dbname is provided, then display the setting
98        */
99    
100       if @threshold is NULL
101       begin
102   
103           select @cur_threshold = int_value,
104               @cfg_threshold = object_info2
105           from master.dbo.sysattributes
106           where class = @class AND
107               attribute = @attrib_id AND
108               object_type = @object_type AND
109               object = @dbid
110   
111           select "DB Name" = @dbname,
112               "Cur. Suspect threshold" = @cur_threshold,
113               "Cfg. Suspect threshold" = @cfg_threshold
114           return (0)
115       end
116   
117       /*
118       **  Only the Accounts with SA role can execute it.
119       **  Call proc_role() with the required SA role.
120       */
121       if (proc_role("sa_role") < 1)
122       begin
123           /*
124           ** 18524, "%1!:Permission denied. This operation requires System Administrator (sa_role) role."                                
125           */
126           raiserror 18524, "sp_setsuspect_threshold"
127           return (1)
128       end
129   
130   
131       if db_name() != "master"
132       begin
133           /*
134           ** 17428, "You must be in the 'master' database in order to change database options."                                
135           */
136           exec sp_getmessage 17428, @msg output
137           print "sp_setsuspect_threshold: %1!", @msg
138           return (1)
139       end
140   
141       /*
142       **  If we're in a transaction, disallow this since it might make recovery
143       **  impossible.
144       */
145       if @@trancount > 0
146       begin
147           /*
148           ** 17260, "Can't run %1! from within a transaction." 
149           */
150           raiserror 17260, "sp_setsuspect_threshold"
151           return (1)
152       end
153       else
154       begin
155           set chained off
156       end
157   
158       set transaction isolation level 1
159   
160       /*
161       ** Check to see that the input params are correct and then hook up with
162       ** Sysattributes table to enter data.
163       */
164   
165       if (@threshold < 0)
166       BEGIN
167           /*
168           ** 18528, sp_setsuspect_threshold: Suspect Threshold value '%1!' is not valid
169           */
170           raiserror 18528, @threshold
171           return (1)
172       END
173   
174       /* get the configured suspect granularity value */
175       select
176           @cfg_susgran = object_info2
177       from master.dbo.sysattributes
178       where class = @class AND
179           attribute = 0 AND
180           object_type = @object_type AND
181           object = @dbid
182   
183       /* if configured suspect granularity is database, then return error */
184       if (@cfg_susgran = 0)
185       begin
186           /*
187           ** 18529, sp_setsuspect_threshold: Not allowed because '%1!' has database level suspect granularity 
188           */
189           raiserror 18529, @dbname
190           return (1)
191       end
192   
193   
194       /*
195       ** if an entry already exists for this database then update the entry,
196       ** otherwise insert a new row for this database.
197       ** When updating, change only the config value (object_info2) and not the
198       ** current value (int_value). The config value will take effect only
199       ** during the next boot/load time recovery of the database, during which
200       ** time the current value will be updated to the config value.
201       */
202       IF exists (select * from master.dbo.sysattributes where
203                   class = @class AND
204                   attribute = @attrib_id AND
205                   object_type = @object_type AND
206                   object = @dbid)
207       begin
208           update master.dbo.sysattributes
209           set object_info2 = @threshold
210           where class = @class AND
211               attribute = @attrib_id AND
212               object_type = @object_type AND
213               object = @dbid
214       end
215       else
216       begin
217           insert master.dbo.sysattributes
218           (class, attribute, object_type, object, int_value, object_info2)
219           values (@class, @attrib_id, @object_type, @dbid,
220               @default_threshold, @threshold)
221       end
222   
223   
224   
225       /*
226       ** Display the setting 
227       */
228   
229       select "Database Name" = @dbname,
230           "Cur. Suspect Threshold" = int_value,
231           "Cfg. Suspect Threshold" = object_info2
232       from master.dbo.sysattributes
233       where class = @class AND
234           attribute = @attrib_id AND
235           object_type = @object_type AND
236           object = @dbid
237   
238       /*
239       ** 18526, "'%1!': The new values will become effective during the next recovery of the database %2!."
240       */
241       exec sp_getmessage 18526, @msg output
242       print @msg, "sp_setsuspect_threshold", @dbname
243       return (0)
244   


exec sp_procxmode 'sp_setsuspect_threshold', 'AnyMode'
go

Grant Execute on sp_setsuspect_threshold to public
go
RESULT SETS
sp_setsuspect_threshold_rset_002
sp_setsuspect_threshold_rset_001

DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MTYP 4 Assignment type mismatch attribute: smallint = int 219
 MTYP 4 Assignment type mismatch class: smallint = int 219
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 57
 QTYP 4 Comparison type mismatch smallint = int 57
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 106
 QTYP 4 Comparison type mismatch smallint = int 106
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 107
 QTYP 4 Comparison type mismatch smallint = int 107
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 178
 QTYP 4 Comparison type mismatch smallint = int 178
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 179
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 203
 QTYP 4 Comparison type mismatch smallint = int 203
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 204
 QTYP 4 Comparison type mismatch smallint = int 204
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 210
 QTYP 4 Comparison type mismatch smallint = int 210
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 211
 QTYP 4 Comparison type mismatch smallint = int 211
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 233
 QTYP 4 Comparison type mismatch smallint = int 233
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 234
 QTYP 4 Comparison type mismatch smallint = int 234
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_setsuspect_threshold  
 MNER 3 No Error Check should check return value of exec 82
 MNER 3 No Error Check should check return value of exec 136
 MNER 3 No Error Check should check @@error after update 208
 MNER 3 No Error Check should check @@error after insert 217
 MNER 3 No Error Check should check return value of exec 241
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 190
 MUCO 3 Useless Code Useless Brackets 243
 MUOT 3 Updates outside transaction 217
 MUPK 3 Update column which is part of a PK or unique index object_info2 209
 QAFM 3 Var Assignment from potentially many rows 103
 QAFM 3 Var Assignment from potentially many rows 175
 QCRS 3 Conditional Result Set 111
 QISO 3 Set isolation level 48
 QISO 3 Set isolation level 158
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 218
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
55
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
106
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
178
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
203
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
210
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {object_type, object, attribute, class}
233
 VNRD 3 Variable is not read @whichone 50
 VNRD 3 Variable is not read @sptlang 58
 VUNU 3 Variable is not used @action 23
 VUNU 3 Variable is not used @sysdbid 33
 MRST 2 Result Set Marker 111
 MRST 2 Result Set Marker 229
 MSUB 2 Subquery Marker 54
 MSUB 2 Subquery Marker 202
 MTR1 2 Metrics: Comments Ratio Comments: 43% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 24dec - 9exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 98 17

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
read_writes table master..sysattributes (1)  
reads table master..sysmessages (1)