DatabaseProcApplicationCreatedLinks
sybsystemprocssp_forceonline_page  14 déc. 14Defects Propagation 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 or replace 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            @nullarg char(1),
41            @dummy int,
42            @status int,
43            @gp_enabled int,
44            @issystemdb int
45    
46        select @issystemdb = 0
47        select @class = 10 /* for suspect granularity */
48        select @action = 2 /* modify entry */
49        select @attrib_id = 2 /* attribute is SUSPECT PAGES */
50        select @object_type = 'D'
51    
52    
53        select @sptlang = @@langid, @whichone = 0
54    
55        if @@langid != 0
56        begin
57            if not exists (
58                    select * from master.dbo.sysmessages where error
59                        between 17050 and 17069
60                        and langid = @@langid)
61                select @sptlang = 0
62        end
63    
64        /*
65        **  Verify the database name and get the @dbid 
66        */
67        select @dbid = db_id(@dbname)
68    
69        /*
70        **  If @dbname not found, say so.
71        */
72        if @dbid is NULL
73        begin
74            /*
75            ** 17421, "No such database -- run sp_helpdb to list databases."
76            */
77            raiserror 17421
78            return (1)
79        end
80    
81        /* 
82        ** If granular permissions is not enabled then sa_role is required.
83        ** If granular permissions is enabled then the permission 'own database' is
84        ** required. proc_role and proc_auditperm will also do auditing
85        ** if required. Both will also print error message if required.
86        */
87    
88        select @nullarg = NULL
89        execute @status = sp_aux_checkroleperm "sa_role", "own database",
90            @dbname, @gp_enabled output
91        /* For Auditing */
92        if (@gp_enabled = 0)
93            select @dummy = proc_role("sa_role")
94        else
95        begin
96            select @dummy = proc_auditperm("own database", @status, @dbname)
97        end
98    
99        if (@status != 0)
100           return (1)
101   
102       if db_name() != "master"
103       begin
104           /*
105           ** 17428, "You must be in the 'master' database in order to change database options."                                
106           */
107           exec sp_getmessage 17428, @msg output
108           print "sp_forceonline_page: %1!", @msg
109           return (1)
110       end
111   
112       /*
113       **  If we're in a transaction, disallow this since it might make recovery
114       **  impossible.
115       */
116       if @@trancount > 0
117       begin
118           /*
119           ** 17260, "Can't run %1! from within a transaction." 
120           */
121           raiserror 17260, "sp_forceonline_page"
122           return (1)
123       end
124       else
125       begin
126           set chained off
127       end
128   
129       set transaction isolation level 1
130   
131       /*
132       ** Check to see that the input params are correct and then hook up with
133       ** Sysattributes table to enter data.
134       */
135   
136   
137       select @susgran = int_value from master.dbo.sysattributes
138       where class = @class AND
139           attribute = 0 AND
140           object_type = @object_type AND
141           object = @dbid
142   
143       if (@susgran != 2)
144       begin
145           /*
146           ** 18439, "%1!: Failed because the suspect granularity for the database '%2!' is not
147           **           page level "
148           */
149           raiserror 18439, "sp_forceonline_page", @dbname
150           return (1)
151       end
152   
153       if ((@option is NULL) OR ((@option != "sa_on") AND (@option != "sa_off")
154                   AND (@option != "all_users")))
155       begin
156           /*
157           ** 18440,"%1!: Invalid option '%2!'. Use 'sa_on', 'sa_off', or 'all_users'."
158           */
159           raiserror 18440, "sp_forceonline_page", @option
160           return (1)
161       end
162   
163       /* check if the requested page is suspect page */
164       if not exists (select * from master.dbo.sysattributes
165               where class = @class AND
166                   attribute = @attrib_id AND
167                   object_type = @object_type AND
168                   object = @dbid AND
169                   int_value = @pgid)
170       begin
171           /*
172           ** 18441, "%1!: The page '%2!' in '%3!' is either already online or is an invalid page. 
173           ** 			Use sp_listsuspect_page to list suspect pages."
174           */
175           raiserror 18441, "sp_forceonline_page", @pgid, @dbname
176           return (1)
177       end
178   
179       /* get the current status of the page */
180       select @old_state = substring(char_value, 1, 9) from master.dbo.sysattributes
181       where class = @class AND
182           attribute = @attrib_id AND
183           object_type = @object_type AND
184           object = @dbid AND
185           int_value = @pgid
186   
187       /* setup the local variables */
188       if (@option = "all_users")
189       begin
190           /* this is to drop the item */
191           select @action = 3
192           select @state = "ALL_USERS"
193       end
194       else
195       begin
196           select @action = 2
197           if (@option = "sa_on")
198           begin
199               select @state = "SA_ONLY"
200           end
201           else
202           begin
203               select @state = "BLOCK_ALL"
204           end
205       end
206       /* 
207       ** first update the cached information and update the master..sysattributes
208       ** only if the update of cached info is successful
209       */
210   
211       if (@state != @old_state)
212       begin
213           if (attrib_notify(@class, @attrib_id, @object_type, @dbid, NULL, NULL,
214                       @pgid, NULL, NULL, @state, NULL, NULL, NULL, @action) = 1)
215           begin
216               /*
217               ** 18442,"%1!: Failed to update cached info for page '%2!' of database '%3!'."
218               */
219               raiserror 18442, "sp_forceonline_page", @pgid, @dbname
220               return (1)
221           end
222       end
223   
224       /* Now update the master..sysattributes */
225       if (@option = "all_users")
226       begin
227           /* this is to drop the item */
228           delete master.dbo.sysattributes
229           where class = @class AND
230               attribute = @attrib_id AND
231               object_type = @object_type AND
232               object = @dbid AND
233               int_value = @pgid
234       end
235       else
236       begin
237           if (@state != @old_state)
238               update master.dbo.sysattributes
239               set char_value = @state
240               where class = @class AND
241                   attribute = @attrib_id AND
242                   object_type = @object_type AND
243                   object = @dbid AND
244                   int_value = @pgid
245       end
246   
247       /* Just say what happened */
248       if (@state != @old_state)
249       begin
250           /*
251           ** 18443, "%1!: The page '%2!' of database '%3!' is now in state '%4!'."
252           */
253           exec sp_getmessage 18443, @msg output
254           print @msg, "sp_forceonline_page", @pgid, @dbname, @state
255       end
256       else
257       begin
258           /*
259           ** 18444, "%1!: The page '%2!' of database '%3!' is already in state '%4!'.
260           */
261           exec sp_getmessage 18444, @msg output
262           print @msg, "sp_forceonline_page", @pgid, @dbname, @state
263           return (1)
264       end
265   
266       /* get the number of remaining suspect pages */
267       select @num = count(*)
268       from master.dbo.sysattributes
269       where class = @class AND
270           attribute = @attrib_id AND
271           object_type = @object_type AND
272           object = @dbid
273   
274       /* if all pages are online, say so */
275       if (@num = 0)
276       begin
277           /*
278           ** 18438, "%1!: All pages/objects in database '%2!' are now online."
279           */
280           exec sp_getmessage 18438, @msg output
281           print @msg, "sp_forceonline_page", @dbname
282           return (0)
283       end
284   
285       if (@printopt = "no_print")
286           return (0)
287   
288       /* 
289       ** if dropping suspect page then list out the remaining suspect 
290       ** pages in the db, otherwise print the status of the page. 
291       */
292       if @action = 3
293       begin
294           /*
295           ** 18445, "%1!: The remaining suspect pages in database '%2!' are:"
296           */
297           exec sp_getmessage 18445, @msg output
298           print @msg, "sp_forceonline_page", @dbname
299           select "DBName" = @dbname, "Pageid" = convert(varchar(12), int_value),
300               "status" = substring(char_value, 1, 9)
301           from master.dbo.sysattributes
302           where class = @class AND
303               attribute = @attrib_id AND
304               object_type = @object_type AND
305               object = @dbid
306       end
307       else
308       begin
309           select "DBName" = @dbname, "Pageid" = convert(varchar(12), int_value),
310               "status" = substring(char_value, 1, 9)
311           from master.dbo.sysattributes
312           where class = @class AND
313               attribute = @attrib_id AND
314               object_type = @object_type AND
315               object = @dbid AND
316               int_value = @pgid
317       end
318       return (0)
319   


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 60
 QTYP 4 Comparison type mismatch smallint = int 60
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 138
 QTYP 4 Comparison type mismatch smallint = int 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 139
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 165
 QTYP 4 Comparison type mismatch smallint = int 165
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 166
 QTYP 4 Comparison type mismatch smallint = int 166
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 181
 QTYP 4 Comparison type mismatch smallint = int 181
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 182
 QTYP 4 Comparison type mismatch smallint = int 182
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 229
 QTYP 4 Comparison type mismatch smallint = int 229
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 230
 QTYP 4 Comparison type mismatch smallint = int 230
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 240
 QTYP 4 Comparison type mismatch smallint = int 240
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 241
 QTYP 4 Comparison type mismatch smallint = int 241
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 269
 QTYP 4 Comparison type mismatch smallint = int 269
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 270
 QTYP 4 Comparison type mismatch smallint = int 270
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 302
 QTYP 4 Comparison type mismatch smallint = int 302
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 303
 QTYP 4 Comparison type mismatch smallint = int 303
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 312
 QTYP 4 Comparison type mismatch smallint = int 312
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 313
 QTYP 4 Comparison type mismatch smallint = int 313
 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 107
 MNER 3 No Error Check should check @@error after delete 228
 MNER 3 No Error Check should check @@error after update 238
 MNER 3 No Error Check should check return value of exec 253
 MNER 3 No Error Check should check return value of exec 261
 MNER 3 No Error Check should check return value of exec 280
 MNER 3 No Error Check should check return value of exec 297
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 188
 MUCO 3 Useless Code Useless Brackets 197
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 237
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 282
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 286
 MUCO 3 Useless Code Useless Brackets 318
 MUOT 3 Updates outside transaction 238
 QAFM 3 Var Assignment from potentially many rows 137
 QAFM 3 Var Assignment from potentially many rows 180
 QCRS 3 Conditional Result Set 299
 QCRS 3 Conditional Result Set 309
 QISO 3 Set isolation level 129
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
58
 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}
138
 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}
165
 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}
181
 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}
229
 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}
240
 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}
269
 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}
302
 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}
312
 VNRD 3 Variable is not read @issystemdb 46
 VNRD 3 Variable is not read @whichone 53
 VNRD 3 Variable is not read @sptlang 61
 VNRD 3 Variable is not read @nullarg 88
 VNRD 3 Variable is not read @dummy 96
 MRST 2 Result Set Marker 299
 MRST 2 Result Set Marker 309
 MSUB 2 Subquery Marker 57
 MSUB 2 Subquery Marker 164
 MTR1 2 Metrics: Comments Ratio Comments: 39% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 32 = 40dec - 10exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 147 22

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
writes table sybsystemprocs..sp_forceonline_page_rset_002 
read_writes table master..sysattributes (1)  
reads table master..sysmessages (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
writes table sybsystemprocs..sp_forceonline_page_rset_001 

CALLERS
called by proc sybsystemprocs..sp_forceonline_db