| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_modify_resource_limit | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_modify_resource_limit" 6 ** 7 ** 17231, "No login with the specified name exists." 8 ** 17260, "Can't run %1! from within a transaction." 9 ** 17261, "Only the System Administrator (SA) may execute this procedure." 10 ** 18182, "Timerange name must be non-NULL." 11 ** 18199, "Unknown time range name '%1!'." 12 ** 18202, "At least one of the login or application name must be non-NULL." 13 ** 18203, "Limit type must be non-NULL." 14 ** 18204, "Unknown limit type '%1!'." 15 ** 18206, "Illegal limit value %1!. Value must be non-negative." 16 ** 18207, "Illegal action %1!." 17 ** 18208, "Illegal enforcement-time value %1! for this limit type." 18 ** 18209, "Illegal scope value %1! for this limit type." 19 ** 18773, "HA_LOG: HA consistency check failure in '%1!' on the companion server '%2!'" 20 ** 18776, "Please also run stored procedure '%1!' on the companion server '%2!'." 21 ** 18779, "Unable to find the time range '%1!' with id '%2!' in systimeranges." 22 ** 18781, "Unable to find a limit type with name '%1!' and id '%2!'." 23 ** 24 */ 25 26 /* 27 ** IMPORTANT: Please read the following instructions before 28 ** making changes to this stored procedure. 29 ** 30 ** To make this stored procedure compatible with High Availability (HA), 31 ** changes to certain system tables must be propagated 32 ** to the companion server under some conditions. 33 ** The tables include (but are not limited to): 34 ** syslogins, sysservers, sysattributes, systimeranges, 35 ** sysresourcelimits, sysalternates, sysdatabases, 36 ** syslanguages, sysremotelogins, sysloginroles, 37 ** sysalternates (master DB only), systypes (master DB only), 38 ** sysusers (master DB only), sysprotects (master DB only) 39 ** please refer to the HA documentation for detail. 40 ** 41 ** Here is what you need to do: 42 ** For each insert/update/delete statement, add three sections to 43 ** -- start HA transaction prior to the statement 44 ** -- add the statement 45 ** -- add HA synchronization code to propagate the change to the companion 46 ** 47 ** For example, if you are adding 48 ** insert master.dbo.syslogins ...... 49 ** the code should look like: 50 ** 1. Before that SQL statement: 51 ** 52 ** 2. Now, the SQL statement: 53 ** insert master.dbo.syslogins ...... 54 ** 3. Add a HA synchronization section right after the SQL statement: 55 ** 56 ** 57 ** You may need to do similar change for each built-in function you 58 ** want to add. 59 ** 60 ** Finally, add a separate part at a place where it can not 61 ** be reached by the normal execution path: 62 ** clean_all: 63 ** 64 ** return (1) 65 */ 66 67 create procedure sp_modify_resource_limit 68 @name varchar(255), /* login to which limit applies */ 69 @appname varchar(255), /* application to which limit applies */ 70 @rangename varchar(255), /* range during which limit is enforced */ 71 @limittype varchar(30), /* what's being limited */ 72 @limitvalue int = NULL, /* upper-bound value for limit */ 73 @enforced int, /* before or during execution */ 74 @action int = NULL, /* what to do if limit is violated */ 75 @scope int /* scope of limit */ 76 as 77 78 declare @limitid smallint 79 declare @rangeid smallint 80 declare @enforced_arg int 81 declare @scope_arg int 82 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 83 declare @retstat int 84 85 86 select @HA_CERTIFIED = 0 87 88 89 90 91 /* check to see if we are using HA specific SP for a HA enabled server */ 92 exec @retstat = sp_ha_check_certified 'sp_modify_resource_limit', @HA_CERTIFIED 93 if (@retstat != 0) 94 return (1) 95 96 if (proc_role("sa_role") = 0) 97 begin 98 /* 99 ** 17261, "Only the System Administrator (SA) may execute this procedure." 100 */ 101 raiserror 17261 102 return (1) 103 end 104 105 if @@trancount > 0 106 begin 107 /* 108 ** 17260, "Can't run %1! from within a transaction." 109 */ 110 raiserror 17260, "sp_modify_resource_limit" 111 return (1) 112 end 113 114 if ((@name is null) and (@appname is null)) 115 begin 116 /* 117 ** 18202, "At least one of the login or application name must be non-NULL." 118 */ 119 raiserror 18202 120 return (1) 121 end 122 123 /* Was a valid user name specified? */ 124 if ((@name is not null) and not exists 125 (select * from master.dbo.syslogins where name = @name and 126 ((status & 512) != 512))) /* not LOGIN PROFILE */ 127 begin 128 /* 129 ** 17231, "No login with the specified name exists." 130 */ 131 raiserror 17231 132 return (1) 133 end 134 135 /* Was a valid range name specified? */ 136 137 select @rangename = rtrim(@rangename) 138 139 if (@rangename is null) 140 begin 141 /* 142 ** 18182, "Timerange name must be non-NULL." 143 */ 144 raiserror 18182 145 return (1) 146 end 147 else if not exists 148 (select * from master.dbo.systimeranges where name = @rangename) 149 begin 150 /* 151 ** 18199, "Unknown time range name '%1!'." 152 */ 153 raiserror 18199, @rangename 154 return (1) 155 end 156 157 /* Was a valid limit type specified? */ 158 if (@limittype is null) 159 begin 160 /* 161 ** 18203, "Limit type must be non-NULL." 162 */ 163 raiserror 18203 164 return (1) 165 end 166 else if not exists 167 (select * from master.dbo.spt_limit_types where name = @limittype) 168 begin 169 /* 170 ** 18204, "Unknown limit type '%1!'." 171 */ 172 raiserror 18204, @limittype 173 return (1) 174 end 175 176 /* Was a valid limit value specified? */ 177 if ((@limitvalue is not null) and (@limitvalue <= 0)) 178 begin 179 /* 180 ** 18206, "Illegal limit value %1!. Value must be non-negative." 181 */ 182 raiserror 18206, @limitvalue 183 return (1) 184 end 185 186 /* Was a valid action specified? */ 187 if ((@action is not null) and ((@action < 1) or (@action > 4))) 188 begin 189 /* 190 ** 18207, "Illegal action %1!." 191 */ 192 raiserror 18207, @action 193 return (1) 194 end 195 196 /* Was a valid enforcement time specified? */ 197 if (@enforced is null) 198 begin 199 select @enforced_arg = enforced from master.dbo.spt_limit_types where name = @limittype 200 end 201 else if (((@enforced & (select enforced from master.dbo.spt_limit_types where name = @limittype)) != @enforced) or (@enforced = 0)) 202 begin 203 /* 204 ** 18208, "Illegal enforcement-time value %1! for this limit type." 205 */ 206 raiserror 18208, @enforced 207 return (1) 208 end 209 else 210 select @enforced_arg = @enforced 211 212 /* Was a valid scope specified? */ 213 if (@scope is NULL) 214 begin 215 select @scope_arg = scope from master.dbo.spt_limit_types where name = @limittype 216 end 217 else if (((@scope & (select scope from master.dbo.spt_limit_types where name = @limittype)) != @scope) or (@scope = 0)) 218 begin 219 /* 220 ** 18209, "Illegal scope value %1! for this limit type." 221 */ 222 raiserror 18209, @scope 223 return (1) 224 end 225 else 226 select @scope_arg = @scope 227 228 select @limitid = id from master.dbo.spt_limit_types where name = @limittype 229 230 select @rangeid = id from master.dbo.systimeranges where name = @rangename 231 232 if (select count(*) from master.dbo.sysresourcelimits 233 where 234 ((((name = @name) and 235 (appname = @appname)) or 236 ((name = @name) and 237 (@appname is null)) or 238 ((@name is null) and 239 (appname = @appname))) and 240 (rangeid = @rangeid) and 241 (limitid = @limitid) and 242 (enforced = @enforced_arg) and 243 (scope = @scope_arg)) 244 ) = 0 245 begin 246 /* 247 ** 18220, "No such limit found in sysresourcelimits." 248 */ 249 raiserror 18220 250 return (1) 251 end 252 253 254 255 /* Modify the limit value */ 256 if (@limitvalue is not null) 257 begin 258 update master.dbo.sysresourcelimits 259 set limitvalue = @limitvalue 260 where 261 ((((name = @name) and 262 (appname = @appname)) or 263 ((name = @name) and 264 (@appname is null)) or 265 ((@name is null) and 266 (appname = @appname))) and 267 (rangeid = @rangeid) and 268 (limitid = @limitid) and 269 (enforced = @enforced_arg) and 270 (scope = @scope_arg)) 271 272 273 end 274 275 /* Modify the action */ 276 if (@action is not null) 277 begin 278 update master.dbo.sysresourcelimits 279 set action = @action 280 where 281 ((((name = @name) and 282 (appname = @appname)) or 283 ((name = @name) and 284 (@appname is null)) or 285 ((@name is null) and 286 (appname = @appname))) and 287 (rangeid = @rangeid) and 288 (limitid = @limitid) and 289 (enforced = @enforced_arg) and 290 (scope = @scope_arg)) 291 292 293 end 294 295 dbcc recachelimits 296 297 298 299 return (0) 300 301 clean_all: 302 303 304 305 return (1) 306 307
exec sp_procxmode 'sp_modify_resource_limit', 'AnyMode' go Grant Execute on sp_modify_resource_limit to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..syslogins (1)  read_writes table master..sysresourcelimits (1) reads table master..systimeranges (1) calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  reads table master..spt_limit_types (1) |
