Database	Proc	Application	Created	Links
sybsystemprocs	sp_drop_resource_limit		31 Aug 14	Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_drop_resource_limit"
6     **
7     ** 17231, "No login with the specified name exists."
8     ** 17260, "Can't run %1! from within a transaction."
9     ** 17261, "Only the System Administrator (SA) may execute this procedure."
10    ** 18199, "Unknown time range name '%1!'."
11    ** 18202, "At least one of the login or application name must be non-NULL."
12    ** 18204, "Unknown limit type '%1!'."
13    ** 18207, "Illegal action %1!."
14    ** 18209, "Illegal scope value %1! for this limit type."
15    ** 18215, "Resource limit dropped."
16    ** 18220, "No such limit found in sysresourcelimits."
17    ** 18773, "HA_LOG: HA consistency check failure in stored procedure '%1!' on companion server '%2!'"
18    ** 18776, "Please also run stored procedure '%1!' on the companion server '%2!'."
19    ** 18778, "Unable to find login '%1!' with id '%2!' in syslogins."
20    ** 18779, "Unable to find the time range '%1!' with id '%2!' in systimeranges.
21    ** 18781, "Unable to find a limit type with name '%1!' and id '%2!'."
22    */
23    
24    /* 
25    ** IMPORTANT: Please read the following instructions before
26    **   making changes to this stored procedure.
27    **
28    **	To make this stored procedure compatible with High Availability (HA),
29    **	changes to certain system tables must be propagated 
30    **	to the companion server under some conditions.
31    **	The tables include (but are not limited to):
32    **		syslogins, sysservers, sysattributes, systimeranges,
33    **		sysresourcelimits, sysalternates, sysdatabases,
34    **		syslanguages, sysremotelogins, sysloginroles,
35    **		sysalternates (master DB only), systypes (master DB only),
36    **		sysusers (master DB only), sysprotects (master DB only)
37    **	please refer to the HA documentation for detail.
38    **
39    **	Here is what you need to do: 
40    **	For each insert/update/delete statement, add three sections to
41    **	-- start HA transaction prior to the statement
42    **	-- add the statement
43    **	-- add HA synchronization code to propagate the change to the companion
44    **
45    **	For example, if you are adding 
46    **		insert master.dbo.syslogins ......
47    **	the code should look like:
48    **	1. Before that SQL statement:
49    **		
50    **	2. Now, the SQL statement:
51    **		insert master.dbo.syslogins ......
52    **	3. Add a HA synchronization section right after the SQL statement:
53    **		
54    **
55    **	You may need to do similar change for each built-in function you
56    **	want to add.
57    **
58    **	Finally, add a separate part at a place where it can not
59    **	be reached by the normal execution path:
60    **	clean_all:
61    **		
62    **		return (1)
63    */
64    
65    create procedure sp_drop_resource_limit
66        @name varchar(255), /* login to which limit applies */
67        @appname varchar(255), /* application to which limit applies */
68        @rangename varchar(255) = NULL, /* timerange to which limit applies */
69        @limittype varchar(255) = NULL, /* what's being limited */
70        @enforced int = NULL, /* before or during execution */
71        @action int = NULL, /* what to do upon violation */
72        @scope int = NULL /* scope of limit */
73    as
74    
75        declare @limitid smallint
76        declare @rangeid smallint
77        declare @msg varchar(1024)
78        declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
79        declare @retstat int
80    
81    
82        select @HA_CERTIFIED = 0
83    
84    
85        if (proc_role("sa_role") = 0)
86        begin
87            /*
88            ** 17261, "Only the System Administrator (SA) may execute this procedure."
89            */
90            raiserror 17261
91            return (1)
92        end
93    
94    
95    
96        /* check to see if we are using HA specific SP for a HA enabled server */
97        exec @retstat = sp_ha_check_certified 'sp_drop_resource_limit', @HA_CERTIFIED
98        if (@retstat != 0)
99            return (1)
100   
101       if @@trancount > 0
102       begin
103           /*
104           ** 17260, "Can't run %1! from within a transaction." 
105           */
106           raiserror 17260, "sp_drop_resource_limit"
107           return (1)
108       end
109   
110       if ((@name is null) and (@appname is null))
111       begin
112           /*
113           ** 18202, "At least one of the login or application name must be non-NULL."
114           */
115           raiserror 18202
116           return (1)
117       end
118   
119       /* If a user name was specified, make sure it's legal */
120       if ((@name is not null) and not exists
121                   (select * from master.dbo.syslogins where name = @name and
122                       ((status & 512) != 512))) /* not LOGIN PROFILE */
123       begin
124           /*
125           ** 17231, "No login with the specified name exists."
126           */
127           raiserror 17231
128           return (1)
129       end
130   
131       /* If a range name was specified, make sure it's legal */
132   
133       select @rangename = rtrim(@rangename)
134   
135       if ((@rangename is not null) and not exists
136                   (select * from master.dbo.systimeranges where name = @rangename))
137       begin
138           /*
139           ** 18199, "Unknown time range name '%1!'."
140           */
141           raiserror 18199, @rangename
142           return (1)
143       end
144   
145       /* If a limit type was specified, make sure it's legal */
146       if ((@limittype is not null) and not exists
147                   (select * from master.dbo.spt_limit_types where name = @limittype))
148       begin
149           /*
150           ** 18204, "Unknown limit type '%1!'."
151           */
152           raiserror 18204, @limittype
153           return (1)
154       end
155   
156       /* If an enforcement time was specified, make sure it's legal */
157       if ((@enforced is not null) and (@limittype is not null) and
158               ((@enforced & (select enforced from master.dbo.spt_limit_types
159                       where name = @limittype)) != @enforced))
160       begin
161           /*
162           ** 18208, "Illegal enforcement-time value %1! for this limit type."
163           */
164           raiserror 18208, @enforced
165           return (1)
166       end
167   
168       /* If an action was specified, make sure it's legal */
169       if ((@action is not null) and ((@action < 1) or (@action > 4)))
170       begin
171           /*
172           ** 18207, "Illegal action %1!."
173           */
174           raiserror 18207, @action
175           return (1)
176       end
177   
178       /* If a scope was specified, make sure it's legal */
179       if ((@scope is not null) and (@limittype is not null) and
180               ((@scope & (select scope from master.dbo.spt_limit_types
181                       where name = @limittype)) != @scope))
182       begin
183           /*
184           ** 18209, "Illegal scope value %1! for this limit type."
185           */
186           raiserror 18209, @scope
187           return (1)
188       end
189   
190       select @limitid = id from master.dbo.spt_limit_types where name = @limittype
191   
192       select @rangeid = id from master.dbo.systimeranges where name = @rangename
193   
194       /* Print a message (but still return successfully) if
195       ** there was no such limit to drop.
196       */
197       if ((select count(*) from master.dbo.sysresourcelimits where
198                       ((((name = @name) and
199                                   (appname = @appname)) or
200                               ((name = @name) and
201                                   (@appname is null)) or
202                               ((@name is null) and
203                                   (appname = @appname))) and
204                           ((@rangename is null) or (rangeid = @rangeid)) and
205                           ((@limittype is null) or (limitid = @limitid)) and
206                           ((@enforced is null) or ((enforced & @enforced) != 0)) and
207                           ((@action is null) or (action = @action)) and
208                           ((@scope is null) or ((scope & @scope) != 0)))
209                   ) = 0)
210       begin
211           /*
212           ** 18220, "No such limit found in sysresourcelimits."
213           */
214           exec sp_getmessage 18220, @msg output
215           print @msg
216           return (0)
217       end
218   
219   
220   
221       /* Delete! */
222       delete from master.dbo.sysresourcelimits where
223           ((((name = @name) and
224                       (appname = @appname)) or
225                   ((name = @name) and
226                       (@appname is null)) or
227                   ((@name is null) and
228                       (appname = @appname))) and
229               ((@rangename is null) or (rangeid = @rangeid)) and
230               ((@limittype is null) or (limitid = @limitid)) and
231               ((@enforced is null) or ((enforced & @enforced) != 0)) and
232               ((@action is null) or (action = @action)) and
233               ((@scope is null) or ((scope & @scope) != 0)))
234   
235   
236   
237       dbcc recachelimits
238   
239       /*
240       ** 18215, "Resource limit dropped."
241       */
242       exec sp_getmessage 18215, @msg output
243       print @msg
244   
245       return (0)
246   
247   clean_all:
248   
249       return (1)
250



exec sp_procxmode 'sp_drop_resource_limit', 'AnyMode'
go

Grant Execute on sp_drop_resource_limit to public
go

DEFECTS
MURC 6 Unreachable Code	247
MURC 6 Unreachable Code	249
QCSC 4 Costly 'select count()', use 'exists()'	197
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	207
QTYP 4 Comparison type mismatch tinyint = int	207
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	232
QTYP 4 Comparison type mismatch tinyint = int	232
TNOI 4 Table with no index master..spt_limit_types	master..spt_limit_types
TNOU 4 Table with no unique index master..sysresourcelimits	master..sysresourcelimits
TNOU 4 Table with no unique index master..systimeranges	master..systimeranges
MGTP 3 Grant to public master..spt_limit_types
MGTP 3 Grant to public master..syslogins
MGTP 3 Grant to public master..sysresourcelimits
MGTP 3 Grant to public master..systimeranges
MGTP 3 Grant to public sybsystemprocs..sp_drop_resource_limit
MNER 3 No Error Check should check return value of exec	214
MNER 3 No Error Check should check @@error after delete	222
MNER 3 No Error Check should check return value of exec	242
MUCO 3 Useless Code Useless Brackets	85
MUCO 3 Useless Code Useless Brackets	91
MUCO 3 Useless Code Useless Brackets	98
MUCO 3 Useless Code Useless Brackets	99
MUCO 3 Useless Code Useless Brackets	107
MUCO 3 Useless Code Useless Brackets	110
MUCO 3 Useless Code Useless Brackets	116
MUCO 3 Useless Code Useless Brackets	120
MUCO 3 Useless Code Useless Brackets	128
MUCO 3 Useless Code Useless Brackets	135
MUCO 3 Useless Code Useless Brackets	142
MUCO 3 Useless Code Useless Brackets	146
MUCO 3 Useless Code Useless Brackets	153
MUCO 3 Useless Code Useless Brackets	157
MUCO 3 Useless Code Useless Brackets	165
MUCO 3 Useless Code Useless Brackets	169
MUCO 3 Useless Code Useless Brackets	175
MUCO 3 Useless Code Useless Brackets	179
MUCO 3 Useless Code Useless Brackets	187
MUCO 3 Useless Code Useless Brackets	197
MUCO 3 Useless Code Useless Brackets	198
MUCO 3 Useless Code Useless Brackets	216
MUCO 3 Useless Code Useless Brackets	223
MUCO 3 Useless Code Useless Brackets	245
MUCO 3 Useless Code Useless Brackets	249
QAFM 3 Var Assignment from potentially many rows	190
QAFM 3 Var Assignment from potentially many rows	192
QPNC 3 No column in condition	201
QPNC 3 No column in condition	202
QPNC 3 No column in condition	204
QPNC 3 No column in condition	205
QPNC 3 No column in condition	206
QPNC 3 No column in condition	207
QPNC 3 No column in condition	208
QPNC 3 No column in condition	226
QPNC 3 No column in condition	227
QPNC 3 No column in condition	229
QPNC 3 No column in condition	230
QPNC 3 No column in condition	231
QPNC 3 No column in condition	232
QPNC 3 No column in condition	233
QSWV 3 Sarg with variable @rangeid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S	204
QSWV 3 Sarg with variable @limitid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S	205
QSWV 3 Sarg with variable @rangeid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S	229
QSWV 3 Sarg with variable @limitid, Candidate Index: sysresourcelimits.csysresourcelimits clustered(name, appname) S	230
MSUB 2 Subquery Marker	121
MSUB 2 Subquery Marker	136
MSUB 2 Subquery Marker	147
MSUB 2 Subquery Marker	158
MSUB 2 Subquery Marker	180
MSUB 2 Subquery Marker	197
MTR1 2 Metrics: Comments Ratio Comments: 51%	65
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 28 = 37dec - 11exi + 2	65
MTR3 2 Metrics: Query Complexity Complexity: 121	65