Database | Proc | Application | Created | Links |
sybsystemprocs | sp_setsuspect_granularity ![]() | ![]() | 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 ** 18525, "%1!: Suspect granularity option '%2!' is not valid." 13 ** 18526, "%1!: The new values will become effective during the next recovery of the database." 14 ** 18527, "%1!: Object level suspect granularity is not supported in this release." 15 */ 16 17 create procedure sp_setsuspect_granularity 18 @dbname varchar(255) = NULL, 19 @susgran varchar(8) = NULL, 20 @option varchar(10) = NULL /* valid options - read_only and offline (only to be used during testing) */ 21 as 22 declare @dbid int, /* dbid of the database */ 23 @attrib_id int, 24 @action int, 25 @object_type varchar(2), 26 @msg varchar(1024), 27 @cur_susgran int, 28 @cfg_susgran int, 29 @display_susgran_cur varchar(10), 30 @display_susgran_cfg varchar(10), 31 @susgran_val int, 32 @default_susgran int, 33 @suspectpagebit int, 34 @class int, 35 @sysdbid int, 36 @option_val varchar(10) 37 38 select @action = 1 /* add or modify entry */ 39 select @attrib_id = 0 /* attribute is SUSPECT GRANULARITY */ 40 select @object_type = 'D' 41 select @default_susgran = 0 42 select @cur_susgran = 0 43 select @cfg_susgran = 0 44 select @suspectpagebit = 32 45 select @class = 10 46 47 if @@trancount = 0 48 begin 49 set chained off 50 end 51 52 set transaction isolation level 1 53 54 55 /* 56 ** If no @dbname given, assume the current database 57 */ 58 59 if @dbname is null 60 select @dbname = db_name() 61 62 /* 63 ** Verify the database name and get the @dbid 64 */ 65 select @dbid = db_id(@dbname) 66 67 /* 68 ** If @dbname not found, say so. 69 */ 70 if @dbid is NULL 71 begin 72 /* 73 ** 17421, "No such database -- run sp_helpdb to list databases." 74 */ 75 exec sp_getmessage 17421, @msg output 76 print "sp_setsuspect_granularity: '%1!'", @msg 77 return (1) 78 end 79 80 if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "sybsystemdb") 81 begin 82 /* 83 ** 18523, "'%1!': Not allowed for System databases." 84 */ 85 raiserror 18523, "sp_setsuspect_granularity" 86 return (1) 87 end 88 /* 89 ** If only dbname is provided, then display the setting 90 */ 91 92 if @susgran is NULL 93 goto display_settings 94 95 /* 96 ** Only the Accounts with SA role can execute it. 97 ** Call proc_role() with the required SA role. 98 */ 99 if (proc_role("sa_role") < 1) 100 begin 101 /* 102 ** 18524, "'%1!':Permission denied. This operation requires System Administrator (sa_role) role." 103 */ 104 raiserror 18524, "sp_setsuspect_granularity" 105 return (1) 106 end 107 108 109 if db_name() != "master" 110 begin 111 /* 112 ** 17428, "You must be in the 'master' database in order to change database options." 113 */ 114 exec sp_getmessage 17428, @msg output 115 print "sp_setsuspect_granularity: %1!", @msg 116 return (1) 117 end 118 119 /* 120 ** If we're in a transaction, disallow this since it might make recovery 121 ** impossible. 122 */ 123 if @@trancount > 0 124 begin 125 /* 126 ** 17260, "Can't run %1! from within a transaction." 127 */ 128 raiserror 17260, "sp_setsuspect_granularity" 129 return (1) 130 end 131 else 132 begin 133 set chained off 134 end 135 136 set transaction isolation level 1 137 138 /* 139 ** Check to see that the input params are correct and then hook up with 140 ** Sysattributes table to enter data. 141 */ 142 143 if ((@susgran != "page") AND (@susgran != "object") 144 AND (@susgran != "database")) 145 BEGIN 146 /* 147 ** 18525, %1!: Suspect granularity option '%2!' is not valid. 148 */ 149 raiserror 18525, "sp_setsuspect_granularity", @susgran 150 return (1) 151 END 152 if (@susgran = "object") 153 BEGIN 154 /* 155 ** 18527, %1!: Object level suspect granularity is not supported in 156 ** this release. 157 */ 158 raiserror 18527, "sp_setsuspect_granularity", @susgran 159 return (1) 160 END 161 if (@option != "read_only") AND (@option != "offline") AND (@option != "read/write") AND (@option = "") 162 begin 163 /* 164 ** 18525, %1!: Suspect granularity option '%2!' is not valid. 165 */ 166 raiserror 18525, "sp_setsuspect_granularity", @option 167 return (1) 168 end 169 170 if (@@clustermode = "shared disk cluster" AND @susgran != "database") 171 begin 172 /* 173 ** 18446, %1!: The only valid suspect granularity option for the ASE Cluster Edition is 'database'. 174 */ 175 raiserror 18446, "sp_setsuspect_granularity" 176 return 1 177 end 178 179 180 if @susgran = "page" 181 select @susgran_val = 2 182 if @susgran = "object" 183 select @susgran_val = 1 184 if @susgran = "database" 185 select @susgran_val = 0 186 187 /* 188 ** if an entry already exists for this database then update the entry, 189 ** otherwise insert a new row for this database. 190 ** When updating, change only the config value (object_info2) and not the 191 ** current value (int_value). The config value will take effect only 192 ** during the next boot/load time recovery of the database, during which 193 ** time the current value will be updated to the config value. 194 */ 195 if exists (select * from master.dbo.sysattributes where 196 class = @class AND 197 attribute = @attrib_id AND 198 object_type = @object_type AND 199 object = @dbid) 200 begin 201 /* get the configured suspect granularity value and the option value */ 202 select @cur_susgran = int_value, 203 @cfg_susgran = object_info2, 204 @option_val = substring(char_value, 1, 10) 205 from master.dbo.sysattributes where 206 class = @class AND 207 attribute = @attrib_id AND 208 object_type = @object_type AND 209 object = @dbid 210 211 /* 212 ** if configured granularity is different from new value or the 213 ** configured option is different from new value then the 214 ** entry must be updated. 215 */ 216 if (@cfg_susgran != @susgran_val OR 217 @option_val != @option) 218 begin 219 update master.dbo.sysattributes 220 set object_info2 = @susgran_val, 221 char_value = @option 222 where class = @class AND 223 attribute = @attrib_id AND 224 object_type = @object_type AND 225 object = @dbid 226 end 227 /* 228 ** If the requested suspect granularity is database and the current 229 ** suspect granularity also happens to be database, then this represents 230 ** the default setting for the database that does not require any entry 231 ** in master..sysattributes. So delete the entry. 232 */ 233 if (@susgran = "database" AND @cur_susgran = 0) 234 begin 235 delete master.dbo.sysattributes 236 where class = @class AND 237 attribute = @attrib_id AND 238 object_type = @object_type AND 239 object = @dbid 240 241 select @cfg_susgran = 0 242 end 243 end 244 else 245 begin 246 insert master.dbo.sysattributes 247 (class, attribute, object_type, object, int_value, object_info2, char_value) 248 values (@class, @attrib_id, @object_type, @dbid, 249 @default_susgran, @susgran_val, @option) 250 251 /* This is the first time, so clear the bit in status2 field of sysdatabases */ 252 update master.dbo.sysdatabases 253 set status2 = status2 & @suspectpagebit 254 where dbid = @dbid 255 end 256 257 258 259 /* 260 ** Display the setting 261 */ 262 display_settings: 263 264 select @cur_susgran = int_value, 265 @cfg_susgran = object_info2, 266 @option_val = substring(char_value, 1, 10) 267 from master.dbo.sysattributes 268 where class = @class AND 269 attribute = @attrib_id AND 270 object_type = @object_type AND 271 object = @dbid 272 273 if (@cur_susgran = 2) select @display_susgran_cur = 'page' 274 if (@cur_susgran = 1) select @display_susgran_cur = 'object' 275 if (@cur_susgran = 0) select @display_susgran_cur = 'database' 276 if (@cfg_susgran = 2) select @display_susgran_cfg = 'page' 277 if (@cfg_susgran = 1) select @display_susgran_cfg = 'object' 278 if (@cfg_susgran = 0) select @display_susgran_cfg = 'database' 279 280 if @option_val is NULL 281 select @option_val = "read/write" 282 select "DB Name" = convert(varchar(20), @dbname), 283 "Cur. Suspect Gran." = convert(varchar(10), @display_susgran_cur), 284 "Cfg. Suspect Gran." = convert(varchar(10), @display_susgran_cfg), 285 "Online mode" = @option_val 286 287 if @susgran is NULL 288 return 0 289 290 /* 291 ** 18526, "'%1!': The new values will become effective during the next recovery of the database." 292 */ 293 exec sp_getmessage 18526, @msg output 294 print @msg, "sp_setsuspect_granularity", @dbname 295 return (0) 296
exec sp_procxmode 'sp_setsuspect_granularity', 'AnyMode' go Grant Execute on sp_setsuspect_granularity to public go
RESULT SETS | |
sp_setsuspect_granularity_rset_001 |
DEFECTS | |
![]() | 161 |
![]() | master..sysattributes |
![]() | 248 |
![]() | 248 |
![]() | 196 |
![]() | 196 |
![]() | 197 |
![]() | 197 |
![]() | 206 |
![]() | 206 |
![]() | 207 |
![]() | 207 |
![]() | 222 |
![]() | 222 |
![]() | 223 |
![]() | 223 |
![]() | 236 |
![]() | 236 |
![]() | 237 |
![]() | 237 |
![]() | 254 |
![]() | 254 |
![]() | 268 |
![]() | 268 |
![]() | 269 |
![]() | 269 |
![]() | |
![]() | |
![]() | |
![]() | 75 |
![]() | 114 |
![]() | 219 |
![]() | 235 |
![]() | 246 |
![]() | 252 |
![]() | 293 |
![]() | 77 |
![]() | 86 |
![]() | 99 |
![]() | 105 |
![]() | 116 |
![]() | 129 |
![]() | 143 |
![]() | 150 |
![]() | 152 |
![]() | 159 |
![]() | 167 |
![]() | 170 |
![]() | 216 |
![]() | 233 |
![]() | 273 |
![]() | 274 |
![]() | 275 |
![]() | 276 |
![]() | 277 |
![]() | 278 |
![]() | 295 |
![]() | 252 |
![]() | 220 |
![]() | 202 |
![]() | 264 |
![]() | 52 |
![]() | 136 |
![]() | 247 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 196 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 206 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 222 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 236 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 268 |
![]() | 38 |
![]() | 35 |
![]() | 282 |
![]() | 195 |
![]() | 17 |
![]() | 17 |
![]() | 17 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..sysattributes (1) ![]() 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) ![]() writes table master..sysdatabases (1) ![]() |