Database | Proc | Application | Created | Links |
sybsystemprocs | sp_multdb_dropgroup | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** Messages for "sp_multdb_dropgroup" 5 ** 6 ** 17260, "Can't run %1! from within a transaction." 7 ** 17240, "'%1!' is not a valid name." 8 ** 18943, "Can not drop '%1!' group." 9 ** 18944, "Internal Error: failed to drop group." 10 ** 18947, "Group '%1!' does not exist." 11 ** 18608, "%1!: Delete row from master.dbo.sysattributes failed. 12 ** Command aborted." 13 */ 14 15 16 /* 17 ** Procedure sp_multdb_dropgroup 18 ** 19 ** This procedure drops a group from SYSATTRIBUTES 20 ** for the MULTEMPDB_CLASS (class 16) 21 ** 22 */ 23 create procedure sp_multdb_dropgroup 24 @tdbgroup varchar(255) /* temp db group */ 25 as 26 27 declare @class_id smallint, /* class in SYSATTRIBUTES */ 28 @attrib_id smallint, /* attribute in SYSATTRIBUTES */ 29 @object_type char(2), /* object type in SYSATTRIBUTES */ 30 @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */ 31 @int_val int, /* int_value in SYSATTRIBUTES */ 32 @group_id int, /* group id database being added to */ 33 @action int, /* action for built in */ 34 @upcase_str varchar(30), 35 @errnum int, /* error during sysattribute update*/ 36 @svrmode int, /* Indicates the SMP or SDC mode */ 37 @SMP int, /* Indicates SMP Server */ 38 @SDC int, /* Indicates SDC Server */ 39 @action_code int, /* Indication action code for the op. */ 40 @MULTDB_ACTION_DO int, /* Action code that operation 41 ** completed successfully 42 */ 43 @MULTDB_ACTION_UNDO int /* Action code to indicate that 44 ** operation did not complete 45 */ 46 47 select @SMP = 0, 48 @SDC = 1 49 50 if @@clustermode != "shared disk cluster" 51 begin 52 select @svrmode = @SMP 53 end 54 else 55 begin 56 select @svrmode = @SDC 57 58 /* 59 ** These values must be kept in sync with their definitions in 60 ** multempdb.h. 61 */ 62 select @MULTDB_ACTION_DO = 2 63 select @MULTDB_ACTION_UNDO = 3 64 end 65 66 /* 67 ** If we're in a transaction, disallow this since it might make recovery 68 ** impossible. 69 */ 70 if @@trancount > 0 71 begin 72 raiserror 17260, "sp_multdb_dropgroup" 73 return (1) 74 end 75 else 76 begin 77 /* Use TSQL mode of unchained transactions */ 78 set chained off 79 end 80 81 /* Dont do "Dirty Reads" */ 82 set transaction isolation level 1 83 84 select @class_id = 16 /* class is MULTEMPDB_CLASS */ 85 86 /* 87 ** We don't allow dropping the default group. 88 */ 89 if (@tdbgroup = "default") 90 begin 91 raiserror 18943, @tdbgroup 92 return (1) 93 end 94 95 /* 96 ** Make sure group name is valid. 97 */ 98 if (@tdbgroup != "default") 99 begin 100 if valid_name(@tdbgroup) = 0 101 begin 102 raiserror 17240, @tdbgroup 103 return (1) 104 end 105 end 106 107 /* 108 ** Check if group exists. 109 */ 110 select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */ 111 select @object_type = 'GR' /* Object type if 'GR' for temp. db group */ 112 113 select @group_id = (select int_value from master..sysattributes 114 where class = @class_id 115 AND attribute = @attrib_id 116 AND object_type = @object_type 117 AND object_cinfo = @tdbgroup) 118 119 /* 120 ** Group does not exist. Report error. 121 */ 122 if @group_id is NULL 123 begin 124 raiserror 18947, @tdbgroup 125 return (1) 126 end 127 128 select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */ 129 130 select @errnum = 0 131 132 begin tran drop_tdbgrp 133 134 /* 135 ** Send the notification to indicate the beginning of the transaction 136 ** to allow cluster synchronization mechanism to be deployed. 137 */ 138 if (@svrmode = @SDC) 139 begin 140 select @action = 5 /* ATTR_BEGIN */ 141 select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */ 142 if (attrib_notify(@class_id, @attrib_id, @object_type, 143 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 144 NULL, "", @action)) = 0 145 begin 146 raiserror 18944 147 goto error_exit 148 end 149 end 150 151 /* 152 ** Drop all bindings of type 'AP' and 'LG' (application 153 ** and login bindings) where the binding is to the group being dropped 154 ** 155 ** The int_value of 1 indicates that the name stored in char_value is a 156 ** group name. 157 */ 158 select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */ 159 160 delete from master..sysattributes 161 where class = @class_id 162 AND attribute = @attrib_id 163 AND (object_type = 'LG' OR object_type = 'AP') 164 AND char_value = @tdbgroup 165 AND int_value = 1 166 167 select @errnum = @@error 168 if (@errnum != 0) 169 goto error_exit 170 171 /* 172 ** Drop all entres from the sysattributes where 173 ** databases are bound to the group that is being dropped. 174 */ 175 select @object_type = 'D ' /* Object type if 'GR' for temp. db group */ 176 177 delete from master..sysattributes 178 where class = @class_id 179 AND attribute = @attrib_id 180 AND object_type = @object_type 181 AND object = @group_id 182 183 select @errnum = @@error 184 if (@errnum != 0) 185 goto error_exit 186 187 /* 188 ** Drop the group entry 189 */ 190 select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */ 191 select @object_type = 'GR' /* Object type if 'GR' for temp. db group */ 192 193 delete from master..sysattributes 194 where class = @class_id 195 AND attribute = @attrib_id 196 AND object_type = @object_type 197 AND object_cinfo = @tdbgroup 198 199 select @errnum = @@error 200 if (@errnum != 0) 201 goto error_exit 202 203 /* 204 ** Notify the deletion of the new group. 205 ** If this return failure, then it's an internal error. 206 ** There is no reason to undo the work we have just done. 207 */ 208 select @action = 3 /* ATTR_DROP - Drop a group */ 209 if (attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL, 210 NULL, @tdbgroup, @group_id, NULL, NULL, NULL, "", 211 @action)) = 0 212 begin 213 raiserror 18944 214 goto error_exit 215 end 216 217 commit tran drop_tdbgrp 218 /* 219 ** Send notification to communicate that transaction is committed and 220 ** in-memory changes be made final. 221 */ 222 if (@svrmode = @SDC) 223 begin 224 select @action = 6 /* ATTR_END */ 225 select @action_code = @MULTDB_ACTION_DO 226 select attrib_notify(@class_id, @attrib_id, @object_type, 227 NULL, NULL, NULL, NULL, NULL, @action_code, NULL, NULL, 228 NULL, "", @action) 229 end 230 return (0) 231 232 error_exit: 233 if @errnum != 0 234 raiserror 18608, 'sp_multdb_dropgroup' 235 /* 236 ** In SDC, the in-memory changes are undone as part of the 237 ** abort transaction processing. So, there is no need to send 238 ** end notification with @MULTDB_ACTION_UNDO action. 239 */ 240 rollback tran drop_tdbgrp 241 return (1) 242
RESULT SETS | |
sp_multdb_dropgroup_rset_001 |
DEFECTS | |
MEST 4 Empty String will be replaced by Single Space | 144 |
MEST 4 Empty String will be replaced by Single Space | 210 |
MEST 4 Empty String will be replaced by Single Space | 228 |
MINU 4 Unique Index with nullable columns master..sysattributes | master..sysattributes |
MGTP 3 Grant to public master..sysattributes | |
MUCO 3 Useless Code Useless Brackets | 73 |
MUCO 3 Useless Code Useless Brackets | 89 |
MUCO 3 Useless Code Useless Brackets | 92 |
MUCO 3 Useless Code Useless Brackets | 98 |
MUCO 3 Useless Code Useless Brackets | 103 |
MUCO 3 Useless Code Useless Brackets | 125 |
MUCO 3 Useless Code Useless Brackets | 138 |
MUCO 3 Useless Code Useless Brackets | 142 |
MUCO 3 Useless Code Useless Brackets | 168 |
MUCO 3 Useless Code Useless Brackets | 184 |
MUCO 3 Useless Code Useless Brackets | 200 |
MUCO 3 Useless Code Useless Brackets | 209 |
MUCO 3 Useless Code Useless Brackets | 222 |
MUCO 3 Useless Code Useless Brackets | 230 |
MUCO 3 Useless Code Useless Brackets | 241 |
QCRS 3 Conditional Result Set | 226 |
QISO 3 Set isolation level | 82 |
QNAM 3 Select expression has no name attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL, NULL, NULL, @action_code, NULL,... | 226 |
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} | 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: {attribute, object_type, class} | 161 |
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, attribute, class} | 178 |
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 |
VNRD 3 Variable is not read @MULTDB_ACTION_UNDO | 63 |
VUNU 3 Variable is not used @object_cinfo | 30 |
VUNU 3 Variable is not used @int_val | 31 |
VUNU 3 Variable is not used @upcase_str | 34 |
MRST 2 Result Set Marker | 226 |
MSUB 2 Subquery Marker | 113 |
MTR1 2 Metrics: Comments Ratio Comments: 44% | 23 |
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 17dec - 5exi + 2 | 23 |
MTR3 2 Metrics: Query Complexity Complexity: 103 | 23 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..sysattributes (1) CALLERS called by proc sybsystemprocs..sp_tempdb |