| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_multdb_addtogroup | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** Messages for "sp_multdb_addtogroup" 4 ** 5 ** 17260, "Can't run %1! from within a transaction." 6 ** 17240, "'%1!' is not a valid name." 7 ** 18610, "%1!: Insert row to master.dbo.sysattributes failed. Command aborted." 8 ** 18945, "'tempdb' is implicitly a member of 'default' group and 9 ** can not be part of another group." 10 ** 18946, "Either the database '%1!' does not exist or is not a 11 ** user created temporary database." 12 ** 18947, "Group '%1!' does not exist." 13 ** 18948, "Database '%1!' is already a group member." 14 ** 18949, "Internal Error: failed to add database to group." 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_addtogroup 22 ** 23 ** This procedure adds a temporary database to a temporary database group 24 ** 25 */ 26 create procedure sp_multdb_addtogroup 27 @tdb varchar(255), /* temporary database to be bound */ 28 @tdbgroup varchar(255) /* temp db group */ 29 as 30 31 declare @class_id smallint, /* class in SYSATTRIBUTES */ 32 @attrib_id smallint, /* attribute in SYSATTRIBUTES */ 33 @object_type char(2), /* object type in SYSATTRIBUTES */ 34 @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */ 35 @int_val int, /* int_value in SYSATTRIBUTES */ 36 @group_id int, /* group id database being added to */ 37 @action int, /* action for build in */ 38 @upcase_str varchar(30), 39 @bindabletdb_stat int, /* dbstatus for database can be added 40 ** to a tdb group */ 41 @instanceid int, 42 @svrmode int, /* Indicates the SMP or SDC mode */ 43 @SMP int, /* Indicates SMP Server */ 44 @SDC int, /* Indicates SDC Server */ 45 @action_code int, /* Indicates action code for the op. */ 46 @MULTDB_ACTION_DO int, /* Action code that operation 47 ** completed successfully 48 */ 49 @MULTDB_ACTION_UNDO int /* Action code to indicate that 50 ** operation did not complete 51 */ 52 53 select @SMP = 0, 54 @SDC = 1 55 56 if @@clustermode != "shared disk cluster" 57 begin 58 select @svrmode = @SMP 59 end 60 else 61 begin 62 select @svrmode = @SDC 63 64 /* 65 ** These values must be kept in sync with their definitions in 66 ** multempdb.h. 67 */ 68 select @MULTDB_ACTION_DO = 2 69 select @MULTDB_ACTION_UNDO = 3 70 end 71 72 /* 73 ** if we're in a transaction, disallow this since it might make recovery 74 ** impossible. 75 */ 76 if @@trancount > 0 77 begin 78 raiserror 17260, "sp_multdb_addtogroup" 79 return (1) 80 end 81 else 82 begin 83 /* Use TSQL mode of unchained transactions */ 84 set chained off 85 end 86 87 /* Dont do "Dirty Reads" */ 88 set transaction isolation level 1 89 90 select @class_id = 16 /* class is MULTEMPDB_CLASS */ 91 select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */ 92 select @object_type = 'D ' /* Object type if 'D ' for temp. db */ 93 select @action = 1 /* New Binding */ 94 95 /* 96 ** The system "tempdb" can not be added to a group. It is implicitly 97 ** part of the default group. 98 ** For a tempdb to be bound to a group: 99 ** In SMP, the tempdb has to be a user create tempdb 100 ** In SDC, the tempdb has to be a local user tempdb 101 */ 102 if (@svrmode = @SMP) 103 begin 104 if @tdb = "tempdb" 105 begin 106 raiserror 18945 107 return (1) 108 end 109 select @bindabletdb_stat = number 110 from master.dbo.spt_values 111 where type = "D3" and name = "user created temp db" 112 end 113 else /* @SDC */ 114 select @bindabletdb_stat = number 115 from master.dbo.spt_values 116 where type = "D3" and name = "local user temp db" 117 118 /* 119 ** 120 ** Check to see that the database being added is indeed 121 ** right type of temporary database. We do not allow the 122 ** addition of a non temporary database to a group. 123 */ 124 if not exists (select * 125 from master..sysdatabases 126 where name = @tdb 127 AND (status3 & @bindabletdb_stat) = @bindabletdb_stat) 128 begin 129 if @@clustermode != "shared disk cluster" 130 raiserror 18946, @tdb 131 else 132 raiserror 19580, @tdb 133 return (1) 134 end 135 136 137 138 /* 139 ** Make sure group name is valid. 140 */ 141 if (@tdbgroup != "default") 142 begin 143 if valid_name(@tdbgroup) = 0 144 begin 145 raiserror 17240, @tdbgroup 146 return (1) 147 end 148 end 149 150 /* 151 ** Check if group exists and get group id 152 */ 153 select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */ 154 select @object_type = 'GR' /* Object type if 'GR' for temp. db group */ 155 156 select @group_id = (select int_value from master..sysattributes 157 where class = @class_id 158 AND attribute = @attrib_id 159 AND object_type = @object_type 160 AND object_cinfo = @tdbgroup) 161 162 /* 163 ** Group must exist in order for us to add a database to it. 164 */ 165 if @group_id is NULL 166 begin 167 raiserror 18947, @tdbgroup 168 return (1) 169 end 170 171 select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */ 172 select @object_type = 'D ' /* Object type if 'D ' for temp db */ 173 174 /* 175 ** If this entry doesn't already exist in sysattributes then 176 ** add the new member into sysattributes. 177 ** Note in SDC, object_info2 will hold the instance id of the tempdb; 178 ** while in SMP, this field is filled with null. 179 */ 180 begin tran bind_tdbtogroup 181 182 183 184 if not exists (select * from master..sysattributes 185 where class = @class_id 186 AND attribute = @attrib_id 187 AND object_type = @object_type 188 AND object_cinfo = @tdb 189 AND object = @group_id) 190 begin 191 insert into master..sysattributes 192 193 (class, attribute, object_type, object_cinfo, object) 194 values 195 (@class_id, @attrib_id, @object_type, @tdb, @group_id) 196 197 if (@@error != 0) 198 begin 199 raiserror 18610, "sp_multdb_addtogroup" 200 goto error_exit 201 end 202 end 203 else 204 begin 205 raiserror 18948, @tdb 206 goto error_exit 207 end 208 209 /* 210 ** Notify the addition of the new database to the group. 211 ** If this returns failure, then delete the just inserted entry. 212 */ 213 select @action = 1 /* ATTR_ADD */ 214 215 if (attrib_notify(@class_id, @attrib_id, @object_type, @group_id, NULL, NULL, 216 NULL, @tdb, NULL, NULL, NULL, NULL, "", 217 @action)) = 0 218 219 begin 220 raiserror 18949 221 goto error_exit 222 end 223 224 commit tran bind_tdbtogroup 225 /* 226 ** Send notification to communicate that transaction is committed and 227 ** in-memory changes be made final. 228 */ 229 if (@svrmode = @SDC) 230 begin 231 select @action = 6 /* ATTR_END */ 232 select @action_code = @MULTDB_ACTION_DO 233 select attrib_notify(@class_id, @attrib_id, @object_type, 234 NULL, NULL, NULL, NULL, NULL, @action_code, NULL, NULL, 235 NULL, "", @action) 236 end 237 return (0) 238 239 error_exit: 240 /* 241 ** In SDC, the in-memory changes are undone as part of the 242 ** abort transaction processing. So, there is no need to send 243 ** end notification with @MULTDB_ACTION_UNDO action. 244 */ 245 rollback tran bind_tdbtogroup 246 return (1) 247
| RESULT SETS | |
| sp_multdb_addtogroup_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysdatabases (1)  read_writes table master..sysattributes (1) reads table master..spt_values (1) CALLERS called by proc sybsystemprocs..sp_tempdb  |
