DatabaseProcApplicationCreatedLinks
sybsystemprocssp_modify_time_range  31 Aug 14Defects 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
DEFECTS
 MURC 6 Unreachable Code 557
 MURC 6 Unreachable Code 559
 MTYP 4 Assignment type mismatch startday: tinyint = int 522
 MTYP 4 Assignment type mismatch endday: tinyint = int 531
 MTYP 4 Assignment type mismatch starttime: varchar(10) = varchar(30) 540
 MTYP 4 Assignment type mismatch endtime: varchar(10) = varchar(30) 549
 QCSC 4 Costly 'select count()', use 'exists()' 506
 TNOU 4 Table with no unique index master..sysresourcelimits master..sysresourcelimits
 TNOU 4 Table with no unique index master..systimeranges master..systimeranges
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause c1 255
 MGTP 3 Grant to public master..sysresourcelimits  
 MGTP 3 Grant to public master..systimeranges  
 MGTP 3 Grant to public sybsystemprocs..sp_modify_time_range  
 MNER 3 No Error Check should check @@error after update 521
 MNER 3 No Error Check should check @@error after update 530
 MNER 3 No Error Check should check @@error after update 539
 MNER 3 No Error Check should check @@error after update 548
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 136
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 170
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 186
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 198
 MUCO 3 Useless Code Useless Brackets 201
 MUCO 3 Useless Code Useless Brackets 207
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 227
 MUCO 3 Useless Code Useless Brackets 248
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 275
 MUCO 3 Useless Code Useless Brackets 340
 MUCO 3 Useless Code Useless Brackets 365
 MUCO 3 Useless Code Useless Brackets 406
 MUCO 3 Useless Code Useless Brackets 431
 MUCO 3 Useless Code Useless Brackets 464
 MUCO 3 Useless Code Useless Brackets 495
 MUCO 3 Useless Code Useless Brackets 513
 MUCO 3 Useless Code Useless Brackets 519
 MUCO 3 Useless Code Useless Brackets 528
 MUCO 3 Useless Code Useless Brackets 537
 MUCO 3 Useless Code Useless Brackets 546
 MUCO 3 Useless Code Useless Brackets 555
 MUCO 3 Useless Code Useless Brackets 559
 MUOT 3 Updates outside transaction 548
 QAFM 3 Var Assignment from potentially many rows 149
 QAFM 3 Var Assignment from potentially many rows 167
 QAFM 3 Var Assignment from potentially many rows 183
 QAFM 3 Var Assignment from potentially many rows 211
 QAFM 3 Var Assignment from potentially many rows 231
 QAFM 3 Var Assignment from potentially many rows 279
 QAFM 3 Var Assignment from potentially many rows 282
 QAFM 3 Var Assignment from potentially many rows 287
 QAFM 3 Var Assignment from potentially many rows 290
 QGWO 3 Group by/Distinct/Union without order by 255
 QSWV 3 Sarg with variable @range_being_modified, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname... 259
 QSWV 3 Sarg with variable @current_range, Candidate Index: systimeranges.csystimeranges clustered(id) F 281
 QSWV 3 Sarg with variable @current_range, Candidate Index: systimeranges.csystimeranges clustered(id) F 284
 QSWV 3 Sarg with variable @current_range, Candidate Index: systimeranges.csystimeranges clustered(id) F 289
 QSWV 3 Sarg with variable @current_range, Candidate Index: systimeranges.csystimeranges clustered(id) F 292
 VUNU 3 Variable is not used @msg 83
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 255
 MSUB 2 Subquery Marker 140
 MSUB 2 Subquery Marker 506
 MSUC 2 Correlated Subquery Marker 256
 MTR1 2 Metrics: Comments Ratio Comments: 42% 63
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 90 = 107dec - 19exi + 2 63
 MTR3 2 Metrics: Query Complexity Complexity: 260 63
 PRED_QUERY_COLLECTION 2 {r=master..sysresourcelimits, r2=master..sysresourcelimits} 0 256

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)