DatabaseProcApplicationCreatedLinks
sybsystemprocssp_forceonline_page  31 Aug 14Defects 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
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 54
 QTYP 4 Comparison type mismatch smallint = int 54
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 125
 QTYP 4 Comparison type mismatch smallint = int 125
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 126
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 152
 QTYP 4 Comparison type mismatch smallint = int 152
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 153
 QTYP 4 Comparison type mismatch smallint = int 153
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 168
 QTYP 4 Comparison type mismatch smallint = int 168
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 169
 QTYP 4 Comparison type mismatch smallint = int 169
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 216
 QTYP 4 Comparison type mismatch smallint = int 216
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 217
 QTYP 4 Comparison type mismatch smallint = int 217
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 227
 QTYP 4 Comparison type mismatch smallint = int 227
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 228
 QTYP 4 Comparison type mismatch smallint = int 228
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 256
 QTYP 4 Comparison type mismatch smallint = int 256
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 257
 QTYP 4 Comparison type mismatch smallint = int 257
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 289
 QTYP 4 Comparison type mismatch smallint = int 289
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 290
 QTYP 4 Comparison type mismatch smallint = int 290
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 299
 QTYP 4 Comparison type mismatch smallint = int 299
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 300
 QTYP 4 Comparison type mismatch smallint = int 300
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_forceonline_page  
 MNER 3 No Error Check should check return value of exec 94
 MNER 3 No Error Check should check @@error after delete 215
 MNER 3 No Error Check should check @@error after update 225
 MNER 3 No Error Check should check return value of exec 240
 MNER 3 No Error Check should check return value of exec 248
 MNER 3 No Error Check should check return value of exec 267
 MNER 3 No Error Check should check return value of exec 284
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 140
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 212
 MUCO 3 Useless Code Useless Brackets 224
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 250
 MUCO 3 Useless Code Useless Brackets 262
 MUCO 3 Useless Code Useless Brackets 269
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 305
 MUOT 3 Updates outside transaction 225
 QAFM 3 Var Assignment from potentially many rows 124
 QAFM 3 Var Assignment from potentially many rows 167
 QCRS 3 Conditional Result Set 286
 QCRS 3 Conditional Result Set 296
 QISO 3 Set isolation level 116
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
52
 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}
125
 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}
152
 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}
168
 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}
216
 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}
227
 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}
256
 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}
289
 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}
299
 VNRD 3 Variable is not read @whichone 47
 VNRD 3 Variable is not read @sptlang 55
 MRST 2 Result Set Marker 286
 MRST 2 Result Set Marker 296
 MSUB 2 Subquery Marker 51
 MSUB 2 Subquery Marker 151
 MTR1 2 Metrics: Comments Ratio Comments: 41% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 29 = 38dec - 11exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 141 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