| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_multdb_unbindall_db | | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** Messages for "sp_multdb_unbindall_db" 5 ** 6 ** 17260, "Can't run %1! from within a transaction." 7 ** 18946, "Either the database '%1!' does not exist or is not a user 8 ** created temporary database." 9 ** 19580, "Either the database '%1!' does not exist or is not a local 10 ** user temporary database." 11 */ 12 13 /* 14 ** Procedure sp_multdb_unbindall_db 15 ** 16 ** This procedure removes all login/application bindings from SYSATTRIBUTES for 17 ** a given database. 18 */ 19 create procedure sp_multdb_unbindall_db 20 @tempdb_name varchar(255) /* temporary database */ 21 as 22 23 declare @class_id smallint, /* class in SYSATTRIBUTES */ 24 @attrib_id smallint, /* attribute in SYSATTRIBUTES */ 25 @int_val int, /* int_value in SYSATTRIBUTES */ 26 @MULTDB_BINDDB int, /* binding is to a temporary database */ 27 @svrmode int, /* Indicates the SMP or SDC mode */ 28 @SMP int, /* Indicates SMP Server */ 29 @SDC int, /* Indicates SDC Server */ 30 @DBT3_USER_TEMPDB int, /* (SMP only) Bit value corresponding 31 ** to DBT3_USER_TEMPDB 32 */ 33 @DBT3_LOCAL_USER_TEMPDB int, /* (SDC only) Bit value corresponding to 34 ** DBT3_LOCAL_USER_TEMPDB 35 */ 36 @action int, /* action for built in */ 37 @ATTR_DROP int /* indicates a binding deletion */ 38 39 select @SMP = 0, 40 @SDC = 1 41 42 if @@clustermode != "shared disk cluster" 43 begin 44 select @svrmode = @SMP 45 46 select @DBT3_USER_TEMPDB = number 47 from master.dbo.spt_values 48 where type = "D3" and name = "user created temp db" 49 end 50 else 51 begin 52 select @svrmode = @SDC 53 54 select @DBT3_LOCAL_USER_TEMPDB = number 55 from master.dbo.spt_values 56 where type = "D3" and name = "local user temp db" 57 end 58 59 /* 60 ** If we're in a transaction, disallow this since it might make recovery 61 ** impossible. 62 */ 63 if @@trancount > 0 64 begin 65 /* 66 ** 17260, "Can't run %1! from within a transaction." 67 */ 68 raiserror 17260, "sp_multdb_unbindall_db" 69 return (1) 70 end 71 else 72 begin 73 /* Use TSQL mode of unchained transactions */ 74 set chained off 75 end 76 77 /* Don't do "Dirty Reads" */ 78 set transaction isolation level 1 79 80 select @class_id = 16 /* class is MULTEMPDB_CLASS */ 81 select @attrib_id = 1 /* attribute is MULTEMPDB_BIND */ 82 83 /* 84 ** Following constants must be consistent with their definition in 85 ** multempdb.c 86 */ 87 select @MULTDB_BINDDB = 0 88 89 /* keep in sync with sysattr.h */ 90 select @ATTR_DROP = 3 91 92 /* 93 ** In SMP, verify that specified dbname is a user created temporary database. 94 ** Database bindings for a login/application could be created with user 95 ** create temporary databases only. Note that login/application binding 96 ** cannot be created with system tempdb. 97 ** 98 ** In SDC, verify that specified dbname is a local user temporary database. 99 ** Database bindings for a login/application could be created with local 100 ** user temporary databases only. 101 */ 102 if (@svrmode = @SDC) 103 begin 104 if not exists (select * from master..sysdatabases 105 where name = @tempdb_name 106 AND ((status3 & @DBT3_LOCAL_USER_TEMPDB) = 107 @DBT3_LOCAL_USER_TEMPDB)) 108 begin 109 /* 110 ** 19580, "Either the database '%1!' does not exist 111 ** or is not a local user temporary database." 112 */ 113 raiserror 19580, @tempdb_name 114 return (1) 115 end 116 end 117 else /* @SMP */ 118 begin 119 if not exists (select * from master..sysdatabases 120 where name = @tempdb_name 121 AND ((status3 & @DBT3_USER_TEMPDB) = 122 @DBT3_USER_TEMPDB)) 123 begin 124 /* 125 ** 18946, "Either the database '%1!' does not exist 126 ** or is not a user created temporary database." 127 */ 128 raiserror 18946, @tempdb_name 129 return (1) 130 end 131 end 132 133 /* 134 ** Delete all login and application bindings 135 */ 136 select @attrib_id = 1 /* Attribute is for binding */ 137 select @int_val = @MULTDB_BINDDB /* Binding is to a database as 138 ** opposed to a group 139 */ 140 select @action = @ATTR_DROP 141 142 begin transaction multdb_unbindall_db 143 144 /* 145 ** Delete the entry representing login/application bindings to the 146 ** given database. 147 */ 148 delete from master..sysattributes 149 where class = @class_id 150 AND attribute = @attrib_id 151 AND char_value = @tempdb_name 152 AND int_value = @int_val 153 154 if (@@error != 0) 155 begin 156 /* 157 ** 18608, "%1!: Delete row from master.dbo.sysattributes failed. 158 ** Command aborted." 159 */ 160 raiserror 18608, "sp_multdb_unbindall_db" 161 goto error_exit 162 end 163 164 /* 165 ** Currently, no action is required upon notification of an unbindall event. 166 ** Uncomment the notification below when some action needs to be implemented. 167 ** Send only one notification for this operation. The notified routine needs 168 ** to decode the operation code as 'unbindall_db' by examining the received 169 ** arguments. 170 */ 171 -- select @notify_status = 172 -- attrib_notify (@class_id, @attrib_id, NULL, NULL, 173 -- NULL, NULL, NULL, NULL, 174 -- @MULTDB_BINDDB, @tempdb_name, NULL, NULL, "", @action) 175 -- 176 --if (@notify_status = 0) 177 --begin 178 -- print "Internal error: Failed to remove binding." 179 -- goto error_exit 180 --end 181 182 commit transaction multdb_unbindall_db 183 return (0) 184 185 error_exit: 186 rollback transaction multdb_unbindall_db 187 return (1) 188
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysdatabases (1)  reads table master..spt_values (1) writes table master..sysattributes (1) CALLERS called by proc sybsystemprocs..sp_tempdb  |
