| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_drop_time_range | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_drop_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 ** 18194, "`at all times' range may not be dropped." 11 ** 18195, "There are still limits using this range." 12 ** 18196, "Time range '%1!' dropped." 13 ** 18199, "Unknown time range name '%1!'." 14 ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'" 15 ** 18779, "Unable to find the time range '%1!' with id '%2!' in systimeranges." 16 */ 17 18 /* 19 ** IMPORTANT: Please read the following instructions before 20 ** making changes to this stored procedure. 21 ** 22 ** To make this stored procedure compatible with High Availability (HA), 23 ** changes to certain system tables must be propagated 24 ** to the companion server under some conditions. 25 ** The tables include (but are not limited to): 26 ** syslogins, sysservers, sysattributes, systimeranges, 27 ** sysresourcelimits, sysalternates, sysdatabases, 28 ** syslanguages, sysremotelogins, sysloginroles, 29 ** sysalternates (master DB only), systypes (master DB only), 30 ** sysusers (master DB only), sysprotects (master DB only) 31 ** please refer to the HA documentation for detail. 32 ** 33 ** Here is what you need to do: 34 ** For each insert/update/delete statement, add three sections to 35 ** -- start HA transaction prior to the statement 36 ** -- add the statement 37 ** -- add HA synchronization code to propagate the change to the companion 38 ** 39 ** For example, if you are adding 40 ** insert master.dbo.syslogins ...... 41 ** the code should look like: 42 ** 1. Before that SQL statement: 43 ** 44 ** 2. Now, the SQL statement: 45 ** insert master.dbo.syslogins ...... 46 ** 3. Add a HA synchronization section right after the SQL statement: 47 ** 48 ** 49 ** You may need to do similar change for each built-in function you 50 ** want to add. 51 ** 52 ** Finally, add a separate part at a place where it can not 53 ** be reached by the normal execution path: 54 ** clean_all: 55 ** 56 ** return (1) 57 */ 58 59 create procedure sp_drop_time_range 60 @name varchar(255) /* name of range to be dropped */ 61 as 62 63 declare @msg varchar(1024) 64 declare @rangeid int 65 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 66 declare @retstat int 67 68 69 select @HA_CERTIFIED = 0 70 71 72 73 74 /* check to see if we are using HA specific SP for a HA enabled server */ 75 exec @retstat = sp_ha_check_certified 'sp_drop_time_range', @HA_CERTIFIED 76 if (@retstat != 0) 77 return (1) 78 79 if (proc_role("sa_role") = 0) 80 begin 81 /* 82 ** 17261, "Only the System Administrator (SA) may execute this procedure." 83 */ 84 raiserror 17261 85 return (1) 86 end 87 88 if @@trancount > 0 89 begin 90 /* 91 ** 17260, "Can't run %1! from within a transaction." 92 */ 93 raiserror 17260, "sp_drop_time_range" 94 return (1) 95 end 96 97 /* range name must be non-null */ 98 99 select @name = rtrim(@name) 100 101 if (@name is null) 102 begin 103 /* 104 ** 18182, "Timerange name must be non-NULL." 105 */ 106 raiserror 18182 107 return (1) 108 end 109 110 if not exists ( 111 select * from master.dbo.systimeranges 112 where upper(name) = upper(@name)) 113 begin 114 /* 115 ** 18199, "Unknown time range name '%1!'." 116 */ 117 raiserror 18199, @name 118 return (1) 119 end 120 121 /* Get the id # associated with the range */ 122 select @rangeid = id from master.dbo.systimeranges 123 where upper(name) = upper(@name) 124 125 /* "at all times" cannot be dropped */ 126 if (@rangeid = 1) 127 begin 128 /* 129 ** 18194, "`at all times' range may not be dropped." 130 */ 131 raiserror 18194 132 return (1) 133 end 134 135 /* Make sure there aren't any limits still using this range */ 136 if exists (select * from master.dbo.sysresourcelimits 137 where rangeid = @rangeid) 138 begin 139 /* 140 ** 18195, "There are still limits using this range." 141 */ 142 raiserror 18195 143 /* Adaptive Server has expanded all '*' elements in the following statement */ select master.dbo.sysresourcelimits.name, master.dbo.sysresourcelimits.appname, master.dbo.sysresourcelimits.rangeid, master.dbo.sysresourcelimits.limitid, master.dbo.sysresourcelimits.enforced, master.dbo.sysresourcelimits.action, master.dbo.sysresourcelimits.limitvalue, master.dbo.sysresourcelimits.scope, master.dbo.sysresourcelimits.spare from master.dbo.sysresourcelimits 144 where rangeid = @rangeid 145 return (1) 146 end 147 148 149 150 /* Delete! */ 151 delete master.dbo.systimeranges 152 where upper(name) = upper(@name) 153 154 155 156 dbcc waketimerange 157 /* 158 ** 18196, "Time range '%1!' dropped." 159 */ 160 exec sp_getmessage 18196, @msg output 161 print @msg, @name 162 163 return (0) 164 165 clean_all: 166 167 return (1) 168
exec sp_procxmode 'sp_drop_time_range', 'AnyMode' go Grant Execute on sp_drop_time_range to public go
| RESULT SETS | |
| sp_drop_time_range_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table master..systimeranges (1)  calls proc sybsystemprocs..sp_getmessage  reads table master..sysmessages (1) reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..sysresourcelimits (1) calls proc sybsystemprocs..sp_ha_check_certified  reads table tempdb..sysobjects (1)  |
