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

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 232
 MEST 4 Empty String will be replaced by Single Space 271
 MEST 4 Empty String will be replaced by Single Space 291
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch @priority: varchar(10) = varchar(255) 73
 MTYP 4 Assignment type mismatch @instanceid: smallint = int 120
 MTYP 4 Assignment type mismatch @instid: smallint = int 193
 MTYP 4 Assignment type mismatch attribute: smallint = int 319
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 140
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 141
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 143
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 184
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 185
 QTYP 4 Comparison type mismatch smallint = int 185
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 194
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 195
 QTYP 4 Comparison type mismatch smallint = int 195
 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 253
 TNOI 4 Table with no index master..monThreadPool master..monThreadPool
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause object_info 251
 MGTP 3 Grant to public master..monThreadPool  
 MGTP 3 Grant to public master..sysattributes  
 MNAC 3 Not using ANSI 'is null' 119
 MNAC 3 Not using ANSI 'is null' 199
 MNER 3 No Error Check should check @@error after update 211
 MNER 3 No Error Check should check @@error after insert 317
 MUCO 3 Useless Code Useless Brackets 36
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 162
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 199
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 253
 MUCO 3 Useless Code Useless Brackets 264
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 283
 MUCO 3 Useless Code Useless Brackets 287
 MUCO 3 Useless Code Useless Brackets 303
 MUCO 3 Useless Code Useless Brackets 323
 MUOT 3 Updates outside transaction 317
 MUPK 3 Update column which is part of a PK or unique index object_info3 212
 QAFM 3 Var Assignment from potentially many rows 193
 QAPT 3 Access to Proxy Table master..monThreadPool 168
 QISO 3 Set isolation level 62
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 318
 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}
140
 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}
184
 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}
194
 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: {class}
253
 VNRD 3 Variable is not read @action 40
 VUNU 3 Variable is not used @retstat 33
 CUPD 2 Updatable Cursor Marker (updatable by default) 251
 MSUB 2 Subquery Marker 139
 MSUB 2 Subquery Marker 167
 MSUB 2 Subquery Marker 183
 MTR1 2 Metrics: Comments Ratio Comments: 39% 18
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 43 = 54dec - 13exi + 2 18
 MTR3 2 Metrics: Query Complexity Complexity: 150 18

DEPENDENCIES
PROCS AND TABLES USED
reads table master..monThreadPool (1)  
read_writes table master..sysattributes (1)