DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setsuspect_error  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 *** WARNING *** WARNING *** WARNING 
13    **
14    **  This procedure is solely for testing and debugging purposes only.
15    **           DO NOT USE IT ON PRODUCTION SYSTEMS.
16    */
17    
18    create procedure sp_setsuspect_error
19        @dbname varchar(255) = NULL,
20        @errornum int = - 1
21    as
22        declare @dbid int, /* dbid of the database */
23            @attrib_id int,
24            @action int,
25            @object_type varchar(2),
26            @msg varchar(1024),
27            @class int,
28            @errortype int,
29            @error_freq int,
30            @sptlang int,
31            @sysdbid int,
32            @comment varchar(255),
33            @whichone int /* which language? */
34    
35        select @attrib_id = 4 /* attribute is SUSPECT error */
36        select @object_type = 'D'
37        select @errortype = 80
38        select @error_freq = 80
39        select @class = 10
40    
41    
42    
43        if @@trancount = 0
44        begin
45            set chained off
46        end
47    
48        set transaction isolation level 1
49    
50        select @sptlang = @@langid, @whichone = 0
51    
52        if @@langid != 0
53        begin
54            if not exists (
55                    select * from master.dbo.sysmessages where error
56                        between 17050 and 17069
57                        and langid = @@langid)
58                select @sptlang = 0
59        end
60    
61    
62        /*
63        **  If no @dbname given, assume the current database
64        */
65    
66        if @dbname is null
67            select @dbname = db_name()
68    
69        /*
70        **  Verify the database name and get the @dbid 
71        */
72        select @dbid = dbid from master.dbo.sysdatabases
73        where name = @dbname
74    
75        /*
76        **  If @dbname not found, say so.
77        */
78        if @dbid is NULL
79        begin
80            /*
81            ** 17421, "No such database -- run sp_helpdb to list databases."
82            */
83            exec sp_getmessage 17421, @msg output
84            print @msg
85            return (1)
86        end
87    
88        if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "sybsystemdb")
89        begin
90            /*
91            ** 18523, "'%1!': Not allowed for System databases."
92            */
93            raiserror 18523, "sp_setsuspect_error"
94            return (1)
95        end
96    
97        /*
98        ** If only dbname is provided, then display the setting
99        */
100   
101       if @errornum = - 1
102       begin
103   
104           select "DBName" = @dbname, "Error Num" = int_value
105           from master.dbo.sysattributes
106           where class = @class AND
107               attribute = @attrib_id AND
108               object_type = @object_type AND
109               object = @dbid
110   
111           return (0)
112       end
113   
114       /*
115       **  Only the Accounts with SA role can execute it.
116       **  Call proc_role() with the required SA role.
117       */
118       if (proc_role("sa_role") < 1)
119       begin
120           /*
121           ** 18524, "'%1!':Permission denied. This operation requires System Administrator (sa_role) role."                                
122           */
123           exec sp_getmessage 18524, @msg output
124           print @msg, "sp_setsuspect_error"
125           return (1)
126       end
127   
128   
129       if db_name() != "master"
130       begin
131           /*
132           ** 17428, "You must be in the 'master' database in order to change database options."                                
133           */
134           exec sp_getmessage 17428, @msg output
135           print "sp_setsuspect_error: '%1!'", @msg
136           return (1)
137       end
138   
139       /*
140       **  If we're in a transaction, disallow this since it might make recovery
141       **  impossible.
142       */
143       if @@trancount > 0
144       begin
145           /*
146           ** 17260, "Can't run %1! from within a transaction." 
147           */
148           exec sp_getmessage 17260, @msg output
149           print @msg, "sp_setsuspect_error"
150           return (1)
151       end
152       else
153       begin
154           set chained off
155       end
156   
157       set transaction isolation level 1
158   
159   
160       /*
161       ** if an entry already exists for this database then update the entry,
162       ** otherwise insert a new row for this database.
163       ** When updating, change only the config value (object_info2) and not the
164       ** current value (int_value). The config value will take effect only
165       ** during the next boot/load time recovery of the database, during which
166       ** time the current value will be updated to the config value.
167       */
168       IF not exists (select * from master.dbo.sysattributes where
169                   class = @class AND
170                   attribute = @attrib_id AND
171                   object_type = @object_type AND
172                   object = @dbid AND
173                   int_value = @errornum)
174       begin
175           select @comment = "This error number will be added to the errors Recovery Fault Isolation can isolate"
176           insert master.dbo.sysattributes
177           (class, attribute, object_type, object, object_info1, int_value)
178           values (@class, @attrib_id, @object_type, @dbid, @errornum,
179               @errornum)
180       end
181   
182   
183   
184       /*
185       ** Display the setting 
186       */
187   
188       select "Database Name" = @dbname,
189           "Error Num" = int_value
190       from master.dbo.sysattributes
191       where class = @class AND
192           attribute = @attrib_id AND
193           object_type = @object_type AND
194           object = @dbid AND
195           int_value = @errornum
196   
197   
198       /*
199       ** 18526, "'%1!': The new values will become effective during the next recovery of the database."
200       */
201       exec sp_getmessage 18526, @msg output
202       print @msg, "sp_setsuspect_error", @dbname
203       return (0)
204   


exec sp_procxmode 'sp_setsuspect_error', 'AnyMode'
go

Grant Execute on sp_setsuspect_error to public
go
RESULT SETS
sp_setsuspect_error_rset_002
sp_setsuspect_error_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 178
 MTYP 4 Assignment type mismatch class: smallint = int 178
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 57
 QTYP 4 Comparison type mismatch smallint = int 57
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 106
 QTYP 4 Comparison type mismatch smallint = int 106
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 107
 QTYP 4 Comparison type mismatch smallint = int 107
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 169
 QTYP 4 Comparison type mismatch smallint = int 169
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 170
 QTYP 4 Comparison type mismatch smallint = int 170
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 191
 QTYP 4 Comparison type mismatch smallint = int 191
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 192
 QTYP 4 Comparison type mismatch smallint = int 192
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_setsuspect_error  
 MNER 3 No Error Check should check return value of exec 83
 MNER 3 No Error Check should check return value of exec 123
 MNER 3 No Error Check should check return value of exec 134
 MNER 3 No Error Check should check return value of exec 148
 MNER 3 No Error Check should check @@error after insert 176
 MNER 3 No Error Check should check return value of exec 201
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 125
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 203
 QCRS 3 Conditional Result Set 104
 QISO 3 Set isolation level 48
 QISO 3 Set isolation level 157
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 177
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}		 55
 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}		 106
 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}		 169
 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}		 191
 VNRD 3 Variable is not read @errortype 37
 VNRD 3 Variable is not read @error_freq 38
 VNRD 3 Variable is not read @whichone 50
 VNRD 3 Variable is not read @sptlang 58
 VNRD 3 Variable is not read @comment 175
 VUNU 3 Variable is not used @action 24
 VUNU 3 Variable is not used @sysdbid 31
 MRST 2 Result Set Marker 104
 MRST 2 Result Set Marker 188
 MSUB 2 Subquery Marker 54
 MSUB 2 Subquery Marker 168
 MTR1 2 Metrics: Comments Ratio Comments: 41% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 17dec - 7exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 82 18

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysattributes (1)  
reads table master..sysdatabases (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)