DatabaseProcApplicationCreatedLinks
sybsystemprocssp_dropengine  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% */
3     
4     /*
5     ** Messages for "sp_dropengine"
6     **
7     ** 17260, "Can't run %1! from within a transaction."
8     ** 18251, "Cannot modify pre-defined engine groups."
9     ** 18248, "Validation of Engine Group modification failed. Check server
10    **	   errorlog for any additional information."
11    ** 18268, "Internal Error: Wrong engine list format in sysattributes." 
12    ** 18269, "Cannot drop engine group used in class definition."
13    ** 18270, "Failed to drop engine '%1!' from engine group '%2!'. Check server
14    **	   errorlog for any additional information. List of engines in
15    **	   engine group unchanged."
16    ** 18271, "Cannot drop engine group bound to a SQL Server process."
17    ** 19703, "Instance id '%1!' is not valid under instance system view."
18    */
19    
20    create procedure sp_dropengine
21        @engine_number int, /* engine number of engine */
22        @engine_group varchar(255), /* engine group name */
23        @instanceid smallint = NULL /* instance id for SDC */
24    as
25    
26        declare @attrib_id int,
27            @action int,
28            @engine_name varchar(5),
29            @object_type varchar(2),
30            @engine_list varchar(255),
31            @upcase_str varchar(255),
32            @scope varchar(32) /* SDC command execution scope */
33    
34        declare @retstat int
35    
36        IF @@kernelmode = 'threaded'
37            return 1
38    
39        /*
40        **  IF we're in a transaction, disallow this since it might make recovery
41        **  impossible.
42        **  This check must be done before any select statement. Otherwise,
43        **  the select statement might start a transaction if the session is
44        **  running in chained mode.
45        */
46        IF @@trancount > 0
47        BEGIN
48            /*
49            ** 17260, "Can't run %1! from within a transaction."
50            */
51            raiserror 17260, "sp_dropengine"
52            return (1)
53        END
54        ELSE
55        BEGIN
56            /* Use TSQL mode of unchained transactions */
57            set chained off
58        END
59    
60        select @action = 3 /* drop operation */
61        select @attrib_id = 1 /* attribute is ENGINE_GROUP */
62        select @object_type = 'EG'
63    
64        /* Don't do "Dirty Reads" */
65        set transaction isolation level 1
66    
67        /* first convert any system defined engine group name to upper case */
68        select @upcase_str = upper(@engine_group)
69        IF ((@upcase_str = "ANYENGINE") OR (@upcase_str = "LASTONLINE"))
70            select @engine_group = @upcase_str
71    
72        /*
73        ** Check to see that the input params are correct and then hook up with
74        ** Sysattributes table to enter data.
75        */
76    
77        IF ((@engine_group = "ANYENGINE") OR (@engine_group = "LASTONLINE"))
78        BEGIN
79            /*
80            ** 18251, "Can't modify pre-defined engine groups."
81            */
82            raiserror 18251
83            return (1)
84        END
85    
86        /* For SDC, use @@instanceid if NULL is specified */
87        if (@@clustermode = "shared disk cluster")
88        BEGIN
89            if ((@@system_view = "instance") AND
90                    (@instanceid != NULL) AND
91                    (@instanceid != @@instanceid))
92            BEGIN
93                /*
94                ** 19703, "Instance id '%1!' is not valid under instance
95                **         system view."
96                */
97                raiserror 19703, @instanceid
98                return (1)
99            END
100   
101           if (@instanceid = NULL)
102               select @instanceid = @@instanceid
103       END
104       else
105           select @instanceid = NULL
106   
107       /*
108       ** Select the engine list for the given engine group name. Then parse
109       ** the engine list to grab the engine we are looking for and then
110       ** drop the engine from the list.
111       */
112       select @engine_list = (select char_value
113               from master..sysattributes where
114                   class = 6 AND
115                   attribute = 1 AND
116                   object_type = 'EG' AND
117                   object_cinfo = @engine_group AND
118                   object_info3 = @instanceid)
119   
120       /* local vars needed to parse the engine list */
121       DECLARE @length int,
122           @new_list varchar(255),
123           @term int,
124           @curlen int,
125           @tengine_name varchar(5), /* trimmed engine name */
126           @engine int
127   
128       /*
129       ** The engine list for a given group is kept in the format -
130       ** engine # : engine # : engine # : ...
131       ** Each element is of fixed length. After selecting an engine name,
132       ** trim it to delete spaces and convert the engine name to an integer.
133       */
134       select @length = char_length(@engine_list)
135       select @term = charindex(":", @engine_list)
136       select @curlen = 0
137   
138       IF ((@engine_list is not NULL) AND (@term = 0))
139       BEGIN
140           /*
141           ** 18268, "Internal Error: Wrong engine list format in sysattributes" 
142           */
143           raiserror 18268
144           return (1)
145       END
146       ELSE
147       BEGIN
148           IF attrib_valid(6, @attrib_id, @object_type, NULL, NULL, NULL,
149                   @instanceid, @engine_group, @engine_number, NULL,
150                   NULL, NULL, "", @action) = 0
151           BEGIN
152               /*
153               ** 18248, "Validation of Engine Group modification failed.Check
154               **	   server errorlog for any additional information."
155               */
156               raiserror 18248
157               return (1)
158           END
159   
160           WHILE (@curlen < @length)
161           BEGIN
162               select @engine_name = substring(@engine_list, (@curlen + 1),
163                       (@term - 1))
164               select @tengine_name = ltrim(@engine_name)
165               select @engine = convert(integer, @tengine_name)
166   
167               /* Build the engine list, ignoring the engine to be dropped */
168               IF (@engine != @engine_number)
169               BEGIN
170                   IF (@curlen = 0)
171                       select @new_list = @engine_name + ':'
172                   ELSE
173                       select @new_list = @new_list + @engine_name
174                           + ':'
175               END
176   
177               select @curlen = @curlen + @term
178           END
179       END
180   
181       IF (char_length(@new_list) > 0)
182   
183           /*
184           ** This is not the last engine in the group, so simply
185           ** update the engine list
186           */
187           update master..sysattributes
188           set char_value = @new_list
189           where class = 6 AND
190               attribute = 1 AND
191               object_type = 'EG' AND
192               object_cinfo = @engine_group AND
193               object_info3 = @instanceid
194       ELSE
195       BEGIN
196           /*
197           ** Check that no user defined class is using this engine group 
198           ** that is about to be deleted.
199           */
200   
201           DECLARE engine_info cursor for
202           (select char_value from
203               master..sysattributes where
204               (class = 6 AND
205                   attribute = 2 AND
206                   object_type = 'UC' AND
207                   object_info3 = @instanceid))
208   
209           DECLARE @class_str varchar(255),
210               @groupname varchar(255),
211               @len int,
212               @spid int
213   
214           OPEN engine_info
215   
216           FETCH engine_info into @class_str
217           WHILE (@@sqlstatus != 2)
218           BEGIN
219               /*
220               ** The list of attributes for a given execution
221               ** class is in the format -
222               **  :  : 
223               ** with 10/10/30 length fields respectively in addition
224               ** to the delimitors ':'
225               */
226               select @len = char_length(@class_str)
227               select @term = charindex(":", @class_str)
228   
229               /* select engine group field from class string */
230               IF @term != 0
231                   select @groupname =
232                       substring(@class_str, (@term + 10 + 2), (@len - @term - 11))
233   
234               IF (@groupname = @engine_group)
235               BEGIN
236                   /* Engine group is used by a class */
237                   CLOSE engine_info
238                   deallocate cursor engine_info
239   
240                   /*
241                   ** 18269, "Cannot drop engine group used in class 
242                   **	   definition."
243                   */
244                   raiserror 18269
245                   return (1)
246               END
247               FETCH engine_info into @class_str
248           END
249   
250           CLOSE engine_info
251           deallocate cursor engine_info
252   
253           /* Check that no sessions are using this engine group */
254   
255           /* SDC only, check clusterwide sysprocesses */
256           if (@@clustermode = "shared disk cluster")
257           BEGIN
258               select @scope = @@system_view
259               set system_view cluster
260           END
261   
262           DECLARE engine_info cursor for
263           (select char_value, object_info1 from
264               master..sysattributes where
265               (class = 6 AND
266                   attribute = 3 AND
267                   object_cinfo = 'enginegroup' AND
268                   object_type = 'PS'))
269   
270           OPEN engine_info
271   
272           FETCH engine_info into @groupname, @spid
273           WHILE (@@sqlstatus != 2)
274           BEGIN
275               IF ((@spid != NULL) AND
276                       exists (select * from master..sysprocesses
277                           where spid = @spid) AND
278                       (@groupname = @engine_group))
279               BEGIN
280                   /* Engine group is used by a session */
281                   CLOSE engine_info
282                   deallocate cursor engine_info
283   
284                   /*
285                   ** 18271, "Cannot drop engine group bound to a 
286                   **	   SQL Server process"
287                   */
288                   raiserror 18271
289                   return (1)
290               END
291               FETCH engine_info into @groupname, @spid
292           END
293   
294           CLOSE engine_info
295           deallocate cursor engine_info
296   
297           /* SDC only: restore previous system_view scope */
298           if (@@clustermode = "shared disk cluster")
299           BEGIN
300               set system_view @scope
301           END
302   
303           delete master..sysattributes
304           where class = 6 AND
305               attribute = 1 AND
306               object_type = 'EG' AND
307               object_cinfo = @engine_group AND
308               object_info3 = @instanceid
309   
310       END
311   
312       if (attrib_notify(6, @attrib_id, @object_type, NULL, @engine_number, NULL,
313                   @instanceid, @engine_group, NULL, NULL, NULL, NULL, "",
314                   @action) = 0)
315       BEGIN
316           /*
317           ** 18270, "Failed to drop engine '%1!' from engine group '%2!'.
318           **	   Check server errorlog for any additional information.
319           **	   List of engines in engine group unchanged."
320           */
321           IF (char_length(@new_list) > 0)
322               update master..sysattributes
323               set char_value = @engine_list
324               where class = 6 AND
325                   attribute = 1 AND
326                   object_type = 'EG' AND
327                   object_cinfo = @engine_group AND
328                   object_info3 = @instanceid
329           ELSE
330               insert master..sysattributes
331               (class, attribute, object_type, object_info3, object_cinfo, char_value)
332               values (6, 1, 'EG', @instanceid, @engine_group, @engine_list)
333   
334           raiserror 18270, @engine_number, @engine_group
335           return (1)
336       END
337       return (0)
338   

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 150
 MEST 4 Empty String will be replaced by Single Space 313
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch @instanceid: smallint = int 102
 MTYP 4 Assignment type mismatch @scope: varchar(32) = int 258
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 114
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 115
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 118
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 189
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 190
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 193
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 204
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 205
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 207
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 265
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 266
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 277
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 304
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 305
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 308
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 324
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 325
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 328
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause engine_info 202
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause engine_info 263
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysprocesses  
 MNAC 3 Not using ANSI 'is null' 90
 MNAC 3 Not using ANSI 'is null' 101
 MNAC 3 Not using ANSI 'is null' 275
 MNER 3 No Error Check should check @@error after update 187
 MNER 3 No Error Check should check @@error after delete 303
 MNER 3 No Error Check should check @@error after update 322
 MNER 3 No Error Check should check @@error after insert 330
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 144
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 168
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 181
 MUCO 3 Useless Code Useless Brackets 204
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 245
 MUCO 3 Useless Code Useless Brackets 256
 MUCO 3 Useless Code Useless Brackets 265
 MUCO 3 Useless Code Useless Brackets 273
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 289
 MUCO 3 Useless Code Useless Brackets 298
 MUCO 3 Useless Code Useless Brackets 312
 MUCO 3 Useless Code Useless Brackets 321
 MUCO 3 Useless Code Useless Brackets 335
 MUCO 3 Useless Code Useless Brackets 337
 MUOT 3 Updates outside transaction 330
 QISO 3 Set isolation level 65
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 331
 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}
114
 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}
189
 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, attribute, class}
204
 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}
265
 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}
304
 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}
324
 VUNU 3 Variable is not used @retstat 34
 CUPD 2 Updatable Cursor Marker (updatable by default) 202
 CUPD 2 Updatable Cursor Marker (updatable by default) 263
 MSUB 2 Subquery Marker 112
 MSUB 2 Subquery Marker 276
 MTR1 2 Metrics: Comments Ratio Comments: 39% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 32 = 39dec - 9exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 156 20

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