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