Database | Proc | Application | Created | Links |
sybsystemprocs | sp_setsuspect_threshold | 31 Aug 14 | Defects 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) |