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


exec sp_procxmode 'sp_forceonline_object', 'AnyMode'
go

Grant Execute on sp_forceonline_object to public
go
RESULT SETS
sp_forceonline_object_rset_002
sp_forceonline_object_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 67
 QTYP 4 Comparison type mismatch smallint = int 67
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 149
 QTYP 4 Comparison type mismatch smallint = int 149
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 150
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 176
 QTYP 4 Comparison type mismatch smallint = int 176
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 177
 QTYP 4 Comparison type mismatch smallint = int 177
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 194
 QTYP 4 Comparison type mismatch smallint = int 194
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 195
 QTYP 4 Comparison type mismatch smallint = int 195
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 244
 QTYP 4 Comparison type mismatch smallint = int 244
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 245
 QTYP 4 Comparison type mismatch smallint = int 245
 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 288
 QTYP 4 Comparison type mismatch smallint = int 288
 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 323
 QTYP 4 Comparison type mismatch smallint = int 323
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 324
 QTYP 4 Comparison type mismatch smallint = int 324
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 335
 QTYP 4 Comparison type mismatch smallint = int 335
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 336
 QTYP 4 Comparison type mismatch smallint = int 336
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_forceonline_object  
 MNER 3 No Error Check should check return value of exec 118
 MNER 3 No Error Check should check @@error after delete 243
 MNER 3 No Error Check should check @@error after update 254
 MNER 3 No Error Check should check return value of exec 271
 MNER 3 No Error Check should check return value of exec 280
 MNER 3 No Error Check should check return value of exec 299
 MNER 3 No Error Check should check return value of exec 316
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 225
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 235
 MUCO 3 Useless Code Useless Brackets 240
 MUCO 3 Useless Code Useless Brackets 253
 MUCO 3 Useless Code Useless Brackets 265
 MUCO 3 Useless Code Useless Brackets 282
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 301
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 342
 MUOT 3 Updates outside transaction 254
 QAFM 3 Var Assignment from potentially many rows 148
 QAFM 3 Var Assignment from potentially many rows 193
 QCRS 3 Conditional Result Set 318
 QCRS 3 Conditional Result Set 330
 QISO 3 Set isolation level 140
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
65
 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}
149
 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: {attribute, object_type, object_info2, object_info1, object, class}
176
 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: {attribute, object_type, object_info2, object_info1, object, class}
194
 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: {attribute, object_type, object_info2, object_info1, object, class}
244
 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: {attribute, object_type, object_info2, object_info1, object, 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}
288
 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}
323
 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: {attribute, object_type, object_info2, object_info1, object, class}
335
 VNRD 3 Variable is not read @issystemdb 53
 VNRD 3 Variable is not read @whichone 60
 VNRD 3 Variable is not read @sptlang 68
 VNRD 3 Variable is not read @nullarg 99
 VNRD 3 Variable is not read @dummy 107
 MRST 2 Result Set Marker 318
 MRST 2 Result Set Marker 330
 MSUB 2 Subquery Marker 64
 MSUB 2 Subquery Marker 175
 MTR1 2 Metrics: Comments Ratio Comments: 39% 27
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 34 = 42dec - 10exi + 2 27
 MTR3 2 Metrics: Query Complexity Complexity: 153 27

DATA PROPAGATION detailed
ColumnWritten To
@dbnamesp_forceonline_object_rset_001.DBName °.Object sp_forceonline_object_rset_002.DBName °.Object

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_forceonline_object_rset_001 
reads table master..sysmessages (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
writes table sybsystemprocs..sp_forceonline_object_rset_002 
read_writes table master..sysattributes (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