| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_multdb_removefromgroup | | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** Messages for "sp_multdb_removefromgroup" 5 ** 6 ** 17260, "Can't run %1! from within a transaction." 7 ** 17240, "'%1!' is not a valid name." 8 ** 18946, "Either the database '%1!' does not exist or is not a user 9 ** created temporary database." 10 ** 18947, "Group '%1!' does not exist." 11 ** 18950, "Internal Error: failed to remove database from group." 12 ** 18975, "Database '%1!' is not a member of the '%2!' group." 13 ** 18608, "%1!: Delete row from master.dbo.sysattributes failed. 14 ** Command aborted." 15 ** 19580, "Either the database '%1!' does not exist or is not a 16 ** local user temporary database." 17 */ 18 19 20 /* 21 ** Procedure sp_multdb_removefromgroup 22 ** 23 ** This procedure removes a database from a group in SYSATTRIBUTES 24 ** for the MULTEMPDB_CLASS (class 16) 25 ** 26 */ 27 create procedure sp_multdb_removefromgroup 28 @tdb varchar(255), /* temporary database to be bound */ 29 @tdbgroup varchar(255) /* temp db group */ 30 as 31 32 declare @class_id smallint, /* class in SYSATTRIBUTES */ 33 @attrib_id smallint, /* attribute in SYSATTRIBUTES */ 34 @object_type char(2), /* object type in SYSATTRIBUTES */ 35 @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */ 36 @int_val int, /* int_value in SYSATTRIBUTES */ 37 @group_id int, /* group id database being added to */ 38 @action int, /* action for built in */ 39 @upcase_str varchar(30), 40 @instanceid int, 41 @bindabletdb_stat int, /* dbstatus for database can be added 42 ** to a tdb group */ 43 @svrmode int, /* Indicates the SMP or SDC mode */ 44 @SMP int, /* Indicates SMP Server */ 45 @SDC int, /* Indicates SDC Server */ 46 @action_code int, /* Indicates action code for the op. */ 47 @MULTDB_ACTION_DO int, /* Action code that operation 48 ** completed successfully 49 */ 50 @MULTDB_ACTION_UNDO int /* Action code to indicate that 51 ** operation did not complete 52 */ 53 54 select @SMP = 0, 55 @SDC = 1 56 57 if @@clustermode != "shared disk cluster" 58 begin 59 select @svrmode = @SMP 60 end 61 else 62 begin 63 select @svrmode = @SDC 64 65 /* 66 ** These values must be kept in sync with their definitions in 67 ** multempdb.h. 68 */ 69 select @MULTDB_ACTION_DO = 2 70 select @MULTDB_ACTION_UNDO = 3 71 end 72 73 /* 74 ** if we're in a transaction, disallow this since it might make recovery 75 ** impossible. 76 */ 77 if @@trancount > 0 78 begin 79 raiserror 17260, "sp_multdb_removefromgroup" 80 return (1) 81 end 82 else 83 begin 84 /* Use TSQL mode of unchained transactions */ 85 set chained off 86 end 87 88 /* Dont do "Dirty Reads" */ 89 set transaction isolation level 1 90 91 select @class_id = 16 /* class is MULTEMPDB_CLASS */ 92 select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */ 93 select @object_type = 'D ' /* Object type if 'D ' for temp. db */ 94 select @action = 3 /* Drop Binding */ 95 96 /* 97 ** Make sure group name is valid. 98 */ 99 if (@tdbgroup != "default") 100 begin 101 if valid_name(@tdbgroup) = 0 102 begin 103 raiserror 17240, @tdbgroup 104 return (1) 105 end 106 end 107 108 /* 109 ** tempdb is a special case since its binding to the default group 110 ** is implicit and is not stored in sysattributes. So we cannot rely 111 ** on the lookup in sysattributes as we do for other databases to determine 112 ** if such a binding exists or not. 113 ** In addition, we want to catch the type of database that can not be 114 ** specificly bind to a group and give proper msg here. Even though tempdb 115 ** in SMP is implicitly part of the default group, it can not be specificly 116 ** bind or unbind from any group. 117 */ 118 if (@svrmode = @SMP) 119 select @bindabletdb_stat = number 120 from master.dbo.spt_values 121 where type = "D3" and name = "user created temp db" 122 else 123 select @bindabletdb_stat = number 124 from master.dbo.spt_values 125 where type = "D3" and name = "local user temp db" 126 127 if not exists (select * 128 from master..sysdatabases 129 where name = @tdb 130 AND (status3 & @bindabletdb_stat) = @bindabletdb_stat) 131 begin 132 if (@svrmode = @SMP) 133 raiserror 18946, @tdb 134 else 135 raiserror 19580, @tdb 136 return (1) 137 end 138 139 140 /* 141 ** Check if group exists and get group id 142 */ 143 select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */ 144 select @object_type = 'GR' /* Object type if 'GR' for temp. db group */ 145 146 select @group_id = (select int_value from master..sysattributes 147 where class = @class_id 148 AND attribute = @attrib_id 149 AND object_type = @object_type 150 AND object_cinfo = @tdbgroup) 151 152 /* 153 ** Group not found 154 */ 155 if @group_id is NULL 156 begin 157 raiserror 18947, @tdbgroup 158 return (1) 159 end 160 161 select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */ 162 select @object_type = 'D ' /* Object type if 'D ' for temp db */ 163 164 begin tran unbind_tdbtogrp 165 166 /* 167 ** Send the notification to indicate the beginning of the transaction 168 ** to allow cluster synchronization mechanisms to be deployed. 169 */ 170 if (@svrmode = @SDC) 171 begin 172 select @action = 5 /* ATTR_BEGIN */ 173 if (attrib_notify(@class_id, @attrib_id, @object_type, 174 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 175 NULL, "", @action)) = 0 176 begin 177 raiserror 18950 178 goto error_exit 179 end 180 end 181 182 /* 183 ** Delete the binding from sysattributes. 184 */ 185 if exists (select * from master..sysattributes 186 where class = @class_id 187 AND attribute = @attrib_id 188 AND object_type = @object_type 189 AND object_cinfo = @tdb 190 AND object = @group_id) 191 begin 192 delete from master..sysattributes 193 where class = @class_id 194 AND attribute = @attrib_id 195 AND object_type = @object_type 196 AND object_cinfo = @tdb 197 AND object = @group_id 198 199 if @@error != 0 200 begin 201 raiserror 18608, 'sp_multdb_removefromgroup' 202 goto error_exit 203 end 204 end 205 else 206 begin 207 raiserror 18975, @tdb, @tdbgroup 208 goto error_exit 209 end 210 211 /* 212 ** Notify the unbinding of the database 213 ** If this returns failure, then abort the transaction. 214 ** 215 ** Note in SDC, object_info2 will hold the instance id of the tempdb; 216 ** while in SMP, this field is filled with null. 217 */ 218 select @action = 3 /* ATTR_DROP */ 219 220 if (attrib_notify(@class_id, @attrib_id, @object_type, @group_id, NULL, NULL, 221 NULL, @tdb, NULL, NULL, NULL, NULL, "", 222 @action)) = 0 223 224 begin 225 raiserror 18950 226 goto error_exit 227 end 228 229 commit tran unbind_tdbgrp 230 /* 231 ** Send notification to communicate that transaction is committed and 232 ** in-memory changes be made final. 233 */ 234 if (@svrmode = @SDC) 235 begin 236 select @action = 6 /* ATTR_END */ 237 select @action_code = @MULTDB_ACTION_DO 238 select attrib_notify(@class_id, @attrib_id, @object_type, 239 NULL, NULL, NULL, NULL, NULL, @action_code, NULL, NULL, 240 NULL, "", @action) 241 end 242 return (0) 243 244 error_exit: 245 /* 246 ** In SDC, the in-memory changes are undone as part of the 247 ** abort transaction processing. So, there is no need to send 248 ** end notification with @MULTDB_ACTION_UNDO action. 249 */ 250 rollback tran unbind_tdbtogrp 251 return (1) 252
| RESULT SETS | |
| sp_multdb_removefromgroup_rset_001 | |
| DEFECTS | |
 MEST 4 Empty String will be replaced by Single Space | 175 |
| MEST 4 Empty String will be replaced by Single Space | 221 |
| MEST 4 Empty String will be replaced by Single Space | 240 |
| MINU 4 Unique Index with nullable columns master..sysattributes | master..sysattributes |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} | 121 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} | 125 |
| TNOU 4 Table with no unique index master..spt_values | master..spt_values |
 MGTP 3 Grant to public master..spt_values | |
| MGTP 3 Grant to public master..sysattributes | |
| MGTP 3 Grant to public master..sysdatabases | |
| MUCO 3 Useless Code Useless Brackets | 80 |
| MUCO 3 Useless Code Useless Brackets | 99 |
| MUCO 3 Useless Code Useless Brackets | 104 |
| MUCO 3 Useless Code Useless Brackets | 118 |
| MUCO 3 Useless Code Useless Brackets | 132 |
| MUCO 3 Useless Code Useless Brackets | 136 |
| MUCO 3 Useless Code Useless Brackets | 158 |
| MUCO 3 Useless Code Useless Brackets | 170 |
| MUCO 3 Useless Code Useless Brackets | 173 |
| MUCO 3 Useless Code Useless Brackets | 220 |
| MUCO 3 Useless Code Useless Brackets | 234 |
| MUCO 3 Useless Code Useless Brackets | 242 |
| MUCO 3 Useless Code Useless Brackets | 251 |
| QAFM 3 Var Assignment from potentially many rows | 119 |
| QAFM 3 Var Assignment from potentially many rows | 123 |
| QCRS 3 Conditional Result Set | 238 |
| QISO 3 Set isolation level | 89 |
| QNAM 3 Select expression has no name attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL, NULL, NULL, @action_code, NULL,... | 238 |
| 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} | 147 |
| 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, object_cinfo, attribute, class} | 186 |
| 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, object_cinfo, attribute, class} | 193 |
| VNRD 3 Variable is not read @MULTDB_ACTION_UNDO | 70 |
| VUNU 3 Variable is not used @object_cinfo | 35 |
| VUNU 3 Variable is not used @int_val | 36 |
| VUNU 3 Variable is not used @upcase_str | 39 |
| VUNU 3 Variable is not used @instanceid | 40 |
 MRST 2 Result Set Marker | 238 |
| MSUB 2 Subquery Marker | 127 |
| MSUB 2 Subquery Marker | 146 |
| MSUB 2 Subquery Marker | 185 |
| MTR1 2 Metrics: Comments Ratio Comments: 46% | 27 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 21 = 24dec - 5exi + 2 | 27 |
| MTR3 2 Metrics: Query Complexity Complexity: 103 | 27 |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..spt_values (1)  reads table master..sysdatabases (1) read_writes table master..sysattributes (1) CALLERS called by proc sybsystemprocs..sp_tempdb  |