Database | Proc | Application | Created | Links |
sybsystemprocs | sp_dropengine ![]() | ![]() | 31 Aug 14 | Defects 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 **: 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: 223 ** with 10/10/30 length fields respectively in addition 224 ** to the delimitors ':' 225 */
DEFECTS | |
![]() | 150 |
![]() | 313 |
![]() | master..sysattributes |
![]() | 102 |
![]() | 258 |
![]() | 114 |
![]() | 115 |
![]() | 118 |
![]() | 189 |
![]() | 190 |
![]() | 193 |
![]() | 204 |
![]() | 205 |
![]() | 207 |
![]() | 265 |
![]() | 266 |
![]() | 277 |
![]() | 304 |
![]() | 305 |
![]() | 308 |
![]() | 324 |
![]() | 325 |
![]() | 328 |
![]() | master..sysprocesses |
![]() | 202 |
![]() | 263 |
![]() | |
![]() | |
![]() | 90 |
![]() | 101 |
![]() | 275 |
![]() | 187 |
![]() | 303 |
![]() | 322 |
![]() | 330 |
![]() | 52 |
![]() | 69 |
![]() | 77 |
![]() | 83 |
![]() | 87 |
![]() | 89 |
![]() | 98 |
![]() | 101 |
![]() | 138 |
![]() | 144 |
![]() | 157 |
![]() | 160 |
![]() | 168 |
![]() | 170 |
![]() | 181 |
![]() | 204 |
![]() | 217 |
![]() | 234 |
![]() | 245 |
![]() | 256 |
![]() | 265 |
![]() | 273 |
![]() | 275 |
![]() | 289 |
![]() | 298 |
![]() | 312 |
![]() | 321 |
![]() | 335 |
![]() | 337 |
![]() | 330 |
![]() | 65 |
![]() | 331 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_info3, object_cinfo, attribute, class} | 114 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_info3, object_cinfo, attribute, class} | 189 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_info3, attribute, class} | 204 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_cinfo, attribute, class} | 265 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_info3, object_cinfo, attribute, class} | 304 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_info3, object_cinfo, attribute, class} | 324 |
![]() | 34 |
![]() | 202 |
![]() | 263 |
![]() | 112 |
![]() | 276 |
![]() | 20 |
![]() | 20 |
![]() | 20 |
DEPENDENCIES |
PROCS AND TABLES USED reads table master..sysprocesses (1) ![]() read_writes table master..sysattributes (1) ![]() |