DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_time_range  14 déc. 14Defects Propagation 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 or replace 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        declare @dummy int
68        declare @nullarg varchar(1)
69        declare @gp_enabled int
70    
71    
72    
73        select @HA_CERTIFIED = 0
74    
75    
76    
77    
78        /* check to see if we are using HA specific SP for a HA enabled server */
79        exec @retstat = sp_ha_check_certified 'sp_drop_time_range', @HA_CERTIFIED
80        if (@retstat != 0)
81            return (1)
82    
83        select @nullarg = NULL
84        execute @retstat = sp_aux_checkroleperm "sa_role", "manage resource limit",
85            @nullarg, @gp_enabled output
86    
87        if (@gp_enabled = 0)
88        begin
89            if (proc_role("sa_role") = 0) return 1
90        end
91        else
92        begin
93            select @dummy = proc_auditperm("manage resource limit", @retstat)
94        end
95    
96        if (@retstat != 0)
97        begin
98            return (1)
99        end
100   
101       if @@trancount > 0
102       begin
103           /*
104           ** 17260, "Can't run %1! from within a transaction." 
105           */
106           raiserror 17260, "sp_drop_time_range"
107           return (1)
108       end
109   
110       /* range name must be non-null */
111   
112       select @name = rtrim(@name)
113   
114       if (@name is null)
115       begin
116           /*
117           ** 18182, "Timerange name must be non-NULL."
118           */
119           raiserror 18182
120           return (1)
121       end
122   
123       if not exists (
124               select * from master.dbo.systimeranges
125               where upper(name) = upper(@name))
126       begin
127           /*
128           ** 18199, "Unknown time range name '%1!'."
129           */
130           raiserror 18199, @name
131           return (1)
132       end
133   
134       /* Get the id # associated with the range */
135       select @rangeid = id from master.dbo.systimeranges
136       where upper(name) = upper(@name)
137   
138       /* "at all times" cannot be dropped */
139       if (@rangeid = 1)
140       begin
141           /*
142           ** 18194, "`at all times' range may not be dropped."
143           */
144           raiserror 18194
145           return (1)
146       end
147   
148       /* Make sure there aren't any limits still using this range */
149       if exists (select * from master.dbo.sysresourcelimits
150               where rangeid = @rangeid)
151       begin
152           /*
153           ** 18195, "There are still limits using this range."
154           */
155           raiserror 18195
156           /* 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
157           where rangeid = @rangeid
158           return (1)
159       end
160   
161   
162   
163       /* Delete! */
164       delete master.dbo.systimeranges
165       where upper(name) = upper(@name)
166   
167   
168   
169       dbcc waketimerange
170       /*
171       ** 18196, "Time range '%1!' dropped."
172       */
173       exec sp_getmessage 18196, @msg output
174       print @msg, @name
175   
176       return (0)
177   
178   clean_all:
179   
180       return (1)
181   


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

DEFECTS
 MURC 6 Unreachable Code 178
 MURC 6 Unreachable Code 180
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 150
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 TNOU 4 Table with no unique index master..sysresourcelimits master..sysresourcelimits
 TNOU 4 Table with no unique index master..systimeranges master..systimeranges
 MAW1 3 Warning message on %name% master..systimeranges.id: Warning message on systimeranges 135
 MGTP 3 Grant to public master..sysresourcelimits  
 MGTP 3 Grant to public master..systimeranges  
 MGTP 3 Grant to public sybsystemprocs..sp_drop_time_range  
 MNER 3 No Error Check should check @@error after delete 164
 MNER 3 No Error Check should check return value of exec 173
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 89
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 131
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 145
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 180
 QAFM 3 Var Assignment from potentially many rows 135
 QCRS 3 Conditional Result Set 156
 VNRD 3 Variable is not read @dummy 93
 MRST 2 Result Set Marker 156
 MSUB 2 Subquery Marker 123
 MSUB 2 Subquery Marker 149
 MTR1 2 Metrics: Comments Ratio Comments: 57% 59
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 10dec - 8exi + 2 59
 MTR3 2 Metrics: Query Complexity Complexity: 64 59

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysresourcelimits (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table master..systimeranges (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
writes table sybsystemprocs..sp_drop_time_range_rset_001