| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_dropthreshold | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_dropthreshold" 17903 6 ** 7 ** 17871, "There is no segment named '%1!'." 8 ** 17875, "Only the DBO of database '%1!' or a user with System Administrator 9 ** (SA) authorization may add, delete, or modify thresholds in that 10 ** database." 11 ** 17903, "Table '%1!' does not exist in database '%2!' -- cannot drop 12 ** thresholds." 13 ** 17904, "Segment '%1!' does not have a threshold at '%2!' pages." 14 ** 17905, "You may not drop the log's last-chance threshold." 15 ** 17906, "Dropping threshold for segment '%1!' at '%2!' pages." 16 ** 17289, "Set your curwrite to the hurdle of current database." 17 */ 18 19 create procedure sp_dropthreshold 20 @dbname varchar(255), /* current database name */ 21 @segname varchar(255), /* segment name */ 22 @free_space int /* threshold level */ 23 as 24 25 declare @segno int, 26 @status smallint, 27 @msg varchar(1024) 28 29 /* 30 ** If we are under a user defined xact, disallow this since we may 31 ** leave the dbtable's threshold cache out-of-sync if the end user 32 ** rollbacks its xact. 33 */ 34 if @@trancount > 0 35 begin 36 /* 37 ** 17260, "Can't run %1! from within a transaction." 38 */ 39 raiserror 17260, "sp_dropthreshold" 40 return (1) 41 end 42 43 set transaction isolation level 1 44 set chained off 45 46 /* 47 ** Make sure we are in the right database 48 */ 49 if (@dbname != db_name()) 50 begin 51 /* 52 ** 18031, "You are in the wrong database. Say 'USE %1!', then run 53 ** this procedure again." 54 */ 55 raiserror 18031, @dbname 56 return (5) 57 end 58 59 /* 60 ** Make sure our database is recent enough to contain Systhresholds 61 */ 62 if (select name from sysobjects 63 where name = 'systhresholds' 64 and type = 'S') 65 is NULL 66 begin 67 /* 68 ** 17903, "Table '%1!' does not exist in database '%2!' -- cannot drop 69 ** thresholds." 70 */ 71 raiserror 17903, "systhresholds", @dbname 72 return (1) 73 end 74 75 /* 76 ** Make sure the user (is the DBO) or (has "sa_role") 77 */ 78 if ((user_id() != 1) and (proc_role("sa_role") < 1)) 79 begin 80 /* 81 ** 17875, "Only the DBO of database %1! or a user with System 82 ** Administrator (SA) authorization may add, delete, or modify 83 ** thresholds in that database." 84 */ 85 raiserror 17875, @dbname 86 return (1) 87 end 88 89 90 91 /* 92 ** Make sure the segment name is valid. 93 */ 94 select @segno = segment from syssegments where name = @segname 95 if @segno is NULL 96 begin 97 /* 98 ** 17871, "There is no segment named '%1!'." 99 */ 100 raiserror 17871, @segname 101 return (2) 102 end 103 104 /* 105 ** Make sure the threshold exists 106 */ 107 if (select free_space from systhresholds 108 where segment = @segno 109 and free_space = @free_space) 110 is null 111 begin 112 /* 113 ** 17904, "Segment '%1!' does not have a threshold at '%2!' pages." 114 */ 115 raiserror 17904, @segname, @free_space 116 select segment_name = @segname, free_space 117 from systhresholds 118 where segment = @segno 119 120 return (3) 121 end 122 123 /* Make sure this is not the last-chance threshold for syslogs */ 124 if @segname = 'logsegment' and 125 (select status from systhresholds 126 where segment = @segno 127 and free_space = @free_space) & 1 = 1 128 begin 129 /* 130 ** 17905, "You may not drop the log's last-chance threshold." 131 */ 132 raiserror 17905 133 return (4) 134 end 135 136 /* 137 ** We have done all the tests we can. Drop the threshold. 138 */ 139 140 /* 141 ** 17906, "Dropping threshold for segment '%1!' at '%2!' pages." 142 */ 143 exec sp_getmessage 17906, @msg output 144 print @msg, @segname, @free_space 145 146 begin transaction delete_threshold 147 delete systhresholds 148 where segment = @segno 149 and free_space = @free_space 150 151 if @@error != 0 152 begin 153 rollback transaction 154 /* 155 ** 17907, "Delete of systhresholds row failed." 156 */ 157 raiserror 17907 158 return (1) 159 end 160 /* 161 ** Last, rebuild the database threshold table 162 */ 163 dbcc dbrepair(@dbname, "newthreshold", @segname) 164 165 166 if @@error != 0 167 begin 168 rollback transaction 169 /* 170 ** 17878, "Rebuild of the database threshold table failed." 171 */ 172 raiserror 17878 173 return (1) 174 end 175 176 commit transaction 177 178 return (0) 179
exec sp_procxmode 'sp_dropthreshold', 'AnyMode' go Grant Execute on sp_dropthreshold to public go
| RESULT SETS | |
| sp_dropthreshold_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table sybsystemprocs..systhresholds calls proc sybsystemprocs..sp_getmessage  reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1)  reads table master..syslanguages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table sybsystemprocs..sysobjects reads table sybsystemprocs..syssegments |
