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 | |
![]() | master..sysattributes |
![]() | master..sysmessages |
![]() | 219 |
![]() | 219 |
![]() | 57 |
![]() | 57 |
![]() | 106 |
![]() | 106 |
![]() | 107 |
![]() | 107 |
![]() | 178 |
![]() | 178 |
![]() | 179 |
![]() | 203 |
![]() | 203 |
![]() | 204 |
![]() | 204 |
![]() | 210 |
![]() | 210 |
![]() | 211 |
![]() | 211 |
![]() | 233 |
![]() | 233 |
![]() | 234 |
![]() | 234 |
![]() | |
![]() | |
![]() | |
![]() | 82 |
![]() | 136 |
![]() | 208 |
![]() | 217 |
![]() | 241 |
![]() | 84 |
![]() | 93 |
![]() | 114 |
![]() | 121 |
![]() | 127 |
![]() | 138 |
![]() | 151 |
![]() | 165 |
![]() | 171 |
![]() | 184 |
![]() | 190 |
![]() | 243 |
![]() | 217 |
![]() | 209 |
![]() | 103 |
![]() | 175 |
![]() | 111 |
![]() | 48 |
![]() | 158 |
![]() | 218 |
![]() (error, dlevel, langid) Intersection: {error, langid} | 55 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 106 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 178 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 203 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 210 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 233 |
![]() | 50 |
![]() | 58 |
![]() | 23 |
![]() | 33 |
![]() | 111 |
![]() | 229 |
![]() | 54 |
![]() | 202 |
![]() | 17 |
![]() | 17 |
![]() | 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) ![]() |