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

exec sp_procxmode 'sp_forceonline_object', 'AnyMode'
go

Grant Execute on sp_forceonline_object to public
go

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	61
QTYP 4 Comparison type mismatch smallint = int	61
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	135
QTYP 4 Comparison type mismatch smallint = int	135
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	136
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	162
QTYP 4 Comparison type mismatch smallint = int	162
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	163
QTYP 4 Comparison type mismatch smallint = int	163
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	180
QTYP 4 Comparison type mismatch smallint = int	180
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	230
QTYP 4 Comparison type mismatch smallint = int	230
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	231
QTYP 4 Comparison type mismatch smallint = int	231
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	242
QTYP 4 Comparison type mismatch smallint = int	242
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	243
QTYP 4 Comparison type mismatch smallint = int	243
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	274
QTYP 4 Comparison type mismatch smallint = int	274
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	275
QTYP 4 Comparison type mismatch smallint = int	275
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	309
QTYP 4 Comparison type mismatch smallint = int	309
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	310
QTYP 4 Comparison type mismatch smallint = int	310
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	321
QTYP 4 Comparison type mismatch smallint = int	321
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	322
QTYP 4 Comparison type mismatch smallint = int	322
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	104
MNER 3 No Error Check should check @@error after delete	229
MNER 3 No Error Check should check @@error after update	240
MNER 3 No Error Check should check return value of exec	257
MNER 3 No Error Check should check return value of exec	266
MNER 3 No Error Check should check return value of exec	285
MNER 3 No Error Check should check return value of exec	302
MUCO 3 Useless Code Useless Brackets	82
MUCO 3 Useless Code Useless Brackets	89
MUCO 3 Useless Code Useless Brackets	95
MUCO 3 Useless Code Useless Brackets	106
MUCO 3 Useless Code Useless Brackets	119
MUCO 3 Useless Code Useless Brackets	140
MUCO 3 Useless Code Useless Brackets	147
MUCO 3 Useless Code Useless Brackets	150
MUCO 3 Useless Code Useless Brackets	157
MUCO 3 Useless Code Useless Brackets	175
MUCO 3 Useless Code Useless Brackets	187
MUCO 3 Useless Code Useless Brackets	196
MUCO 3 Useless Code Useless Brackets	211
MUCO 3 Useless Code Useless Brackets	213
MUCO 3 Useless Code Useless Brackets	221
MUCO 3 Useless Code Useless Brackets	226
MUCO 3 Useless Code Useless Brackets	239
MUCO 3 Useless Code Useless Brackets	251
MUCO 3 Useless Code Useless Brackets	268
MUCO 3 Useless Code Useless Brackets	280
MUCO 3 Useless Code Useless Brackets	287
MUCO 3 Useless Code Useless Brackets	290
MUCO 3 Useless Code Useless Brackets	291
MUCO 3 Useless Code Useless Brackets	328
MUOT 3 Updates outside transaction	240
QAFM 3 Var Assignment from potentially many rows	134
QAFM 3 Var Assignment from potentially many rows	179
QCRS 3 Conditional Result Set	304
QCRS 3 Conditional Result Set	316
QISO 3 Set isolation level	126
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique (error, dlevel, langid) Intersection: {error, langid}	59
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}	135
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}	162
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}	180
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}	230
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}	242
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}	274
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}	309
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}	321
VNRD 3 Variable is not read @whichone	54
VNRD 3 Variable is not read @sptlang	62
MRST 2 Result Set Marker	304
MRST 2 Result Set Marker	316
MSUB 2 Subquery Marker	58
MSUB 2 Subquery Marker	161
MTR1 2 Metrics: Comments Ratio Comments: 40%	27
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 31 = 40dec - 11exi + 2	27
MTR3 2 Metrics: Query Complexity Complexity: 147	27

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)