DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setsuspect_granularity  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    ** 18523, "%1!: Not allowed for System databases."
11    ** 18524, "%1!: Permission denied. This operation requires System Administrator (sa_role) role."                                
12    ** 18525, "%1!: Suspect granularity option '%2!' is not valid."
13    ** 18526, "%1!: The new values will become effective during the next recovery of the database."
14    ** 18527, "%1!: Object level suspect granularity is not supported in this release."
15    */
16    
17    create procedure sp_setsuspect_granularity
18        @dbname varchar(255) = NULL,
19        @susgran varchar(8) = NULL,
20        @option varchar(10) = NULL /* valid options - read_only and offline (only to be used during testing) */
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            @cur_susgran int,
28            @cfg_susgran int,
29            @display_susgran_cur varchar(10),
30            @display_susgran_cfg varchar(10),
31            @susgran_val int,
32            @default_susgran int,
33            @suspectpagebit int,
34            @class int,
35            @sysdbid int,
36            @option_val varchar(10)
37    
38        select @action = 1 /* add or modify entry */
39        select @attrib_id = 0 /* attribute is SUSPECT GRANULARITY */
40        select @object_type = 'D'
41        select @default_susgran = 0
42        select @cur_susgran = 0
43        select @cfg_susgran = 0
44        select @suspectpagebit = 32
45        select @class = 10
46    
47        if @@trancount = 0
48        begin
49            set chained off
50        end
51    
52        set transaction isolation level 1
53    
54    
55        /*
56        **  If no @dbname given, assume the current database
57        */
58    
59        if @dbname is null
60            select @dbname = db_name()
61    
62        /*
63        **  Verify the database name and get the @dbid 
64        */
65        select @dbid = db_id(@dbname)
66    
67        /*
68        **  If @dbname not found, say so.
69        */
70        if @dbid is NULL
71        begin
72            /*
73            ** 17421, "No such database -- run sp_helpdb to list databases."
74            */
75            exec sp_getmessage 17421, @msg output
76            print "sp_setsuspect_granularity: '%1!'", @msg
77            return (1)
78        end
79    
80        if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "sybsystemdb")
81        begin
82            /*
83            ** 18523, "'%1!': Not allowed for System databases."
84            */
85            raiserror 18523, "sp_setsuspect_granularity"
86            return (1)
87        end
88        /*
89        ** If only dbname is provided, then display the setting
90        */
91    
92        if @susgran is NULL
93            goto display_settings
94    
95        /*
96        **  Only the Accounts with SA role can execute it.
97        **  Call proc_role() with the required SA role.
98        */
99        if (proc_role("sa_role") < 1)
100       begin
101           /*
102           ** 18524, "'%1!':Permission denied. This operation requires System Administrator (sa_role) role."                                
103           */
104           raiserror 18524, "sp_setsuspect_granularity"
105           return (1)
106       end
107   
108   
109       if db_name() != "master"
110       begin
111           /*
112           ** 17428, "You must be in the 'master' database in order to change database options."                                
113           */
114           exec sp_getmessage 17428, @msg output
115           print "sp_setsuspect_granularity: %1!", @msg
116           return (1)
117       end
118   
119       /*
120       **  If we're in a transaction, disallow this since it might make recovery
121       **  impossible.
122       */
123       if @@trancount > 0
124       begin
125           /*
126           ** 17260, "Can't run %1! from within a transaction." 
127           */
128           raiserror 17260, "sp_setsuspect_granularity"
129           return (1)
130       end
131       else
132       begin
133           set chained off
134       end
135   
136       set transaction isolation level 1
137   
138       /*
139       ** Check to see that the input params are correct and then hook up with
140       ** Sysattributes table to enter data.
141       */
142   
143       if ((@susgran != "page") AND (@susgran != "object")
144               AND (@susgran != "database"))
145       BEGIN
146           /*
147           **	18525, %1!: Suspect granularity option '%2!' is not valid.
148           */
149           raiserror 18525, "sp_setsuspect_granularity", @susgran
150           return (1)
151       END
152       if (@susgran = "object")
153       BEGIN
154           /*
155           ** 18527, %1!: Object level suspect granularity is not supported in 
156           ** 		this release.
157           */
158           raiserror 18527, "sp_setsuspect_granularity", @susgran
159           return (1)
160       END
161       if (@option != "read_only") AND (@option != "offline") AND (@option != "read/write") AND (@option = "")
162       begin
163           /*
164           **      18525, %1!: Suspect granularity option '%2!' is not valid.
165           */
166           raiserror 18525, "sp_setsuspect_granularity", @option
167           return (1)
168       end
169   
170       if (@@clustermode = "shared disk cluster" AND @susgran != "database")
171       begin
172           /*
173           **	18446, %1!: The only valid suspect granularity option for the ASE Cluster Edition is 'database'.
174           */
175           raiserror 18446, "sp_setsuspect_granularity"
176           return 1
177       end
178   
179   
180       if @susgran = "page"
181           select @susgran_val = 2
182       if @susgran = "object"
183           select @susgran_val = 1
184       if @susgran = "database"
185           select @susgran_val = 0
186   
187       /*
188       ** if an entry already exists for this database then update the entry,
189       ** otherwise insert a new row for this database.
190       ** When updating, change only the config value (object_info2) and not the
191       ** current value (int_value). The config value will take effect only
192       ** during the next boot/load time recovery of the database, during which
193       ** time the current value will be updated to the config value.
194       */
195       if exists (select * from master.dbo.sysattributes where
196                   class = @class AND
197                   attribute = @attrib_id AND
198                   object_type = @object_type AND
199                   object = @dbid)
200       begin
201           /* get the configured suspect granularity value and the option value */
202           select @cur_susgran = int_value,
203               @cfg_susgran = object_info2,
204               @option_val = substring(char_value, 1, 10)
205           from master.dbo.sysattributes where
206               class = @class AND
207               attribute = @attrib_id AND
208               object_type = @object_type AND
209               object = @dbid
210   
211           /* 
212           ** if configured granularity is different from new value or the 
213           ** configured option is different from new value then the 
214           ** entry must be updated.
215           */
216           if (@cfg_susgran != @susgran_val OR
217                   @option_val != @option)
218           begin
219               update master.dbo.sysattributes
220               set object_info2 = @susgran_val,
221                   char_value = @option
222               where class = @class AND
223                   attribute = @attrib_id AND
224                   object_type = @object_type AND
225                   object = @dbid
226           end
227           /*
228           ** If the requested suspect granularity is database and the current
229           ** suspect granularity also happens to be database, then this represents
230           ** the default setting for the database that does not require any entry
231           ** in master..sysattributes. So delete the entry.
232           */
233           if (@susgran = "database" AND @cur_susgran = 0)
234           begin
235               delete master.dbo.sysattributes
236               where class = @class AND
237                   attribute = @attrib_id AND
238                   object_type = @object_type AND
239                   object = @dbid
240   
241               select @cfg_susgran = 0
242           end
243       end
244       else
245       begin
246           insert master.dbo.sysattributes
247           (class, attribute, object_type, object, int_value, object_info2, char_value)
248           values (@class, @attrib_id, @object_type, @dbid,
249               @default_susgran, @susgran_val, @option)
250   
251           /* This is the first time, so clear the bit in status2 field of sysdatabases */
252           update master.dbo.sysdatabases
253           set status2 = status2 & @suspectpagebit
254           where dbid = @dbid
255       end
256   
257   
258   
259       /*
260       ** Display the setting 
261       */
262   display_settings:
263   
264       select @cur_susgran = int_value,
265           @cfg_susgran = object_info2,
266           @option_val = substring(char_value, 1, 10)
267       from master.dbo.sysattributes
268       where class = @class AND
269           attribute = @attrib_id AND
270           object_type = @object_type AND
271           object = @dbid
272   
273       if (@cur_susgran = 2) select @display_susgran_cur = 'page'
274       if (@cur_susgran = 1) select @display_susgran_cur = 'object'
275       if (@cur_susgran = 0) select @display_susgran_cur = 'database'
276       if (@cfg_susgran = 2) select @display_susgran_cfg = 'page'
277       if (@cfg_susgran = 1) select @display_susgran_cfg = 'object'
278       if (@cfg_susgran = 0) select @display_susgran_cfg = 'database'
279   
280       if @option_val is NULL
281           select @option_val = "read/write"
282       select "DB Name" = convert(varchar(20), @dbname),
283           "Cur. Suspect Gran." = convert(varchar(10), @display_susgran_cur),
284           "Cfg. Suspect Gran." = convert(varchar(10), @display_susgran_cfg),
285           "Online mode" = @option_val
286   
287       if @susgran is NULL
288           return 0
289   
290       /*
291       ** 18526, "'%1!': The new values will become effective during the next recovery of the database."
292       */
293       exec sp_getmessage 18526, @msg output
294       print @msg, "sp_setsuspect_granularity", @dbname
295       return (0)
296   


exec sp_procxmode 'sp_setsuspect_granularity', 'AnyMode'
go

Grant Execute on sp_setsuspect_granularity to public
go
RESULT SETS
sp_setsuspect_granularity_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 161
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch attribute: smallint = int 248
 MTYP 4 Assignment type mismatch class: smallint = int 248
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 196
 QTYP 4 Comparison type mismatch smallint = int 196
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 197
 QTYP 4 Comparison type mismatch smallint = int 197
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 206
 QTYP 4 Comparison type mismatch smallint = int 206
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 207
 QTYP 4 Comparison type mismatch smallint = int 207
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 222
 QTYP 4 Comparison type mismatch smallint = int 222
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 223
 QTYP 4 Comparison type mismatch smallint = int 223
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 236
 QTYP 4 Comparison type mismatch smallint = int 236
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 237
 QTYP 4 Comparison type mismatch smallint = int 237
 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 268
 QTYP 4 Comparison type mismatch smallint = int 268
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 269
 QTYP 4 Comparison type mismatch smallint = int 269
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_setsuspect_granularity  
 MNER 3 No Error Check should check return value of exec 75
 MNER 3 No Error Check should check return value of exec 114
 MNER 3 No Error Check should check @@error after update 219
 MNER 3 No Error Check should check @@error after delete 235
 MNER 3 No Error Check should check @@error after insert 246
 MNER 3 No Error Check should check @@error after update 252
 MNER 3 No Error Check should check return value of exec 293
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 99
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 152
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 233
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 274
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 276
 MUCO 3 Useless Code Useless Brackets 277
 MUCO 3 Useless Code Useless Brackets 278
 MUCO 3 Useless Code Useless Brackets 295
 MUOT 3 Updates outside transaction 252
 MUPK 3 Update column which is part of a PK or unique index object_info2 220
 QAFM 3 Var Assignment from potentially many rows 202
 QAFM 3 Var Assignment from potentially many rows 264
 QISO 3 Set isolation level 52
 QISO 3 Set isolation level 136
 QIWC 3 Insert with not all columns specified missing 8 columns out of 15 247
 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}
196
 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}
206
 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}
222
 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}
236
 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}
268
 VNRD 3 Variable is not read @action 38
 VUNU 3 Variable is not used @sysdbid 35
 MRST 2 Result Set Marker 282
 MSUB 2 Subquery Marker 195
 MTR1 2 Metrics: Comments Ratio Comments: 39% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 35 = 43dec - 10exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 148 17

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)  
writes table master..sysdatabases (1)