Database | Proc | Application | Created | Links |
sybsystemprocs | sp_help_resource_limit ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_help_resource_limit" 6 ** 7 ** 17231, "No login with the specified name exists." 8 ** 18207, "Illegal action %1!." 9 ** 18216, "Users other than the System Administrator (SA) may only view limits applicable to themselves." 10 ** 18217, "Illegal limit time argument '%1!'." 11 ** 18218, "Unknown limitday value '%1!'." 12 ** 13 */ 14 15 create procedure sp_help_resource_limit 16 @name varchar(30) = NULL, /* login to which limits apply */ 17 @appname varchar(30) = NULL, /* application to which limits apply */ 18 @limittime varchar(30) = NULL, /* time during which limits apply */ 19 @limitday varchar(30) = NULL, /* day during which limits apply */ 20 @scope int = NULL, /* scope of limits */ 21 @action int = NULL, /* what to do if limits are exceeded */ 22 @verbose int = 0 /* 1 means describe, 0 means default */ 23 as 24 25 declare @rangeid smallint 26 declare @limittime_dt datetime 27 declare @limitdaynum int 28 declare @procval int 29 declare @has_sa_role int 30 31 /* 32 ** Check if the user executing the procedure 33 ** has sa_role. We use show_role() because 34 ** proc_role() will raise an error message 35 ** if the role is not enabled for this user. 36 */ 37 if (charindex("sa_role", show_role()) > 0) 38 select @has_sa_role = 1 39 else 40 select @has_sa_role = 0 41 42 select @procval = 0 43 44 /* Is login valid? */ 45 if ((@name is not null) and not exists 46 (select * from master.dbo.syslogins where name = @name and 47 ((status & 512) != 512))) /* not LOGIN PROFILE */ 48 begin 49 /* 50 ** 17231, "No login with the specified name exists." 51 */ 52 raiserror 17231 53 return (1) 54 end 55 56 if ((@has_sa_role = 0) and (@name is not null) and (@name != suser_name())) 57 begin 58 /* 59 ** 18216, "Users other than the System Administrator (SA) may only view limits applicable to themselves." 60 */ 61 raiserror 18216 62 return (1) 63 end 64 else 65 begin 66 /* 67 ** Call proc_role() with each role that the user has 68 ** in order to send the success audit records. 69 ** Note that this could mean 1 or 2 audit records. 70 */ 71 if (@has_sa_role = 1) 72 select @procval = proc_role("sa_role") 73 end 74 75 /* Has a legal action been specified? */ 76 if (@action is not null) and ((@action < 1) or (@action > 4)) 77 begin 78 /* 79 ** 18207, "Illegal action %1!." 80 */ 81 raiserror 18207, @action 82 return (1) 83 end 84 85 /* Convert the limit time to datetime so we can do arithmetic */ 86 if (@limittime is not null) 87 begin 88 select @limittime_dt = convert(datetime, @limittime) 89 if (@limittime_dt is null) 90 begin 91 /* 92 ** 18217, "Illegal limit time argument '%1!'." 93 */ 94 raiserror 18217, @limittime 95 return (1) 96 end 97 end 98 99 /* Convert the starting time to datetime so we can do arithmetic */ 100 if (@limitday is not null) 101 begin 102 select @limitdaynum = daytonum(@limitday) 103 if (@limitdaynum = - 1) 104 begin 105 /* 106 ** 18218, "Unknown limitday value '%1!'." 107 */ 108 raiserror 18218, @limitday 109 return (1) 110 end 111 end 112 113 if (@verbose = 0) 114 begin 115 /* Find the limits that apply */ 116 select l.name, l.appname, rangename = r.name, l.rangeid, l.limitid, l.limitvalue, l.enforced, l.action, l.scope 117 into #sphelpreslimit1rs 118 from master.dbo.sysresourcelimits l, master.dbo.systimeranges r 119 where (l.rangeid = r.id) 120 and 121 ( 122 (l.name = @name) or 123 (l.name is null) or 124 ((@has_sa_role = 1) and (@name is null)) or 125 ((suser_name() = @name) and (l.name is null)) 126 ) 127 and 128 ((@appname is null) or (l.appname = @appname)) 129 and 130 ((@scope is null) or (l.scope = @scope)) 131 and 132 ((@action is null) or (l.action = @action)) 133 and 134 ( 135 ( 136 (r.startday <= r.endday) 137 and 138 ((@limitday is null) or 139 ((@limitdaynum >= r.startday) and 140 (@limitdaynum <= r.endday))) 141 and 142 ((@limittime is null) or 143 ((convert(datetime, r.starttime) <= @limittime_dt) and 144 ((convert(datetime, r.endtime) > @limittime_dt) or 145 ((datepart(hour, convert(datetime, r.endtime)) = 0) and 146 (datepart(minute, convert(datetime, r.endtime)) = 0) and 147 (datepart(second, convert(datetime, r.endtime)) = 0))))) 148 ) 149 or 150 ( 151 (r.startday >= r.endday) 152 and 153 ((@limitday is null) or 154 ((@limitdaynum >= r.startday) or 155 (@limitdaynum <= r.endday))) 156 and 157 ((@limittime is null) or 158 ((convert(datetime, r.starttime) <= @limittime_dt) and 159 ((convert(datetime, r.endtime) > @limittime_dt) or 160 ((datepart(hour, convert(datetime, r.endtime)) = 0) and 161 (datepart(minute, convert(datetime, r.endtime)) = 0) and 162 (datepart(second, convert(datetime, r.endtime)) = 0))))) 163 ) 164 ) 165 exec sp_autoformat @fulltabname = #sphelpreslimit1rs 166 drop table #sphelpreslimit1rs 167 end 168 else 169 begin 170 /* Find the limits that apply */ 171 select l.name, l.appname, rangename = r.name, l.rangeid, 172 case l.limitid 173 when 1 then convert(char(15), 174 "io_cost" + "(" + ltrim(str(l.limitid)) + ")") 175 when 2 then convert(char(15), 176 "elapsed_time" + "(" + ltrim(str(l.limitid)) + ")") 177 when 3 then convert(char(15), 178 "row_count" + "(" + ltrim(str(l.limitid)) + ")") 179 when 4 then convert(char(15), 180 "tempdb_space" + "(" + ltrim(str(l.limitid)) + ")") 181 end AS "limitType", 182 l.limitvalue, 183 case l.enforced 184 when 1 then convert(char(16), "Compilation Time") 185 when 2 then convert(char(16), "Execution Time") 186 when 3 then convert(char(16), "Any Time") 187 end AS "enforced", 188 case l.action 189 when 0 then convert(char(17), "No Action") 190 when 1 then convert(char(17), "Warn") 191 when 2 then convert(char(17), "Abort Batch") 192 when 3 then convert(char(17), "Abort Transaction") 193 when 4 then convert(char(17), "Kill Session") 194 end AS "action", 195 case l.scope 196 when 1 then convert(char(11), "Query") 197 when 2 then convert(char(11), "Batch") 198 when 4 then convert(char(11), "Transaction") 199 when 6 then convert(char(11), "Batch/Tran") 200 end AS "scope" 201 into #sphelpreslimit1rs_v 202 from master.dbo.sysresourcelimits l, master.dbo.systimeranges r 203 where (l.rangeid = r.id) 204 and 205 ( 206 (l.name = @name) or 207 (l.name is null) or 208 ((@has_sa_role = 1) and (@name is null)) or 209 ((suser_name() = @name) and (l.name is null)) 210 ) 211 and 212 ((@appname is null) or (l.appname = @appname)) 213 and 214 ((@scope is null) or (l.scope = @scope)) 215 and 216 ((@action is null) or (l.action = @action)) 217 and 218 ( 219 ( 220 (r.startday <= r.endday) 221 and 222 ((@limitday is null) or 223 ((@limitdaynum >= r.startday) and 224 (@limitdaynum <= r.endday))) 225 and 226 ((@limittime is null) or 227 ((convert(datetime, r.starttime) <= @limittime_dt) and 228 ((convert(datetime, r.endtime) > @limittime_dt) or 229 ((datepart(hour, convert(datetime, r.endtime)) = 0) and 230 (datepart(minute, convert(datetime, r.endtime)) = 0) and 231 (datepart(second, convert(datetime, r.endtime)) = 0))))) 232 ) 233 or 234 ( 235 (r.startday >= r.endday) 236 and 237 ((@limitday is null) or 238 ((@limitdaynum >= r.startday) or 239 (@limitdaynum <= r.endday))) 240 and 241 ((@limittime is null) or 242 ((convert(datetime, r.starttime) <= @limittime_dt) and 243 ((convert(datetime, r.endtime) > @limittime_dt) or 244 ((datepart(hour, convert(datetime, r.endtime)) = 0) and 245 (datepart(minute, convert(datetime, r.endtime)) = 0) and 246 (datepart(second, convert(datetime, r.endtime)) = 0))))) 247 ) 248 ) 249 exec sp_autoformat @fulltabname = #sphelpreslimit1rs_v 250 drop table #sphelpreslimit1rs_v 251 252 end 253 254 return (0) 255 256
exec sp_procxmode 'sp_help_resource_limit', 'AnyMode' go Grant Execute on sp_help_resource_limit to public go
DEPENDENCIES |
PROCS AND TABLES USED reads table master..syslogins (1) ![]() writes table tempdb..#sphelpreslimit1rs (1) writes table tempdb..#sphelpreslimit1rs_v (1) calls proc sybsystemprocs..sp_autoformat ![]() reads table tempdb..syscolumns (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() read_writes table tempdb..#colinfo_af (1) reads table master..syscolumns (1) ![]() reads table tempdb..systypes (1) ![]() reads table master..systypes (1) ![]() calls proc sybsystemprocs..sp_namecrack ![]() reads table master..sysresourcelimits (1) ![]() reads table master..systimeranges (1) ![]() |