Database	Proc	Application	Created	Links
sybsystemprocs	sp_makesuspect_obj		31 Aug 14	Defects 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   

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)