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


exec sp_procxmode 'sp_dropengine', 'AnyMode'
go

Grant Execute on sp_dropengine to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 182
 MEST 4 Empty String will be replaced by Single Space 345
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MTYP 4 Assignment type mismatch @instanceid: smallint = int 134
 MTYP 4 Assignment type mismatch @scope: varchar(32) = int 290
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 146
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 147
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 150
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 221
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 222
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 225
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 236
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 237
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 239
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 297
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 298
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 309
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 336
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 337
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 340
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 356
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 357
 QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint 360
 TNOI 4 Table with no index master..sysprocesses master..sysprocesses
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause engine_info 234
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause engine_info 295
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysprocesses  
 MGTP 3 Grant to public sybsystemprocs..sp_dropengine  
 MNAC 3 Not using ANSI 'is null' 122
 MNAC 3 Not using ANSI 'is null' 133
 MNAC 3 Not using ANSI 'is null' 307
 MNER 3 No Error Check should check @@error after update 219
 MNER 3 No Error Check should check @@error after delete 335
 MNER 3 No Error Check should check @@error after update 354
 MNER 3 No Error Check should check @@error after insert 362
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 189
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 236
 MUCO 3 Useless Code Useless Brackets 249
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 277
 MUCO 3 Useless Code Useless Brackets 288
 MUCO 3 Useless Code Useless Brackets 297
 MUCO 3 Useless Code Useless Brackets 305
 MUCO 3 Useless Code Useless Brackets 307
 MUCO 3 Useless Code Useless Brackets 321
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 344
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 367
 MUCO 3 Useless Code Useless Brackets 369
 MUOT 3 Updates outside transaction 362
 QISO 3 Set isolation level 70
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 363
 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}
146
 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}
221
 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}
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_cinfo, attribute, class}
297
 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}
336
 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}
356
 VNRD 3 Variable is not read @dummy 112
 VUNU 3 Variable is not used @retstat 34
 CUPD 2 Updatable Cursor Marker (updatable by default) 234
 CUPD 2 Updatable Cursor Marker (updatable by default) 295
 MSUB 2 Subquery Marker 144
 MSUB 2 Subquery Marker 308
 MTR1 2 Metrics: Comments Ratio Comments: 39% 20
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 35 = 43dec - 10exi + 2 20
 MTR3 2 Metrics: Query Complexity Complexity: 171 20

DATA PROPAGATION detailed
ColumnWritten To
@engine_groupsysattributes.object_cinfo   sp_optgoal_rset_002.name sp_passwordpolicy_rset_001.value sp_rjs_retrieve_rset_001.js_server
@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

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysprocesses (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
read_writes table master..sysattributes (1)