DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setsuspect_granularity  14 déc. 14Defects Propagation 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 or replace 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            @dummy int,
38            @nullarg char(1),
39            @gp_enabled int,
40            @status int
41    
42        select @action = 1 /* add or modify entry */
43        select @attrib_id = 0 /* attribute is SUSPECT GRANULARITY */
44        select @object_type = 'D'
45        select @default_susgran = 0
46        select @cur_susgran = 0
47        select @cfg_susgran = 0
48        select @suspectpagebit = 32
49        select @class = 10
50    
51        if @@trancount = 0
52        begin
53            set chained off
54        end
55    
56        set transaction isolation level 1
57    
58    
59        /*
60        **  If no @dbname given, assume the current database
61        */
62    
63        if @dbname is null
64            select @dbname = db_name()
65    
66        /*
67        **  Verify the database name and get the @dbid 
68        */
69        select @dbid = db_id(@dbname)
70    
71        /*
72        **  If @dbname not found, say so.
73        */
74        if @dbid is NULL
75        begin
76            /*
77            ** 17421, "No such database -- run sp_helpdb to list databases."
78            */
79            exec sp_getmessage 17421, @msg output
80            print "sp_setsuspect_granularity: '%1!'", @msg
81            return (1)
82        end
83    
84        if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "sybsystemdb")
85        begin
86            /*
87            ** 18523, "'%1!': Not allowed for System databases."
88            */
89            raiserror 18523, "sp_setsuspect_granularity"
90            return (1)
91        end
92        /*
93        ** If only dbname is provided, then display the setting
94        */
95    
96        if @susgran is NULL
97            goto display_settings
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_setsuspect_granularity: %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_setsuspect_granularity"
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       ** If granular permissions is not enabled then sa_role is required.
130       ** If granular permissions is enabled then the permission 'own database' is
131       ** required. proc_role and proc_auditperm will also do auditing
132       ** if required. Both will also print error message if required.
133       */
134   
135       select @nullarg = NULL
136       execute @status = sp_aux_checkroleperm "sa_role", "own database",
137           @dbname, @gp_enabled output
138       /* For Auditing */
139       if (@gp_enabled = 0)
140           select @dummy = proc_role("sa_role")
141       else
142       begin
143           select @dummy = proc_auditperm("own database", @status, @dbname)
144       end
145   
146       if (@status != 0)
147           return (1)
148   
149       /*
150       ** Check to see that the input params are correct and then hook up with
151       ** Sysattributes table to enter data.
152       */
153   
154       if ((@susgran != "page") AND (@susgran != "object")
155               AND (@susgran != "database"))
156       BEGIN
157           /*
158           **	18525, %1!: Suspect granularity option '%2!' is not valid.
159           */
160           raiserror 18525, "sp_setsuspect_granularity", @susgran
161           return (1)
162       END
163       if (@susgran = "object")
164       BEGIN
165           /*
166           ** 18527, %1!: Object level suspect granularity is not supported in 
167           ** 		this release.
168           */
169           raiserror 18527, "sp_setsuspect_granularity", @susgran
170           return (1)
171       END
172       if (@option != "read_only") AND (@option != "offline") AND (@option != "read/write") AND (@option = "")
173       begin
174           /*
175           **      18525, %1!: Suspect granularity option '%2!' is not valid.
176           */
177           raiserror 18525, "sp_setsuspect_granularity", @option
178           return (1)
179       end
180   
181       if (@@clustermode = "shared disk cluster" AND @susgran != "database")
182       begin
183           /*
184           **	18446, %1!: The only valid suspect granularity option for the ASE Cluster Edition is 'database'.
185           */
186           raiserror 18446, "sp_setsuspect_granularity"
187           return 1
188       end
189   
190   
191       if @susgran = "page"
192           select @susgran_val = 2
193       if @susgran = "object"
194           select @susgran_val = 1
195       if @susgran = "database"
196           select @susgran_val = 0
197   
198       /*
199       ** if an entry already exists for this database then update the entry,
200       ** otherwise insert a new row for this database.
201       ** When updating, change only the config value (object_info2) and not the
202       ** current value (int_value). The config value will take effect only
203       ** during the next boot/load time recovery of the database, during which
204       ** time the current value will be updated to the config value.
205       */
206       if exists (select * from master.dbo.sysattributes where
207                   class = @class AND
208                   attribute = @attrib_id AND
209                   object_type = @object_type AND
210                   object = @dbid)
211       begin
212           /* get the configured suspect granularity value and the option value */
213           select @cur_susgran = int_value,
214               @cfg_susgran = object_info2,
215               @option_val = substring(char_value, 1, 10)
216           from master.dbo.sysattributes where
217               class = @class AND
218               attribute = @attrib_id AND
219               object_type = @object_type AND
220               object = @dbid
221   
222           /* 
223           ** if configured granularity is different from new value or the 
224           ** configured option is different from new value then the 
225           ** entry must be updated.
226           */
227           if (@cfg_susgran != @susgran_val OR
228                   @option_val != @option)
229           begin
230               update master.dbo.sysattributes
231               set object_info2 = @susgran_val,
232                   char_value = @option
233               where class = @class AND
234                   attribute = @attrib_id AND
235                   object_type = @object_type AND
236                   object = @dbid
237           end
238           /*
239           ** If the requested suspect granularity is database and the current
240           ** suspect granularity also happens to be database, then this represents
241           ** the default setting for the database that does not require any entry
242           ** in master..sysattributes. So delete the entry.
243           */
244           if (@susgran = "database" AND @cur_susgran = 0)
245           begin
246               delete master.dbo.sysattributes
247               where class = @class AND
248                   attribute = @attrib_id AND
249                   object_type = @object_type AND
250                   object = @dbid
251   
252               select @cfg_susgran = 0
253           end
254       end
255       else
256       begin
257           insert master.dbo.sysattributes
258           (class, attribute, object_type, object, int_value, object_info2, char_value)
259           values (@class, @attrib_id, @object_type, @dbid,
260               @default_susgran, @susgran_val, @option)
261   
262           /* This is the first time, so clear the bit in status2 field of sysdatabases */
263           update master.dbo.sysdatabases
264           set status2 = status2 & @suspectpagebit
265           where dbid = @dbid
266       end
267   
268   
269   
270       /*
271       ** Display the setting 
272       */
273   display_settings:
274   
275       select @cur_susgran = int_value,
276           @cfg_susgran = object_info2,
277           @option_val = substring(char_value, 1, 10)
278       from master.dbo.sysattributes
279       where class = @class AND
280           attribute = @attrib_id AND
281           object_type = @object_type AND
282           object = @dbid
283   
284       if (@cur_susgran = 2) select @display_susgran_cur = 'page'
285       if (@cur_susgran = 1) select @display_susgran_cur = 'object'
286       if (@cur_susgran = 0) select @display_susgran_cur = 'database'
287       if (@cfg_susgran = 2) select @display_susgran_cfg = 'page'
288       if (@cfg_susgran = 1) select @display_susgran_cfg = 'object'
289       if (@cfg_susgran = 0) select @display_susgran_cfg = 'database'
290   
291       if @option_val is NULL
292           select @option_val = "read/write"
293       select "DB Name" = convert(varchar(20), @dbname),
294           "Cur. Suspect Gran." = convert(varchar(10), @display_susgran_cur),
295           "Cfg. Suspect Gran." = convert(varchar(10), @display_susgran_cfg),
296           "Online mode" = @option_val
297   
298       if @susgran is NULL
299           return 0
300   
301       /*
302       ** 18526, "'%1!': The new values will become effective during the next recovery of the database."
303       */
304       exec sp_getmessage 18526, @msg output
305       print @msg, "sp_setsuspect_granularity", @dbname
306       return (0)
307   


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 172
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch attribute: smallint = int 259
 MTYP 4 Assignment type mismatch class: smallint = int 259
 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 208
 QTYP 4 Comparison type mismatch smallint = int 208
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 217
 QTYP 4 Comparison type mismatch smallint = int 217
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 218
 QTYP 4 Comparison type mismatch smallint = int 218
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 233
 QTYP 4 Comparison type mismatch smallint = int 233
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 234
 QTYP 4 Comparison type mismatch smallint = int 234
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 247
 QTYP 4 Comparison type mismatch smallint = int 247
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 248
 QTYP 4 Comparison type mismatch smallint = int 248
 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 279
 QTYP 4 Comparison type mismatch smallint = int 279
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 280
 QTYP 4 Comparison type mismatch smallint = int 280
 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 79
 MNER 3 No Error Check should check return value of exec 104
 MNER 3 No Error Check should check @@error after update 230
 MNER 3 No Error Check should check @@error after delete 246
 MNER 3 No Error Check should check @@error after insert 257
 MNER 3 No Error Check should check @@error after update 263
 MNER 3 No Error Check should check return value of exec 304
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 244
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 285
 MUCO 3 Useless Code Useless Brackets 286
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 306
 MUOT 3 Updates outside transaction 263
 MUPK 3 Update column which is part of a PK or unique index object_info2 231
 QAFM 3 Var Assignment from potentially many rows 213
 QAFM 3 Var Assignment from potentially many rows 275
 QISO 3 Set isolation level 56
 QISO 3 Set isolation level 126
 QIWC 3 Insert with not all columns specified missing 8 columns out of 15 258
 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}
207
 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}
217
 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}
233
 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}
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}
279
 VNRD 3 Variable is not read @action 42
 VNRD 3 Variable is not read @nullarg 135
 VNRD 3 Variable is not read @dummy 143
 VUNU 3 Variable is not used @sysdbid 35
 MRST 2 Result Set Marker 293
 MSUB 2 Subquery Marker 206
 MTR1 2 Metrics: Comments Ratio Comments: 39% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 38 = 45dec - 9exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 153 17

DATA PROPAGATION detailed
ColumnWritten To
@dbnamesysattributes.object   °.object_info1   °.object_info3   °.char_value   sysremotelogins.remoteserverid   sysservers.srvid   sysattributes.object_info1   °.object_info3   °.int_value   °.char_value  
sp_dropdevice_rset_001.device sp_checknames_rset_006.remoteserverid sp_checknames_rset_007.srvid sp_checknames_rset_008.srvid sp_dbrecovery_order_rset_001.Database Name °.Database id sp_displayroles_rset_001.Role Name sp_displayroles_rset_002.Role Name °.Grantee sp_displayroles_rset_003.Role Name
sp_displayroles_rset_004.Role Name sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.Object °.status sp_forceonline_object_rset_002.Object °.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Object °.Access
sp_listsuspect_page_rset_001.Object °.Access sp_makesuspect_obj_rset_001.Obj °.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Obj
°.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Obj °.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_memlog_rset_001.dumps_per_file sp_passwordpolicy_rset_001.message sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.DB Name
°.Online mode sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference
@optionsysattributes.char_value   sysattributes.int_value   °.char_value   sp_dropdevice_rset_001.device sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.status sp_forceonline_object_rset_002.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Access
sp_listsuspect_page_rset_001.Access sp_makesuspect_obj_rset_001.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_passwordpolicy_rset_001.message
sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
read_writes table master..sysattributes (1)  
writes table master..sysdatabases (1)  
writes table sybsystemprocs..sp_setsuspect_granularity_rset_001 
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)