Database | Proc | Application | Created | Links |
sybsystemprocs | sp_forceonline_page ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */ 3 4 5 /* 6 ** 17260, "Can't run %1! from within a transaction." 7 ** 17421, "No such database -- run sp_helpdb to list databases." 8 ** 17422, "The 'master' database's options can not be changed." 9 ** 17428, "You must be in the 'master' database in order to change 10 ** database options." 11 ** 18438, "%1!: All pages/objects in database '%2!' are now online." 12 ** 18439, "%1!: Failed because the suspect granularity for the database '%2!' is not 13 ** at page level " 14 ** 18440, "%1!: Invalid option '%2!'. Use 'sa_on', 'sa_off', or 'all_users'." 15 ** 18441, "%1!: The page '%2!' in '%3!' is either already online or is an invalid page. 16 ** Use sp_listsuspect_page to list suspect pages." 17 ** 18442, "%1!: Failed to update cached info for page '%2!' of database '%3!'." 18 ** 18443, "%1!: The page '%2!' of database '%3!' is now in state '%4!'." 19 ** 18444, "%1!: The page '%2!' of database '%3!' is already in state '%4!'. 20 ** 18445, "%1!: The remaining suspect pages in database '%2!' are:" 21 */ 22 create procedure sp_forceonline_page 23 @dbname varchar(30), 24 @pgid int, 25 @option varchar(9), 26 @printopt varchar(8) = NULL 27 as 28 declare @dbid int, /* dbid of the database */ 29 @class int, 30 @attrib_id int, 31 @action int, 32 @object_type varchar(2), 33 @msg varchar(1024), 34 @sptlang int, 35 @state varchar(9), 36 @old_state varchar(9), 37 @susgran int, 38 @num int, 39 @whichone int /* which language? */ 40 41 select @class = 10 /* for suspect granularity */ 42 select @action = 2 /* modify entry */ 43 select @attrib_id = 2 /* attribute is SUSPECT PAGES */ 44 select @object_type = 'D' 45 46 47 select @sptlang = @@langid, @whichone = 0 48 49 if @@langid != 0 50 begin 51 if not exists ( 52 select * from master.dbo.sysmessages where error 53 between 17050 and 17069 54 and langid = @@langid) 55 select @sptlang = 0 56 end 57 58 /* 59 ** Verify the database name and get the @dbid 60 */ 61 select @dbid = db_id(@dbname) 62 63 /* 64 ** If @dbname not found, say so. 65 */ 66 if @dbid is NULL 67 begin 68 /* 69 ** 17421, "No such database -- run sp_helpdb to list databases." 70 */ 71 raiserror 17421 72 return (1) 73 end 74 75 /* 76 ** Only the Accounts with SA role can execute it. 77 ** Call proc_role() with the required SA role. 78 */ 79 if (proc_role("sa_role") < 1) 80 begin 81 /* 82 **18524 , "%1!:Permission denied. This operation requires System Administrator (sa_role) role." 83 */ 84 raiserror 18524, "sp_forceonline_page" 85 return (1) 86 end 87 88 89 if db_name() != "master" 90 begin 91 /* 92 ** 17428, "You must be in the 'master' database in order to change database options." 93 */ 94 exec sp_getmessage 17428, @msg output 95 print "sp_forceonline_page: %1!", @msg 96 return (1) 97 end 98 99 /* 100 ** If we're in a transaction, disallow this since it might make recovery 101 ** impossible. 102 */ 103 if @@trancount > 0 104 begin 105 /* 106 ** 17260, "Can't run %1! from within a transaction." 107 */ 108 raiserror 17260, "sp_forceonline_page" 109 return (1) 110 end 111 else 112 begin 113 set chained off 114 end 115 116 set transaction isolation level 1 117 118 /* 119 ** Check to see that the input params are correct and then hook up with 120 ** Sysattributes table to enter data. 121 */ 122 123 124 select @susgran = int_value from master.dbo.sysattributes 125 where class = @class AND 126 attribute = 0 AND 127 object_type = @object_type AND 128 object = @dbid 129 130 if (@susgran != 2) 131 begin 132 /* 133 ** 18439, "%1!: Failed because the suspect granularity for the database '%2!' is not 134 ** page level " 135 */ 136 raiserror 18439, "sp_forceonline_page", @dbname 137 return (1) 138 end 139 140 if ((@option is NULL) OR ((@option != "sa_on") AND (@option != "sa_off") 141 AND (@option != "all_users"))) 142 begin 143 /* 144 ** 18440,"%1!: Invalid option '%2!'. Use 'sa_on', 'sa_off', or 'all_users'." 145 */ 146 raiserror 18440, "sp_forceonline_page", @option 147 return (1) 148 end 149 150 /* check if the requested page is suspect page */ 151 if not exists (select * from master.dbo.sysattributes 152 where class = @class AND 153 attribute = @attrib_id AND 154 object_type = @object_type AND 155 object = @dbid AND 156 int_value = @pgid) 157 begin 158 /* 159 ** 18441, "%1!: The page '%2!' in '%3!' is either already online or is an invalid page. 160 ** Use sp_listsuspect_page to list suspect pages." 161 */ 162 raiserror 18441, "sp_forceonline_page", @pgid, @dbname 163 return (1) 164 end 165 166 /* get the current status of the page */ 167 select @old_state = substring(char_value, 1, 9) from master.dbo.sysattributes 168 where class = @class AND 169 attribute = @attrib_id AND 170 object_type = @object_type AND 171 object = @dbid AND 172 int_value = @pgid 173 174 /* setup the local variables */ 175 if (@option = "all_users") 176 begin 177 /* this is to drop the item */ 178 select @action = 3 179 select @state = "ALL_USERS" 180 end 181 else 182 begin 183 select @action = 2 184 if (@option = "sa_on") 185 begin 186 select @state = "SA_ONLY" 187 end 188 else 189 begin 190 select @state = "BLOCK_ALL" 191 end 192 end 193 /* 194 ** first update the cached information and update the master..sysattributes 195 ** only if the update of cached info is successful 196 */ 197 198 if (@state != @old_state) 199 begin 200 if (attrib_notify(@class, @attrib_id, @object_type, @dbid, NULL, NULL, 201 @pgid, NULL, NULL, @state, NULL, NULL, NULL, @action) = 1) 202 begin 203 /* 204 ** 18442,"%1!: Failed to update cached info for page '%2!' of database '%3!'." 205 */ 206 raiserror 18442, "sp_forceonline_page", @pgid, @dbname 207 return (1) 208 end 209 end 210 211 /* Now update the master..sysattributes */ 212 if (@option = "all_users") 213 begin 214 /* this is to drop the item */ 215 delete master.dbo.sysattributes 216 where class = @class AND 217 attribute = @attrib_id AND 218 object_type = @object_type AND 219 object = @dbid AND 220 int_value = @pgid 221 end 222 else 223 begin 224 if (@state != @old_state) 225 update master.dbo.sysattributes 226 set char_value = @state 227 where class = @class AND 228 attribute = @attrib_id AND 229 object_type = @object_type AND 230 object = @dbid AND 231 int_value = @pgid 232 end 233 234 /* Just say what happened */ 235 if (@state != @old_state) 236 begin 237 /* 238 ** 18443, "%1!: The page '%2!' of database '%3!' is now in state '%4!'." 239 */ 240 exec sp_getmessage 18443, @msg output 241 print @msg, "sp_forceonline_page", @pgid, @dbname, @state 242 end 243 else 244 begin 245 /* 246 ** 18444, "%1!: The page '%2!' of database '%3!' is already in state '%4!'. 247 */ 248 exec sp_getmessage 18444, @msg output 249 print @msg, "sp_forceonline_page", @pgid, @dbname, @state 250 return (1) 251 end 252 253 /* get the number of remaining suspect pages */ 254 select @num = count(*) 255 from master.dbo.sysattributes 256 where class = @class AND 257 attribute = @attrib_id AND 258 object_type = @object_type AND 259 object = @dbid 260 261 /* if all pages are online, say so */ 262 if (@num = 0) 263 begin 264 /* 265 ** 18438, "%1!: All pages/objects in database '%2!' are now online." 266 */ 267 exec sp_getmessage 18438, @msg output 268 print @msg, "sp_forceonline_page", @dbname 269 return (0) 270 end 271 272 if (@printopt = "no_print") 273 return (0) 274 275 /* 276 ** if dropping suspect page then list out the remaining suspect 277 ** pages in the db, otherwise print the status of the page. 278 */ 279 if @action = 3 280 begin 281 /* 282 ** 18445, "%1!: The remaining suspect pages in database '%2!' are:" 283 */ 284 exec sp_getmessage 18445, @msg output 285 print @msg, "sp_forceonline_page", @dbname 286 select "DBName" = @dbname, "Pageid" = convert(varchar(12), int_value), 287 "status" = substring(char_value, 1, 9) 288 from master.dbo.sysattributes 289 where class = @class AND 290 attribute = @attrib_id AND 291 object_type = @object_type AND 292 object = @dbid 293 end 294 else 295 begin 296 select "DBName" = @dbname, "Pageid" = convert(varchar(12), int_value), 297 "status" = substring(char_value, 1, 9) 298 from master.dbo.sysattributes 299 where class = @class AND 300 attribute = @attrib_id AND 301 object_type = @object_type AND 302 object = @dbid AND 303 int_value = @pgid 304 end 305 return (0) 306
exec sp_procxmode 'sp_forceonline_page', 'AnyMode' go Grant Execute on sp_forceonline_page to public go
RESULT SETS | |
sp_forceonline_page_rset_002 | |
sp_forceonline_page_rset_001 |
DEFECTS | |
![]() | master..sysattributes |
![]() | master..sysmessages |
![]() | 54 |
![]() | 54 |
![]() | 125 |
![]() | 125 |
![]() | 126 |
![]() | 152 |
![]() | 152 |
![]() | 153 |
![]() | 153 |
![]() | 168 |
![]() | 168 |
![]() | 169 |
![]() | 169 |
![]() | 216 |
![]() | 216 |
![]() | 217 |
![]() | 217 |
![]() | 227 |
![]() | 227 |
![]() | 228 |
![]() | 228 |
![]() | 256 |
![]() | 256 |
![]() | 257 |
![]() | 257 |
![]() | 289 |
![]() | 289 |
![]() | 290 |
![]() | 290 |
![]() | 299 |
![]() | 299 |
![]() | 300 |
![]() | 300 |
![]() | |
![]() | |
![]() | |
![]() | 94 |
![]() | 215 |
![]() | 225 |
![]() | 240 |
![]() | 248 |
![]() | 267 |
![]() | 284 |
![]() | 72 |
![]() | 79 |
![]() | 85 |
![]() | 96 |
![]() | 109 |
![]() | 130 |
![]() | 137 |
![]() | 140 |
![]() | 147 |
![]() | 163 |
![]() | 175 |
![]() | 184 |
![]() | 198 |
![]() | 200 |
![]() | 207 |
![]() | 212 |
![]() | 224 |
![]() | 235 |
![]() | 250 |
![]() | 262 |
![]() | 269 |
![]() | 272 |
![]() | 273 |
![]() | 305 |
![]() | 225 |
![]() | 124 |
![]() | 167 |
![]() | 286 |
![]() | 296 |
![]() | 116 |
![]() (error, dlevel, langid) Intersection: {error, langid} | 52 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 125 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 152 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 168 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 216 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 227 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 256 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 289 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} | 299 |
![]() | 47 |
![]() | 55 |
![]() | 286 |
![]() | 296 |
![]() | 51 |
![]() | 151 |
![]() | 22 |
![]() | 22 |
![]() | 22 |
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) ![]() reads table master..sysmessages (1) ![]() CALLERS called by proc sybsystemprocs..sp_forceonline_db ![]() |