Database | Proc | Application | Created | Links |
sybsystemprocs | sp_multdb_creategroup | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** Messages for "sp_multdb_creategroup" 5 ** 6 ** 17260, "Can't run %1! from within a transaction." 7 ** 17240, "'%1!' is not a valid name." 8 ** 18610, "%1!: Insert row to master.dbo.sysattributes failed. Command aborted." 9 ** 18941, "Currently only 'default' group is supported." 10 ** 18967, "Group '%1!' already exists." 11 ** 18942, "Internal Error: failed to create group." 12 */ 13 14 /* 15 ** Procedure sp_multdb_creategroup 16 ** 17 ** This procedure adds a new group in SYSATTRIBUTES 18 ** for the MULTEMPDB_CLASS (class 16) 19 ** 20 */ 21 create procedure sp_multdb_creategroup 22 @tdbgroup varchar(255) /* temp db group */ 23 as 24 25 declare @class_id smallint, /* class in SYSATTRIBUTES */ 26 @attrib_id smallint, /* attribute in SYSATTRIBUTES */ 27 @object_type char(2), /* object type in SYSATTRIBUTES */ 28 @object_cinfo varchar(255), /* object_cinfo in SYSATTRIBUTES */ 29 @int_val int, /* int_value in SYSATTRIBUTES */ 30 @group_id int, /* group id database being added to */ 31 @action int, /* action for built in */ 32 @upcase_str varchar(30), 33 @svrmode int, /* Indicates the SMP or SDC mode */ 34 @SMP int, /* Indicates SMP Server */ 35 @SDC int, /* Indicates SDC Server */ 36 @action_code int, /* Indication action code for the op. */ 37 @MULTDB_ACTION_DO int, /* Action code that operation 38 ** completed successfully 39 */ 40 @MULTDB_ACTION_UNDO int /* Action code to indicate that 41 ** operation did not complete 42 */ 43 44 select @SMP = 0, 45 @SDC = 1 46 47 if @@clustermode != "shared disk cluster" 48 begin 49 select @svrmode = @SMP 50 end 51 else 52 begin 53 select @svrmode = @SDC 54 55 /* 56 ** These values must be kept in sync with their definitions in 57 ** multempdb.h. 58 */ 59 60 select @MULTDB_ACTION_DO = 2 61 select @MULTDB_ACTION_UNDO = 3 62 end 63 64 /* 65 ** if we're in a transaction, disallow this since it might make recovery 66 ** impossible. 67 */ 68 if @@trancount > 0 69 begin 70 raiserror 17260, "sp_multdb_creategroup" 71 return (1) 72 end 73 else 74 begin 75 /* Use TSQL mode of unchained transactions */ 76 set chained off 77 end 78 79 /* Dont do "Dirty Reads" */ 80 set transaction isolation level 1 81 82 select @class_id = 16 /* class is MULTEMPDB_CLASS */ 83 select @attrib_id = 0 /* attribute is MULTEMPDB_GROUP */ 84 select @object_type = 'GR' /* Object type if 'GR' for temp. db group */ 85 select @action = 1 /* add new group */ 86 87 /* 88 ** Make sure group name is valid. 89 */ 90 if (@tdbgroup != "default") 91 begin 92 if valid_name(@tdbgroup) = 0 93 begin 94 raiserror 17240, @tdbgroup 95 return (1) 96 end 97 end 98 99 /* 100 ** Check if group already exists. It shouldn't. 101 */ 102 if exists (select * from master..sysattributes 103 where class = @class_id 104 AND attribute = @attrib_id 105 AND object_type = @object_type 106 AND object_cinfo = @tdbgroup) 107 begin 108 raiserror 18967, @tdbgroup 109 return (1) 110 end 111 112 /* 113 ** The "default" group id is fixed at 0. 114 ** For a different group, find the next available id to use. 115 */ 116 if (@tdbgroup = "default") 117 begin 118 select @group_id = 0 119 end 120 else 121 begin 122 select @group_id = (select MAX(int_value) 123 from master..sysattributes 124 where class = @class_id 125 AND attribute = @attrib_id 126 AND object_type = @object_type) 127 128 select @group_id = @group_id + 1 129 end 130 131 /* 132 ** Insert the group entry into the sysattributes. 133 */ 134 begin tran crt_tdbgrp 135 136 /* 137 ** Send the notification to indicate the beginning of the transaction 138 ** to allow cluster synchronization mechanism to be deployed. 139 */ 140 if (@svrmode = @SDC) 141 begin 142 select @action = 5 /* ATTR_BEGIN */ 143 if (attrib_notify(@class_id, @attrib_id, @object_type, 144 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 145 NULL, "", @action)) = 0 146 begin 147 raiserror 18942 148 goto error_exit 149 end 150 end 151 152 insert into master..sysattributes 153 (class, attribute, object_type, object_cinfo, int_value) 154 values 155 (@class_id, @attrib_id, @object_type, @tdbgroup, @group_id) 156 157 if (@@error != 0) 158 begin 159 raiserror 18610, "sp_multdb_creategroup" 160 goto error_exit 161 end 162 163 /* 164 ** Notify the addition of the new group. 165 */ 166 select @action = 1 /* ATTR_ADD */ 167 if (attrib_notify(@class_id, @attrib_id, @object_type, NULL, NULL, NULL, 168 NULL, @tdbgroup, @group_id, NULL, NULL, NULL, "", 169 @action)) = 0 170 begin 171 raiserror 18942 172 goto error_exit 173 end 174 175 commit tran 176 /* 177 ** Send notification to communicate that transaction is committed and 178 ** in-memory changes be made final. 179 */ 180 if (@svrmode = @SDC) 181 begin 182 select @action = 6 /* ATTR_END */ 183 select @action_code = @MULTDB_ACTION_DO 184 select attrib_notify(@class_id, @attrib_id, @object_type, 185 NULL, NULL, NULL, NULL, NULL, @action_code, NULL, NULL, 186 NULL, "", @action) 187 end 188 return (0) 189 190 error_exit: 191 /* 192 ** In SDC, the in-memory changes are undone as part of the 193 ** abort transaction processing. So, there is no need to send 194 ** end notification with @MULTDB_ACTION_UNDO action. 195 */ 196 rollback tran crt_tdbgrp 197 return (1) 198
RESULT SETS | |
sp_multdb_creategroup_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..sysattributes (1) CALLERS called by proc sybsystemprocs..sp_tempdb |