DatabaseProcApplicationCreatedLinks
sybsystemprocssp_addexeclass  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** Messages for "sp_addexeclass"
6     **
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18249, "Name '%1!' is not valid."
9     ** 18252, "'%1!' value '%2!' is not valid."
10    ** 18253, "No specification for engine group '%1!' exists"
11    ** 18254, "Failed to update attributes of objects assigned to class '%1!'.
12    **	   Check server errorlog for any additional information."
13    ** 18367, "The specified thread pool does not exist."
14    ** 18522, "Execution Class '%1!' is a system defined class. This class
15    **	   cannot be created or changed."
16    */
17    
18    create or replace procedure sp_addexeclass
19        @classname varchar(255), /* name of class */
20        @priority varchar(10), /* priority of task */
21        @timeslice int, /* time quantum of task */
22        @group varchar(255), /* group task belongs to */
23        @instanceid smallint = NULL /* instance id  for SDC */
24    
25    as
26    
27        declare @attrib_id int,
28            @action int,
29            @ts_str varchar(10),
30            @upcase_str varchar(255),
31            @instid smallint
32    
33        declare @retstat int
34        declare @nullarg char(1)
35        declare @dummy int
36        declare @status int
37        declare @gp_enabled int
38    
39    
40        /* For timeslice, use 0 if NULL is specified */
41        if (@timeslice is NULL)
42            select @timeslice = 0
43    
44        select @ts_str = str(@timeslice)
45        select @action = 1
46        select @attrib_id = 2 /* attribute is USERCLASS_DEFN */
47    
48        /*
49        **  IF we're in a transaction, disallow this since it might make recovery
50        **  impossible.
51        */
52        IF @@trancount > 0
53        BEGIN
54            /*
55            ** 17260, "Can't run %1! from within a transaction."
56            */
57            raiserror 17260, "sp_addexeclass"
58            return (1)
59        END
60        ELSE
61        BEGIN
62            /* Use TSQL mode of unchained transactions */
63            set chained off
64        END
65    
66        /* Don't do "Dirty Reads" */
67        set transaction isolation level 1
68    
69        /* convert all system defined values to upper case as needed */
70        select @upcase_str = upper(@classname)
71        IF ((@upcase_str = "EC0") OR (@upcase_str = "EC1") OR
72                (@upcase_str = "EC2") OR (@upcase_str = "EC3"))
73            select @classname = @upcase_str
74    
75        select @upcase_str = upper(@priority)
76        IF ((@upcase_str = "HIGH") OR (@upcase_str = "MEDIUM") OR
77                (@upcase_str = "LOW"))
78            select @priority = @upcase_str
79    
80        select @upcase_str = upper(@group)
81        IF (@@kernelmode = "process") AND
82            ((@upcase_str = "ANYENGINE") OR (@upcase_str = "LASTONLINE"))
83            select @group = @upcase_str
84    
85        /*
86        ** Check to see that the input params are correct
87        */
88        IF valid_name(@classname, 30) = 0
89        BEGIN
90            /*
91            ** 18249, "Name '%1!' is not valid."
92            */
93            raiserror 18249, @classname
94            return (1)
95        END
96        ELSE
97        BEGIN
98            IF ((@classname = "EC0") OR (@classname = "EC1") OR
99                    (@classname = "EC2") OR (@classname = "EC3"))
100           BEGIN
101               /*
102               ** 18522, "Execution Class '%1!' is a system defined
103               ** 	   class. This class cannot be created or changed."
104               */
105               raiserror 18522, @classname
106               return (1)
107           END
108       END
109   
110       IF ((@priority != "HIGH")
111               and (@priority != "MEDIUM")
112               and (@priority != "LOW"))
113       BEGIN
114           /*
115           ** 18252, "'%1!' value '%2!' is not valid."
116           */
117           raiserror 18252, "Priority", @priority
118           return (1)
119       END
120   
121   
122       /*
123       ** If granular permissions is not enabled then sa_role is required.
124       ** If granular permissions is enabled then the permission 
125       ** 'manage any execution class' is required.  proc_role and proc_auditperm 
126       ** will also do auditing if required. Both will also print error message 
127       ** if required.
128       */
129   
130       select @nullarg = NULL
131       execute @status = sp_aux_checkroleperm "sa_role", "manage any execution class",
132           @nullarg, @gp_enabled output
133   
134       /* For Auditing */
135       if (@gp_enabled = 0)
136       begin
137           if (proc_role("sa_role") = 0)
138               return (1)
139       end
140       else
141       begin
142           select @dummy = proc_auditperm("manage any execution class", @status)
143       end
144   
145       if (@status != 0)
146           return (1)
147       /* For SDC, use @@instanceid if NULL is specified */
148       if (@@clustermode = "shared disk cluster")
149       BEGIN
150           if (@instanceid = NULL)
151               select @instanceid = @@instanceid
152       END
153       else
154           select @instanceid = NULL
155   
156       /* 
157       ** For SDC, valid @group must be local user-defined engine group or 
158       **	ANYENGINE or LASTONLINE
159       */
160       IF ((@@kernelmode = "process") AND (@group != "ANYENGINE") AND (@group != "LASTONLINE"))
161       BEGIN
162           IF valid_name(@group, 30) = 0
163           BEGIN
164               /*
165               ** 18249, "Name '%1!' is not valid."
166               */
167               raiserror 18249, @group
168               return (1)
169           END
170           IF not exists (select * from master..sysattributes where
171                       class = 6 AND
172                       attribute = 1 AND
173                       object_type = 'EG' AND
174                       object_info3 = @instanceid AND
175                       object_cinfo = @group)
176           BEGIN
177               /*
178               ** 18253, "No specification for engine group '%1!' exists"
179               */
180               raiserror 18253, @group
181               return (1)
182           END
183       END
184       /* For THREADED kernel mode, valid @group must be a thread pool. */
185       ELSE IF ((@@kernelmode != "process"))
186       BEGIN
187           IF valid_name(@group, 30) = 0
188           BEGIN
189               /*
190               ** 18249, "Name '%1!' is not valid."
191               */
192               raiserror 18249, @group
193               return (1)
194           END
195           /*
196           **  Does the threadpool exist?
197           */
198           if not exists (select *
199                   from master.dbo.monThreadPool
200                   where ThreadPoolName = @group)
201           BEGIN
202               /* 18367, "The specified thread pool does not exist." */
203               raiserror 18367
204               return (1)
205           END
206       END
207   
208       /* 
209       ** Now hook up with Sysattributes Table...
210       ** The execution attribute values are stored in the format:
211       ** "priority value : time slice value : engine group name"
212       */
213   
214       IF exists (select * from master..sysattributes where
215                   class = 6 AND
216                   attribute = @attrib_id AND
217                   object_type = 'UC' AND
218                   object_cinfo = @classname)
219       BEGIN
220   
221           /*
222           ** For SDC,  force classname to be unique clusterwise
223           */
224           select @instid = object_info3 from master..sysattributes where
225               class = 6 AND
226               attribute = @attrib_id AND
227               object_type = 'UC' AND
228               object_cinfo = @classname
229   
230           if ((@instid != NULL) AND
231                   (@instid != @instanceid))
232           BEGIN
233               /*
234               ** RESOLVE: change error to new message:
235               ** "Class '%1!' is bound to instance '%2!'."
236               */
237               raiserror 19659, @classname, @instid
238               return (1)
239           END
240   
241   
242           update master..sysattributes
243           set char_value =
244               (@priority + ":" + @ts_str + ":" + @group),
245               object_info3 = @instanceid
246           where class = 6 AND
247               attribute = @attrib_id AND
248               object_type = 'UC' AND
249               object_cinfo = @classname
250   
251           /*
252           ** If there are any objects bound to this class, then change 
253           ** the execution attributes of those objects based on the new class
254           ** attributes. 
255           ** For stored procs, this is an attr_change operation.
256           ** for others its an attr_add. Since bindings may
257           ** exist in any database for sp, we let attrib_notify
258           ** search in all databases for bindings simply given the object_type
259           ** (PR) and class name and do the necessary update.
260           */
261           IF attrib_notify(6, 0, 'PR', NULL,
262                   NULL, NULL, @instanceid, NULL, NULL,
263                   @classname, NULL, NULL, "", 2) = 0
264           BEGIN
265               /*
266               ** 18254, "Failed to update attributes
267               ** of objects assigned to class '%1!'.
268               ** Check server errorlog for any 
269               ** additional information."
270               */
271               raiserror 18254, @classname
272               return (1)
273           END
274   
275           /*
276           ** for logins and applications, we will look only
277           ** in master. Update the execution attributes of these objects
278           ** that are bound (@attr = 0) to this class.
279           */
280   
281           DECLARE object_info cursor for
282           select attribute, object_type, object, object_info1, object_cinfo,
283               char_value
284           from master..sysattributes where (class = 6)
285   
286           DECLARE @attr smallint,
287               @obj_t varchar(2),
288               @obj int,
289               @objinfo1 int,
290               @objcinfo varchar(30),
291               @class varchar(30)
292   
293           OPEN object_info
294           FETCH object_info into @attr, @obj_t, @obj, @objinfo1, @objcinfo, @class
295           WHILE (@@sqlstatus != 2)
296           BEGIN
297               IF ((@class = @classname) AND (@attr = 0) AND
298                       ((@obj_t = 'LG') OR (@obj_t = 'AP')))
299               BEGIN
300                   IF attrib_notify(6, @attr, @obj_t, @obj, @objinfo1,
301                           NULL, @instanceid, @objcinfo, NULL,
302                           @classname, NULL, NULL, "", 1) = 0
303                   BEGIN
304                       CLOSE object_info
305                       deallocate cursor object_info
306   
307                       /*
308                       ** 18254, "Failed to update attributes
309                       ** of objects assigned to class '%1!'.
310                       ** Check server errorlog for any 
311                       ** additional information."
312                       */
313                       raiserror 18254, @classname
314                       return (1)
315                   END
316               END
317   
318               IF ((@class = @classname) AND (@attr = 0) AND (@obj_t = 'DF'))
319               BEGIN
320                   IF attrib_notify(6, @attr, @obj_t, NULL,
321                           NULL, NULL, @instanceid, NULL, NULL,
322                           @classname, NULL, NULL, "", 1) = 0
323                   BEGIN
324                       CLOSE object_info
325                       deallocate cursor object_info
326   
327                       /*
328                       ** 18254, "Failed to update attributes
329                       ** of objects assigned to class '%1!'.
330                       ** Check server errorlog for any
331                       ** additional information."
332                       */
333                       raiserror 18254, @classname
334                       return (1)
335                   END
336               END
337   
338               /* Fetch the next row from sysattributes table */
339               FETCH object_info into @attr, @obj_t, @obj, @objinfo1,
340                   @objcinfo, @class
341           END
342   
343           CLOSE object_info
344           deallocate cursor object_info
345       END
346       ELSE
347       BEGIN
348           insert master..sysattributes
349           (class, attribute, object_type, object_info3, object_cinfo, char_value)
350           values (6, @attrib_id, 'UC', @instanceid, @classname,
351               (@priority + ":" + @ts_str + ":" + @group))
352       END
353   
354       return (0)
355   


exec sp_procxmode 'sp_addexeclass', 'AnyMode'
go

Grant Execute on sp_addexeclass to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 263
 MEST 4 Empty String will be replaced by Single Space 302
 MEST 4 Empty String will be replaced by Single Space 322
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch @priority: varchar(10) = varchar(255) 78
 MTYP 4 Assignment type mismatch @instanceid: smallint = int 151
 MTYP 4 Assignment type mismatch @instid: smallint = int 224
 MTYP 4 Assignment type mismatch attribute: smallint = int 350
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 171
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 172
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 174
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 215
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 216
 QTYP 4 Comparison type mismatch smallint = int 216
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 225
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 226
 QTYP 4 Comparison type mismatch smallint = int 226
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 246
 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 284
 TNOI 4 Table with no index master..monThreadPool master..monThreadPool
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause object_info 282
 MGTP 3 Grant to public master..monThreadPool  
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_addexeclass  
 MNAC 3 Not using ANSI 'is null' 150
 MNAC 3 Not using ANSI 'is null' 230
 MNER 3 No Error Check should check @@error after update 242
 MNER 3 No Error Check should check @@error after insert 348
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 193
 MUCO 3 Useless Code Useless Brackets 204
 MUCO 3 Useless Code Useless Brackets 230
 MUCO 3 Useless Code Useless Brackets 238
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 284
 MUCO 3 Useless Code Useless Brackets 295
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Brackets 314
 MUCO 3 Useless Code Useless Brackets 318
 MUCO 3 Useless Code Useless Brackets 334
 MUCO 3 Useless Code Useless Brackets 354
 MUOT 3 Updates outside transaction 348
 MUPK 3 Update column which is part of a PK or unique index object_info3 243
 QAFM 3 Var Assignment from potentially many rows 224
 QAPT 3 Access to Proxy Table master..monThreadPool 199
 QISO 3 Set isolation level 67
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 349
 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_info3, object_cinfo, attribute, class}
171
 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_cinfo, attribute, class}
215
 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_cinfo, attribute, class}
225
 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_cinfo, attribute, class}
246
 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: {class}
284
 VNRD 3 Variable is not read @action 45
 VNRD 3 Variable is not read @dummy 142
 VUNU 3 Variable is not used @retstat 33
 CUPD 2 Updatable Cursor Marker (updatable by default) 282
 MSUB 2 Subquery Marker 170
 MSUB 2 Subquery Marker 198
 MSUB 2 Subquery Marker 214
 MTR1 2 Metrics: Comments Ratio Comments: 39% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 46 = 58dec - 14exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 165 18

DATA PROPAGATION detailed
ColumnWritten To
@classnamesysattributes.object_cinfo   sp_optgoal_rset_002.name sp_passwordpolicy_rset_001.value sp_rjs_retrieve_rset_001.js_server
@groupsysattributes.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
@instanceidsysattributes.object_info3   °.char_value   sysattributes.object_info3   °.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
@prioritysysattributes.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
@timeslicesysattributes.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
reads table master..monThreadPool (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
read_writes table master..sysattributes (1)