| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_add_resource_limit | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_add_resource_limit" 6 ** 7 ** 17231, "No login with the specified name exists." 8 ** 17240, "'%1!' is not a valid name." 9 ** 17260, "Can't run %1! from within a transaction." 10 ** 17261, "Only the System Administrator (SA) may execute this procedure." 11 ** 18182, "Timerange name must be non-NULL." 12 ** 18199, "Unknown time range name '%1!'." 13 ** 18202, "At least one of the login or application name must be non-NULL." 14 ** 18203, "Limit type must be non-NULL. 15 ** 18204, "Unknown limit type '%1!'." 16 ** 18205, "Limit value must be non-NULL." 17 ** 18206, "Illegal limit value %1!. Value must be non-negative." 18 ** 18207, "Illegal action %1!." 19 ** 18208, "Illegal enforcement-time value %1! for this limit type." 20 ** 18209, "Illegal scope value %1! for this limit type." 21 ** 18210, "Unknown starting time value '%1!' found in systime ranges." 22 ** 18211, "Unknown ending time value '%1!' found in systimeranges." 23 ** 18212, "New limit would cause overlap with limits on range %1! for this user-application combination." 24 ** 18213, "This user/application can have only one limit for each distinct combination of time range, limit type, enforcement time and scope." 25 ** 18214, "New resource limit created." 26 ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'" 27 ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins. 28 ** 18779, "Unable to find the time range '%1!' with id '%2!' in systimeranges. 29 ** 18781, "Unable to find a limit type with name '%1!' and id '%2!'." 30 */ 31 32 /* 33 ** IMPORTANT: Please read the following instructions before 34 ** making changes to this stored procedure. 35 ** 36 ** To make this stored procedure compatible with High Availability (HA), 37 ** changes to certain system tables must be propagated 38 ** to the companion server under some conditions. 39 ** The tables include (but are not limited to): 40 ** syslogins, sysservers, sysattributes, systimeranges, 41 ** sysresourcelimits, sysalternates, sysdatabases, 42 ** syslanguages, sysremotelogins, sysloginroles, 43 ** sysalternates (master DB only), systypes (master DB only), 44 ** sysusers (master DB only), sysprotects (master DB only) 45 ** please refer to the HA documentation for detail. 46 ** 47 ** Here is what you need to do: 48 ** For each insert/update/delete statement, add three sections to 49 ** -- start HA transaction prior to the statement 50 ** -- add the statement 51 ** -- add HA synchronization code to propagate the change to the companion 52 ** 53 ** For example, if you are adding 54 ** insert master.dbo.syslogins ...... 55 ** the code should look like: 56 ** 1. Before that SQL statement: 57 ** 58 ** 2. Now, the SQL statement: 59 ** insert master.dbo.syslogins ...... 60 ** 3. Add a HA synchronization section right after the SQL statement: 61 ** 62 ** 63 ** You may need to do similar change for each built-in function you 64 ** want to add. 65 ** 66 ** Finally, add a separate part at a place where it can not 67 ** be reached by the normal execution path: 68 ** clean_all: 69 ** 70 ** return (1) 71 */ 72 73 create procedure sp_add_resource_limit 74 @name varchar(255), /* login to which limit applies */ 75 @appname varchar(255), /* application to which limit applies */ 76 @rangename varchar(255), /* timerange during limit applied */ 77 @limittype varchar(30), /* what's being limited */ 78 @limitvalue int, /* upper-bound value of limit */ 79 @enforced int = NULL, /* before or during execution */ 80 @action int = 2, /* what to do if limit is violated */ 81 @scope int = NULL /* scope of limit */ 82 as 83 84 declare @limitid smallint 85 declare @rangeid smallint 86 declare @current_range int 87 declare @enforced_arg int 88 declare @action_arg int 89 declare @scope_arg int 90 declare @msg varchar(1024) 91 declare @start_dt datetime 92 declare @end_dt datetime 93 declare @cur_start_dt datetime 94 declare @cur_end_dt datetime 95 declare @startdaynum int 96 declare @enddaynum int 97 declare @cur_startdaynum int 98 declare @cur_enddaynum int 99 declare @tmp_starttime varchar(30) 100 declare @tmp_endtime varchar(30) 101 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 102 declare @retstat int 103 104 105 select @HA_CERTIFIED = 0 106 107 108 if (proc_role("sa_role") = 0) 109 begin 110 /* 111 ** 17261, "Only the System Administrator (SA) may execute this procedure." 112 */ 113 raiserror 17261 114 return (1) 115 end 116 117 118 119 /* check to see if we are using HA specific SP for a HA enabled server */ 120 exec @retstat = sp_ha_check_certified 'sp_add_resource_limit', @HA_CERTIFIED 121 if (@retstat != 0) 122 return (1) 123 124 if @@trancount > 0 125 begin 126 /* 127 ** 17260, "Can't run %1! from within a transaction." 128 */ 129 raiserror 17260, "sp_add_resource_limit" 130 return (1) 131 end 132 133 if ((@name is null) and (@appname is null)) 134 begin 135 /* 136 ** 18202, "At least one of the login or application name must be non-NULL." 137 */ 138 raiserror 18202 139 return (1) 140 end 141 142 /* Is login valid? */ 143 if ((@name is not null) and not exists 144 (select * from master.dbo.syslogins where name = @name and 145 ((status & 512) != 512))) /* not LOGIN PROFILE */ 146 begin 147 /* 148 ** 17231, "No login with the specified name exists." 149 */ 150 raiserror 17231 151 return (1) 152 end 153 154 /* Has a legal range been specified? */ 155 156 select @rangename = rtrim(@rangename) 157 158 if (@rangename is null) 159 begin 160 /* 161 ** 18182, "Timerange name must be non-NULL." 162 */ 163 raiserror 18182 164 return (1) 165 end 166 else if not exists 167 (select * from master.dbo.systimeranges where name = @rangename) 168 begin 169 /* 170 ** 18199, "Unknown time range name '%1!'." 171 */ 172 raiserror 18199, @rangename 173 return (1) 174 end 175 176 /* Has a legal limit type been specified? */ 177 if (@limittype is null) 178 begin 179 /* 180 ** 18203, "Limit type must be non-NULL. 181 */ 182 raiserror 18203 183 return (1) 184 end 185 else if not exists 186 (select * from master.dbo.spt_limit_types where name = @limittype) 187 begin 188 /* 189 ** 18204, "Unknown limit type '%1!'." 190 */ 191 raiserror 18204, @limittype 192 return (1) 193 end 194 195 /* Has a limit value been specified? */ 196 if (@limitvalue is null) 197 begin 198 /* 199 ** 18205, "Limit value must be non-NULL." 200 */ 201 raiserror 18205 202 return (1) 203 end 204 else if (@limitvalue <= 0) 205 begin 206 /* 207 ** 18206, "Illegal limit value %1!. Value must be non-negative." 208 */ 209 raiserror 18206, @limitvalue 210 return (1) 211 end 212 213 /* Has a legal action been specified? */ 214 /* If NULL, the default (batch abort) is taken */ 215 if (@action is null) 216 begin 217 select @action_arg = 2 218 end 219 else if ((@action < 1) or (@action > 4)) 220 begin 221 /* 222 ** 18207, "Illegal action %1!." 223 */ 224 raiserror 18207, @action 225 return (1) 226 end 227 else 228 begin 229 select @action_arg = @action 230 end 231 232 /* Has a legal enforcement time been specified? */ 233 if (@enforced is NULL) 234 begin 235 select @enforced_arg = enforced from master.dbo.spt_limit_types where name = @limittype 236 end 237 else if (((@enforced & (select enforced from master.dbo.spt_limit_types where name = @limittype)) != @enforced) or (@enforced = 0)) 238 begin 239 /* 240 ** 18208, "Illegal enforcement-time value %1! for this limit type." 241 */ 242 raiserror 18208, @enforced 243 return (1) 244 end 245 else 246 begin 247 select @enforced_arg = @enforced 248 end 249 250 /* Has a legal scope been specified? */ 251 if (@scope is NULL) 252 begin 253 select @scope_arg = scope from master.dbo.spt_limit_types where name = @limittype 254 end 255 else if (((@scope & (select scope from master.dbo.spt_limit_types where name = @limittype)) != @scope) or (@scope = 0)) 256 begin 257 /* 258 ** 18209, "Illegal scope value %1! for this limit type." 259 */ 260 raiserror 18209, @scope 261 return (1) 262 end 263 else 264 begin 265 select @scope_arg = @scope 266 end 267 268 /* Get the id# corresponding to the limit */ 269 select @limitid = id from master.dbo.spt_limit_types where name = @limittype 270 271 /* Get the id# corresponding to the timerange */ 272 select @rangeid = id from master.dbo.systimeranges 273 where name = @rangename 274 275 /* Get the starting time for the range used by this limit */ 276 select @tmp_starttime = starttime from master.dbo.systimeranges 277 where id = @rangeid 278 279 /* Get the ending time for the range used by this limit */ 280 select @tmp_endtime = endtime from master.dbo.systimeranges 281 where id = @rangeid 282 283 /* Get the starting day for the range used by this limit */ 284 select @startdaynum = startday from master.dbo.systimeranges 285 where name = @rangename 286 287 /* Get the ending day for the range used by this limit */ 288 select @enddaynum = endday from master.dbo.systimeranges 289 where name = @rangename 290 291 /* Convert the starting time to datetime so we can do arithmetic */ 292 select @start_dt = convert(datetime, @tmp_starttime) 293 if (@start_dt is null) 294 begin 295 /* 296 ** 18210, "Unknown starting time value '%1!' found in systimeranges." 297 */ 298 raiserror 18210, @tmp_starttime 299 return (1) 300 end 301 302 /* Convert the ending time to datetime so we can do arithmetic */ 303 select @end_dt = convert(datetime, @tmp_endtime) 304 if (@end_dt is null) 305 begin 306 /* 307 ** 18211, "Unknown ending time value '%1!' found in systimeranges." 308 */ 309 raiserror 18211, @tmp_endtime 310 return (1) 311 end 312 313 /* Cursor result: ranges associated with limits currently imposed 314 ** upon the given user/application 315 */ 316 declare c1 cursor for 317 select distinct rangeid from master.dbo.sysresourcelimits 318 where ((name = @name) and (appname = @appname)) or 319 ((name = @name) and (@appname is null)) or 320 ((name = @name) and (appname is null)) or 321 ((name is null) and (appname = @appname)) or 322 ((@name is null) and (appname = @appname)) 323 324 open c1 325 326 fetch c1 into @current_range 327 328 /* We need to peruse the cursor results to see if this new 329 ** limit overlaps with another limit for this user/application. 330 */ 331 while (@@sqlstatus != 2) 332 begin 333 334 if (@current_range != @rangeid) 335 begin 336 337 /* Get the start and end days for current range */ 338 select @cur_startdaynum = startday from 339 master.dbo.systimeranges 340 where id = @current_range 341 select @cur_enddaynum = endday from 342 master.dbo.systimeranges 343 where id = @current_range 344 345 /* Get the start and end times for current range */ 346 select @tmp_starttime = starttime from 347 master.dbo.systimeranges 348 where id = @current_range 349 select @tmp_endtime = endtime from 350 master.dbo.systimeranges 351 where id = @current_range 352 select @cur_start_dt = convert(datetime, @tmp_starttime) 353 select @cur_end_dt = convert(datetime, @tmp_endtime) 354 355 /* See if the limit overlaps with any other 356 ** limit defined for this user/application 357 ** (excepting limits defined for the exact 358 ** same timerange). 359 */ 360 361 /* This is the non-wrapping case, for starttime. 362 ** If (the current range doesn't wrap around the 363 ** end of the week) 364 ** and 365 ** (the days of the new limit's range 366 ** overlap with the current range) 367 ** and 368 ** (the starttime of the new limit's range is after 369 ** the starttime of the current range) 370 ** and 371 ** (the endtime of the new limit's range is before 372 ** the endtime of the current range, with 00:00 373 ** being taken as midnight) 374 ** then we have an overlap, so raise an error 375 ** and return. 376 */ 377 if 378 (@cur_startdaynum <= @cur_enddaynum) 379 and 380 (((@startdaynum >= @cur_startdaynum) and 381 (@startdaynum <= @cur_enddaynum)) or 382 ((@enddaynum >= @cur_startdaynum) and 383 (@enddaynum <= @cur_enddaynum)) or 384 ((@startdaynum <= @cur_startdaynum) and 385 (@enddaynum >= @cur_enddaynum))) 386 and 387 (@start_dt >= @cur_start_dt) 388 and 389 ((@start_dt < @cur_end_dt) or 390 ((datepart(hour, @cur_end_dt) = 0) and 391 (datepart(minute, @cur_end_dt) = 0) and 392 (datepart(second, @cur_end_dt) = 0))) 393 begin 394 /* 395 ** 18212, "New limit would cause overlap with limits on range %1! for this user-application combination." 396 */ 397 raiserror 18212, @current_range 398 return (1) 399 end 400 401 /* This is the wrapping case, for starttime. 402 */ 403 if 404 (@cur_startdaynum > @cur_enddaynum) 405 and 406 ((@startdaynum >= @cur_startdaynum) or 407 (@startdaynum <= @cur_enddaynum) or 408 (@enddaynum >= @cur_startdaynum) or 409 (@enddaynum <= @cur_enddaynum) or 410 ((@startdaynum > @enddaynum) and 411 (@startdaynum <= @cur_startdaynum) and 412 (@enddaynum >= @cur_enddaynum))) 413 and 414 (@start_dt >= @cur_start_dt) 415 and 416 ((@start_dt < @cur_end_dt) or 417 ((datepart(hour, @cur_end_dt) = 0) and 418 (datepart(minute, @cur_end_dt) = 0) and 419 (datepart(second, @cur_end_dt) = 0))) 420 begin 421 /* 422 ** 18212, "New limit would cause overlap with limits on range %1! for this user-application combination." 423 */ 424 raiserror 18212, @current_range 425 return (1) 426 end 427 428 /* This is the non-wrapping case, for endtime. 429 ** If (the current range doesn't wrap around the 430 ** end of the week) 431 ** and 432 ** (the days of the new limit's range 433 ** overlap with the current range) 434 ** and 435 ** (the endtime of the new limit's range is before 436 ** the endtime of the current range, with 00:00 437 ** being taken as midnight) 438 ** and 439 ** (the endtime of the new limit's range is after 440 ** the starttime of the current range) 441 ** then we have an overlap, so raise an error 442 ** and return. 443 */ 444 if 445 (@cur_startdaynum <= @cur_enddaynum) 446 and 447 (((@startdaynum >= @cur_startdaynum) and 448 (@startdaynum <= @cur_enddaynum)) or 449 ((@enddaynum >= @cur_startdaynum) and 450 (@enddaynum <= @cur_enddaynum)) or 451 ((@startdaynum <= @cur_startdaynum) and 452 (@enddaynum >= @cur_enddaynum))) 453 and 454 ((@end_dt <= @cur_end_dt) or 455 ((datepart(hour, @cur_end_dt) = 0) and 456 (datepart(minute, @cur_end_dt) = 0) and 457 (datepart(second, @cur_end_dt) = 0))) 458 and 459 (@end_dt > @cur_start_dt) 460 begin 461 /* 462 ** 18212, "New limit would cause overlap with limits on range %1! for this user-application combination." 463 */ 464 raiserror 18212, @current_range 465 return (1) 466 end 467 468 /* This is the wrapping case, for endtime. 469 */ 470 if 471 (@cur_startdaynum > @cur_enddaynum) 472 and 473 ((@startdaynum >= @cur_startdaynum) or 474 (@startdaynum <= @cur_enddaynum) or 475 (@enddaynum >= @cur_startdaynum) or 476 (@enddaynum <= @cur_enddaynum) or 477 ((@startdaynum > @enddaynum) and 478 (@startdaynum <= @cur_startdaynum) and 479 (@enddaynum >= @cur_enddaynum))) 480 and 481 ((@end_dt <= @cur_end_dt) or 482 ((datepart(hour, @cur_end_dt) = 0) and 483 (datepart(minute, @cur_end_dt) = 0) and 484 (datepart(second, @cur_end_dt) = 0))) 485 and 486 (@end_dt > @cur_start_dt) 487 begin 488 /* 489 ** 18212, "New limit would cause overlap with limits on range %1! for this user-application combination." 490 */ 491 raiserror 18212, @current_range 492 return (1) 493 end 494 495 /* This is the non-wrapping case, where 496 ** the limit's timerange completely 497 ** covers the current range. 498 */ 499 if 500 (@cur_startdaynum <= @cur_enddaynum) 501 and 502 (((@startdaynum >= @cur_startdaynum) and 503 (@startdaynum <= @cur_enddaynum)) or 504 ((@enddaynum >= @cur_startdaynum) and 505 (@enddaynum <= @cur_enddaynum)) or 506 ((@startdaynum <= @cur_startdaynum) and 507 (@enddaynum >= @cur_enddaynum))) 508 and 509 (@start_dt < @cur_start_dt) 510 and 511 (((@end_dt > @cur_end_dt) and 512 ((datepart(hour, @cur_end_dt) != 0) or 513 (datepart(minute, @cur_end_dt) != 0) or 514 (datepart(second, @cur_end_dt) != 0))) 515 or 516 ((datepart(hour, @end_dt) = 0) and 517 (datepart(minute, @end_dt) = 0) and 518 (datepart(second, @end_dt) = 0))) 519 begin 520 /* 521 ** 18212, "New limit would cause overlap with limits on range %1! for this user-application combination." 522 */ 523 raiserror 18212, @current_range 524 return (1) 525 end 526 527 /* This is the wrapping case, where 528 ** the limit's timerange completely 529 ** covers the current range. 530 */ 531 if 532 (@cur_startdaynum > @cur_enddaynum) 533 and 534 ((@startdaynum >= @cur_startdaynum) or 535 (@startdaynum <= @cur_enddaynum) or 536 (@enddaynum >= @cur_startdaynum) or 537 (@enddaynum <= @cur_enddaynum) or 538 ((@startdaynum > @enddaynum) and 539 (@startdaynum <= @cur_startdaynum) and 540 (@enddaynum >= @cur_enddaynum))) 541 and 542 (@start_dt < @cur_start_dt) 543 and 544 (((@end_dt > @cur_end_dt) and 545 ((datepart(hour, @cur_end_dt) != 0) or 546 (datepart(minute, @cur_end_dt) != 0) or 547 (datepart(second, @cur_end_dt) != 0))) 548 or 549 ((datepart(hour, @end_dt) = 0) and 550 (datepart(minute, @end_dt) = 0) and 551 (datepart(second, @end_dt) = 0))) 552 begin 553 /* 554 ** 18212, "New limit would cause overlap with limits on range %1! for this user-application combination." 555 */ 556 raiserror 18212, @current_range 557 return (1) 558 end 559 560 end 561 562 fetch c1 into @current_range 563 end 564 565 /* Make sure we're not adding a duplicate */ 566 if exists (select * from master.dbo.sysresourcelimits 567 where 568 /* 569 ( 570 ((name = @name) and 571 (appname = @appname)) or 572 ((name = @name) and 573 (@appname is null)) or 574 ((@name is null) and 575 (appname = @appname)) 576 ) 577 */ 578 name = @name 579 and appname = @appname 580 581 and rangeid = @rangeid 582 and limitid = @limitid 583 and ((enforced & @enforced_arg) != 0) 584 and ((scope & @scope_arg) != 0)) 585 begin 586 /* 587 ** 18213, "This user/application can have only one limit for each distinct combination of time range, limit type, enforcement time and scope." 588 */ 589 raiserror 18213 590 return (1) 591 end 592 593 594 /* Insert! */ 595 insert master.dbo.sysresourcelimits values (@name, @appname, @rangeid, @limitid, @enforced_arg, @action_arg, @limitvalue, @scope_arg, 0) 596 597 598 599 dbcc recachelimits 600 601 /* 602 ** 18214, "New resource limit created." 603 */ 604 exec sp_getmessage 18214, @msg output 605 print @msg 606 607 /* 608 ** Okay, so we know that the limit has been added ... 609 ** but are resource limits enabled? 610 */ 611 612 declare @rg_status int 613 select @rg_status = s.value from 614 master.dbo.syscurconfigs s, 615 master.dbo.sysconfigures sc 616 where sc.config = s.config 617 and sc.name = "allow resource limits" 618 if (@rg_status = 0) 619 begin 620 /* 621 ** 19373, "WARNING: This limit will not take effect until resource limits 622 ** are enabled for this server. Use sp_configure 'allow resource limits', 1. 623 */ 624 exec sp_getmessage 19373, @msg output 625 print @msg 626 end 627 628 return (0) 629 630 clean_all: 631 632 return (1) 633 634
exec sp_procxmode 'sp_add_resource_limit', 'AnyMode' go Grant Execute on sp_add_resource_limit to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysconfigures (1)  read_writes table master..sysresourcelimits (1) reads table master..systimeranges (1) calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..syslanguages (1) reads table master..sysmessages (1) reads table master..spt_limit_types (1) reads table master..syslogins (1) reads table master..syscurconfigs (1) calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  |
