DatabaseProcApplicationCreatedLinks
sybsystemprocssp_makesuspect_obj  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** 17260, "Can't run %1! from within a transaction." 
6     ** 17421, "No such database -- run sp_helpdb to list databases."
7     ** 17422, "The 'master' database's options can not be changed."
8     ** 17428, "You must be in the 'master' database in order to change
9     **	database options."                                
10    */
11    /*
12    **************************  WARNING   WARNING   WARNING ************************
13    **************************  WARNING   WARNING   WARNING ************************
14    ** This stored procedure is only meant for testing and testing ONLY - to test **
15    ** the Recovery Fault Isolation (RFI) feature by simulating various types of  **
16    ** corruptions and isolating them during recovery using RFI feature.          ** 
17    ** This should never be used in any production environment.                   **
18    ********************************************************************************
19    */
20    create procedure sp_makesuspect_obj
21        @dbname varchar(30) = NULL,
22        @objname varchar(30) = NULL,
23        @indid int = 0,
24        @logtype int = - 2,
25        @pgtype int = - 2,
26        @errtype int = - 2,
27        @errorcnt int = 0,
28        @errdelay int = 0
29    as
30        declare @dbid int, /* dbid of the database */
31            @attrib_id int,
32            @action int,
33            @object_type varchar(2),
34            @msg varchar(250),
35            @charvalue varchar(255),
36            @sptlang int,
37            @whichone int, /* which language? */
38            @error_index int,
39            @shift16 int,
40            @objid int,
41            @class int,
42            @indid_offset int,
43            @logtype_offset int,
44            @pgtype_offset int,
45            @errtype_offset int,
46            @errorcnt_offset int,
47            @errdelay_offset int
48    
49    
50    
51        select @attrib_id = 5 /* attribute is SUSPECT error */
52        select @object_type = 'D'
53        select @class = 10
54    
55        select @indid_offset = 1
56        select @logtype_offset = 9
57        select @pgtype_offset = 17
58        select @errtype_offset = 25
59        select @errorcnt_offset = 33
60        select @errdelay_offset = 41
61    
62        if @@trancount = 0
63        begin
64            set chained off
65        end
66    
67        set transaction isolation level 1
68    
69        select @sptlang = @@langid, @whichone = 0
70    
71        if @@langid != 0
72        begin
73            if not exists (
74                    select * from master.dbo.sysmessages where error
75                        between 17050 and 17069
76                        and langid = @@langid)
77                select @sptlang = 0
78        end
79    
80    
81        /*
82        **  If no @dbname given, assume the current database
83        */
84    
85        if @dbname is null
86            select @dbname = db_name()
87    
88        /*
89        **  Verify the database name and get the @dbid 
90        */
91        select @dbid = dbid from master.dbo.sysdatabases
92        where name = @dbname
93    
94        /*
95        **  If @dbname not found, say so.
96        */
97        if @dbid is NULL
98        begin
99            /*
100           ** 17421, "No such database -- run sp_helpdb to list databases."
101           */
102           raiserror 17421
103           return (1)
104       end
105   
106       /* get the object id  */
107       if @objname is null
108           select @objid = - 1
109       else
110           select @objid = object_id(@dbname + '..' + @objname)
111   
112       if @objid is null AND @objname != "simobj" AND @objname != "allocobj"
113       BEGIN
114           /*
115           **	xxxx0, Suspect error value %2 is not valid
116           */
117           /*exec sp_getmessage xxxx0, @msg output
118           print @msg*/
119           print "Invalid Object in the database"
120           return (1)
121       END
122       if @objname = "simobj"
123       begin
124           select @objid = 0
125           select @errtype = 0
126           select @pgtype = 0
127           select @errorcnt = 0
128           select @errdelay = 0
129       end
130   
131       /*
132       ** If only dbname is provided, then display the setting
133       */
134   
135       if @errtype = - 2
136       begin
137           if (@objid = - 1)
138           begin
139               select "DBName" = convert(varchar(15), @dbname),
140                   "Obj" = convert(varchar(15), object_name(object_info1, @dbid)),
141                   "Indid" = substring(char_value, @indid_offset, 5),
142                   "LogType" = substring(char_value, @logtype_offset, 5),
143                   "PageType" = substring(char_value, @pgtype_offset, 5),
144                   "ErrType" = substring(char_value, @errtype_offset, 5),
145                   "Delay" = substring(char_value, @errorcnt_offset, 5),
146                   "TotalNum" = substring(char_value, @errdelay_offset, 5)
147               from master.dbo.sysattributes
148               where class = @class AND
149                   attribute = @attrib_id AND
150                   object_type = @object_type AND
151                   object = @dbid
152           end
153           else
154           begin
155               select "DBName" = convert(varchar(15), @dbname),
156                   "Obj" = convert(varchar(15), object_name(object_info1, @dbid)),
157                   "Indid" = substring(char_value, @indid_offset, 5),
158                   "LogType" = substring(char_value, @logtype_offset, 5),
159                   "PageType" = substring(char_value, @pgtype_offset, 5),
160                   "ErrType" = substring(char_value, @errtype_offset, 5),
161                   "Delay" = substring(char_value, @errorcnt_offset, 5),
162                   "TotalNum" = substring(char_value, @errdelay_offset, 5)
163               from master.dbo.sysattributes
164               where class = @class AND
165                   attribute = @attrib_id AND
166                   object_type = @object_type AND
167                   object = @dbid AND
168                   object_info1 = @objid AND
169                   substring(char_value, 0, 8) = convert(char(8), @indid)
170           end
171   
172   
173           /*	select 	"DB Name" = @dbname,
174           **		"Error Type" = @errortype,
175           **		"Error Freq" = @error_freq 
176           */
177           return (0)
178       end
179   
180       /*
181       **  Only the Accounts with SA role can execute it.
182       **  Call proc_role() with the required SA role.
183       */
184       if (proc_role("sa_role") < 1)
185           return (1)
186   
187       if @dbid = 1
188       begin
189           /*
190           ** 17422, "The 'master' database's options can not be changed."
191           */
192           raiserror 17422
193           return (1)
194       end
195   
196       if db_name() != "master"
197       begin
198           /*
199           ** 17428, "You must be in the 'master' database in order to change database options."                                
200           */
201           raiserror 17428
202           return (1)
203       end
204   
205       /*
206       **  If we're in a transaction, disallow this since it might make recovery
207       **  impossible.
208       */
209       if @@trancount > 0
210       begin
211           /*
212           ** 17260, "Can't run %1! from within a transaction." 
213           */
214           raiserror 17260, "sp_makesuspect_obj"
215           return (1)
216       end
217       else
218       begin
219           set chained off
220       end
221   
222       set transaction isolation level 1
223   
224       /*
225       ** Check to see that the input params are correct and then hook up with
226       ** Sysattributes table to enter data.
227       */
228   
229   
230       if (@errtype < - 1) OR (@errtype > 50) or
231           (@pgtype < - 1) or (@pgtype > 8) or
232           (@logtype < - 1) or (@logtype > 58) or
233           (@errorcnt < 0) or (@errorcnt > 999999) or
234           (@errdelay < 0) or (@errdelay > 999999)
235       BEGIN
236           /*
237           **	xxxx0, Suspect error value %2 is not valid
238           */
239           /*exec sp_getmessage xxxx0, @msg output
240           print @msg*/
241           print "Invalid suspect error value"
242           return (1)
243       END
244       select @charvalue = convert(char(8), @indid) + convert(char(8), @logtype) +
245           convert(char(8), @pgtype) + convert(char(8), @errtype) +
246           convert(char(8), @errorcnt) + convert(char(8), @errdelay)
247   
248       /*
249       ** if an entry already exists for this database then delet the entry,
250       ** and insert a new row for this database.
251       */
252       IF exists (select * from master.dbo.sysattributes where
253                   class = @class AND
254                   attribute = @attrib_id AND
255                   object_type = @object_type AND
256                   object = @dbid AND
257                   object_info1 = @objid AND
258                   substring(char_value, @indid_offset, 8) = convert(char(8), @indid) AND
259                   substring(char_value, @logtype_offset, 8) = convert(char(8), @logtype) AND
260                   substring(char_value, @pgtype_offset, 8) = convert(char(8), @pgtype) AND
261                   substring(char_value, @errtype_offset, 8) = convert(char(8), @errtype))
262       begin
263           /* save the error index and delete the row */
264           select @error_index = object_info2 from master.dbo.sysattributes where
265               class = @class AND
266               attribute = @attrib_id AND
267               object_type = @object_type AND
268               object = @dbid AND
269               object_info1 = @objid AND
270               substring(char_value, @indid_offset, 8) = convert(char(8), @indid) AND
271               substring(char_value, @logtype_offset, 8) = convert(char(8), @logtype) AND
272               substring(char_value, @pgtype_offset, 8) = convert(char(8), @pgtype) AND
273               substring(char_value, @errtype_offset, 8) = convert(char(8), @errtype)
274           delete master.dbo.sysattributes
275           where class = @class AND
276               attribute = @attrib_id AND
277               object_type = @object_type AND
278               object = @dbid AND
279               object_info1 = @objid AND
280               substring(char_value, @indid_offset, 8) = convert(char(8), @indid) AND
281               substring(char_value, @logtype_offset, 8) = convert(char(8), @logtype) AND
282               substring(char_value, @pgtype_offset, 8) = convert(char(8), @pgtype) AND
283               substring(char_value, @errtype_offset, 8) = convert(char(8), @errtype)
284       end
285       else
286       begin
287           /* create a new error index which is got by incrementing the previous highest
288           error index*/
289           select @error_index = 0
290           if (@objname = "simobj")
291               select @error_index = 0
292           else
293           begin
294               select @error_index = 1
295               while exists (select * from master.dbo.sysattributes where
296                           class = @class AND
297                           attribute = @attrib_id AND
298                           object_type = @object_type AND
299                           object_info2 = @error_index)
300               begin
301                   select @error_index = @error_index + 1
302               end
303           end
304       end
305       insert master.dbo.sysattributes
306       (class, attribute, object_type, object, object_info1, object_info2, char_value)
307       values (@class, @attrib_id, @object_type, @dbid, @objid, @error_index, @charvalue)
308   
309   
310   
311       /*
312       ** Display the setting 
313       */
314   
315       select "DBName" = convert(varchar(15), @dbname),
316           "Obj" = convert(varchar(15), object_name(object_info1, @dbid)),
317           "Indid" = substring(char_value, @indid_offset, 5),
318           "LogType" = substring(char_value, @logtype_offset, 5),
319           "PageType" = substring(char_value, @pgtype_offset, 5),
320           "ErrType" = substring(char_value, @errtype_offset, 5),
321           "Delay" = substring(char_value, @errorcnt_offset, 5),
322           "TotalNum" = substring(char_value, @errdelay_offset, 5)
323       from master.dbo.sysattributes
324       where class = @class AND
325           attribute = @attrib_id AND
326           object_type = @object_type AND
327           object = @dbid AND
328           object_info1 = @objid AND
329           substring(char_value, @indid_offset, 8) = convert(char(8), @indid) AND
330           substring(char_value, @logtype_offset, 8) = convert(char(8), @logtype) AND
331           substring(char_value, @pgtype_offset, 8) = convert(char(8), @pgtype) AND
332           substring(char_value, @errtype_offset, 8) = convert(char(8), @errtype)
333   
334   
335       /*
336       ** xxxx3, "The new suspect granularity will become effective
337       **			during next load/boot time recovery"
338       */
339       /*exec sp_getmessage xxxx1, @msg output
340       print @msg*/
341       print "This error value will effective with next recovery of db"
342       return (0)
343   

RESULT SETS
sp_makesuspect_obj_rset_003
sp_makesuspect_obj_rset_002
sp_makesuspect_obj_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
 MTYP 4 Assignment type mismatch attribute: smallint = int 307
 MTYP 4 Assignment type mismatch class: smallint = int 307
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 76
 QTYP 4 Comparison type mismatch smallint = int 76
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 148
 QTYP 4 Comparison type mismatch smallint = int 148
 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 164
 QTYP 4 Comparison type mismatch smallint = int 164
 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 253
 QTYP 4 Comparison type mismatch smallint = int 253
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 254
 QTYP 4 Comparison type mismatch smallint = int 254
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 265
 QTYP 4 Comparison type mismatch smallint = int 265
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 266
 QTYP 4 Comparison type mismatch smallint = int 266
 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 276
 QTYP 4 Comparison type mismatch smallint = int 276
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 296
 QTYP 4 Comparison type mismatch smallint = int 296
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 297
 QTYP 4 Comparison type mismatch smallint = int 297
 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 325
 QTYP 4 Comparison type mismatch smallint = int 325
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysmessages  
 MNER 3 No Error Check should check @@error after delete 274
 MNER 3 No Error Check should check @@error after insert 305
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 215
 MUCO 3 Useless Code Useless Brackets 242
 MUCO 3 Useless Code Useless Brackets 290
 MUCO 3 Useless Code Useless Brackets 342
 MUOT 3 Updates outside transaction 305
 QAFM 3 Var Assignment from potentially many rows 264
 QCRS 3 Conditional Result Set 139
 QCRS 3 Conditional Result Set 155
 QISO 3 Set isolation level 67
 QISO 3 Set isolation level 222
 QIWC 3 Insert with not all columns specified missing 8 columns out of 15 306
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
74
 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}
148
 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_info1, object, class}
164
 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, class, object, object_type, object_info1}
253
 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, class, object, object_type, object_info1}
265
 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, class, object, object_type, object_info1}
275
 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_info2, attribute, class}
296
 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, class, object, object_type, object_info1}
324
 VNRD 3 Variable is not read @whichone 69
 VNRD 3 Variable is not read @sptlang 77
 VUNU 3 Variable is not used @action 32
 VUNU 3 Variable is not used @msg 34
 VUNU 3 Variable is not used @shift16 39
 MRST 2 Result Set Marker 139
 MRST 2 Result Set Marker 155
 MRST 2 Result Set Marker 315
 MSUB 2 Subquery Marker 73
 MSUB 2 Subquery Marker 252
 MSUB 2 Subquery Marker 295
 MTR1 2 Metrics: Comments Ratio Comments: 24% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 44 = 50dec - 8exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 163 20

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)  
reads table master..sysdatabases (1)  
reads table master..sysmessages (1)