1
2 /* Stored procedure for adding or modifying lock promotion attribute.
3 ** Database attributes can only be modified when using Master.
4 */
5
6 /*
7 ** Messages for "sp_setlockpromote"
8 **
9 ** 17260, "Can't run %1! from within a transaction."
10 ** 17421, "No such database -- run sp_helpdb to list databases."
11 ** 17460, "Object must be in the current database."
12 ** 18076, "Could not set curwrite to object level. Set your maxwrite label correctly."
13 ** 18090, "The target object does not exist."
14 ** 18158, "At least one of the parameters 'new_lwm', 'new_hwm' or 'new_pct' must be non-NULL to execute this procedure."
15 ** 18159, "You must be in the 'master' database to add, change or drop lock promotion attribute for a user database."
16 ** 18160, "Please specify a non-NULL value for %1, since it has not been set previously with a non-NULL value."
17 ** 18161, "Object name parameter must be NULL for Server-wide lock promotion attributes. Using NULL instead of - %1."
18 ** 18162, "'%1!' is a not a user table. '%1' can be used only on user tables."
19 ** 18163, "The 'lock promotion LWM' value %1 cannot be greater than the 'lock promotion HWM' value %2."
20 ** 18164, "Invalid value specified for 'scope' parameter. Valid values are 'SERVER','DATABASE' or 'TABLE'."
21 ** 18165, "The 'lock promotion!' attributes of %1 '%2' have been changed. The new values are %3."
22 ** 18338, "Invalid obj_type parameter: '%1!'. Please specify 'PAGE' or 'ROW'."
23 */
24
25 create procedure sp_setlockpromote
26 @obj_type varchar(10), /* page or row */
27 @scope varchar(10), /* table, database or server */
28 @objname varchar(767) = NULL, /* table or database name */
29 @new_lwm int = NULL, /* Lock promotion lwm value */
30 @new_hwm int = NULL, /* Lock promotion hwm value */
31 @new_pct int = NULL /* Lock promotion pct value */
32 as
33
34 declare @attrib_objid int /* object id of the table/db */
35 declare @object_type varchar(2) /* object type of the table/db */
36 declare @attrib int /* attrib type of the optname */
37 declare @use_char_value varchar(42) /* Lock promotion attribute's char_value */
38 declare @msg varchar(1024) /* message buffer */
39 declare @action int /* ADD or CHANGE attribute */
40 declare @ret int /* Return value */
41 declare @tab_type smallint /* User table ? */
42 declare @use_lwm int
43 declare @use_hwm int
44 declare @use_pct int
45 declare @update_lwm int
46 declare @update_hwm int
47
48 select @use_lwm = NULL
49 select @use_hwm = NULL
50 select @use_pct = NULL
51
52 /* If we're in a transaction, disallow this */
53 if @@trancount > 0
54 begin
55 /*
56 ** 17260, "Can't run %1! from within a transaction."
57 */
58 raiserror 17260, "sp_setpglockpromote"
59 return (1)
60 end
61 else
62 begin
63 set chained off
64 end
65
66 set transaction isolation level 1
67
68 if upper(@obj_type) not in ("PAGE", "ROW")
69 begin
70 /*
71 ** 18338, "Invalid obj_type parameter: '%1!'. Please specify 'PAGE' or 'ROW'."
72 */
73 raiserror 18338, @obj_type
74 return (1)
75 end
76
77 if @objname like "%.%.%" and
78 substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
79 begin
80 /*
81 ** 17460, "Object must be in the current database."
82 */
83 raiserror 17460
84 return (1)
85 end
86
87 if (upper(@scope) != "DATABASE" and upper(@scope) != "SERVER"
88 and upper(@scope) != "TABLE")
89 begin
90 /*
91 ** 18164, "Invalid value specified for 'scope' parameter
92 ** Valid values are 'SERVER','DATBASE' or 'TABLE'."
93 */
94 raiserror 18164, @objname
95 return (1)
96 end
97
98 if (@new_lwm is NULL and @new_hwm is NULL and @new_pct is NULL)
99 begin
100 /*
101 ** 18158, "At least one of the parameters 'new_lwm', 'new_hwm' or
102 ** 'new_pct' must be non-NULL to execute this procedure."
103 */
104 raiserror 18158
105 return 1
106 end
107
108 if upper(@obj_type) = "PAGE"
109 select @attrib = 0
110 else
111 select @attrib = 1
112
113
114 if (upper(@scope) = "SERVER")
115 begin
116 if @objname is not NULL
117 begin
118 /*
119 ** 18161, "Object name parameter must be NULL for Server-wide
120 ** lock promotion attributes. Using NULL instead of - %1."
121 */
122 raiserror 18161, @objname
123 end
124
125 /*
126 ** For valid attributes we call sp_configure to set the server-wide
127 ** values. All 3 server-wide values cannot be set in the same tran,
128 ** since sp_configure cannot be invoked from within a tran.
129 */
130 if upper(@obj_type) = "PAGE"
131 begin
132 select @use_lwm = value from master.dbo.sysconfigures
133 where config = 253
134
135 select @use_hwm = value from master.dbo.sysconfigures
136 where config = 144
137 end
138 else
139 begin
140 select @use_lwm = value from master.dbo.sysconfigures
141 where config = 324
142
143 select @use_hwm = value from master.dbo.sysconfigures
144 where config = 323
145 end
146
147 /* If the new_lwm is NULL, then we're not updating the LWM. */
148 if @new_lwm is NULL
149 begin
150 select @new_lwm = @use_lwm
151 select @update_lwm = 0
152 end
153 else
154 select @update_lwm = 1
155
156 /* If the new_hwm is NULL, then we're not updating the HWM. */
157 if @new_hwm is NULL
158 begin
159 select @new_hwm = @use_hwm
160 select @update_hwm = 0
161 end
162 else
163 select @update_hwm = 1
164
165 /* Validate that LWM is less than or equal to HWM. */
166 if @new_lwm > @new_hwm
167 begin
168 /*
169 ** 18163, "The 'lock promotion LWM' value %1 cannot
170 ** be greater than the 'lock promotion HWM' value %2."
171 **
172 */
173 raiserror 18163, @new_lwm, @new_hwm
174 return 1
175 end
176
177 /* If the the new LWM value is greater than the existing HWM,
178 ** then configure the HWM value first so that we dont break
179 ** the validation criterion.
180 */
181 if @new_lwm > @use_hwm
182 begin
183 if @update_hwm = 1
184 begin
185 if upper(@obj_type) = "PAGE"
186 exec @ret = sp_configure "page lock promotion HWM", @new_hwm
187 else
188 exec @ret = sp_configure "row lock promotion HWM", @new_hwm
189
190 if (@ret != 0)
191 return @ret
192 end
193
194 if @update_lwm = 1
195 begin
196 if upper(@obj_type) = "PAGE"
197 exec @ret = sp_configure "page lock promotion LWM", @new_lwm
198 else
199 exec @ret = sp_configure "row lock promotion LWM", @new_lwm
200
201 if (@ret != 0)
202 return @ret
203 end
204 end
205 else /* @new_lwm <= @use_hwm */
206 begin
207 if @update_lwm = 1
208 begin
209 if upper(@obj_type) = "PAGE"
210 exec @ret = sp_configure "page lock promotion LWM", @new_lwm
211 else
212 exec @ret = sp_configure "row lock promotion LWM", @new_lwm
213
214 if (@ret != 0)
215 return @ret
216 end
217
218 if @update_hwm = 1
219 begin
220 if upper(@obj_type) = "PAGE"
221 exec @ret = sp_configure "page lock promotion HWM", @new_hwm
222 else
223 exec @ret = sp_configure "row lock promotion HWM", @new_hwm
224
225 if (@ret != 0)
226 return @ret
227 end
228 end
229
230 if (@new_pct is not NULL)
231 begin
232 if upper(@obj_type) = "PAGE"
233 exec @ret = sp_configure "page lock promotion PCT", @new_pct
234 else
235 exec @ret = sp_configure "row lock promotion PCT", @new_pct
236
237 if (@ret != 0)
238 return @ret
239 end
240 /* The server wide values have been successfully configured. */
241 return (0)
242 end
243
244 /*
245 ** Only Accounts with SA role can execute it.
246 ** Call proc_role() with the required SA role.
247 */
248
249 if (proc_role("sa_role") < 1)
250 begin
251 /* "User must be System Administrator (SA)
252 ** to configure lock promotion attributes.
253 */
254 return (1)
255 end
256
257
258 if (upper(@scope) = "DATABASE")
259 begin
260 /*
261 ** If we're configuring lock promotion for a database,
262 ** make sure we're currently in master.
263 */
264
265 select @attrib_objid = db_id()
266 if @attrib_objid != 1
267 begin
268 /*
269 ** 18159, "You must be in the 'master' database to add, change
270 ** or drop lock promotion attribute for a user database."
271 */
272 raiserror 18159
273 return 1
274
275 end
276
277 /* Translate dbname to dbid. */
278 select @attrib_objid = 0
279 select @attrib_objid = dbid from master.dbo.sysdatabases
280 where name = @objname
281
282 if @attrib_objid = 0
283 begin
284 /*
285 ** 17421, "No such database -- run sp_helpdb to list databases."
286 */
287 raiserror 17421
288 return 1
289 end
290
291
292 select @object_type = 'D '
293 end
294
295 if (upper(@scope) = "TABLE")
296 begin
297 /* Translate objname to objid. */
298 select @attrib_objid = 0
299 select @attrib_objid = id, @tab_type = sysstat & 7
300 from sysobjects where id = object_id(@objname)
301
302 if @attrib_objid = 0
303 begin
304 /*
305 ** 18090, "The target object does not exist."
306 */
307 raiserror 18090
308 return 1
309
310 end
311
312 if @tab_type != 3
313 begin
314 /*
315 ** 18162, "'%1!' is a not a user table. '%2' can be
316 ** used only on user tables."
317 */
318 raiserror 18162, @objname, "sp_setlockpromote"
319 return (1)
320 end
321
322 select @object_type = 'T '
323 end
324
325
326 select @use_lwm = object_info1, @use_hwm = object_info2,
327 @use_pct = object_info3
328
329 from sysattributes where class = 5
330 and attribute = @attrib
331 and object_type = @object_type
332 and object = @attrib_objid
333 if @@rowcount != 0
334 begin
335
336 select @action = 2 /* ATTR_CHANGE */
337 end
338 else
339 begin
340 select @action = 1 /* ATTR_ADD */
341 end
342
343
344 /*
345 ** Validate that if a new attribute value is not specified, then an old one
346 ** exists for each of LWM, HWM and PCT.
347 */
348
349 if @new_lwm is not NULL
350 select @use_lwm = @new_lwm
351 if @use_lwm is NULL
352 begin
353 /*
354 ** 18160, "Please specify a non-NULL value for %1,
355 ** since it has not been set previously with a non-NULL value."
356 */
357 raiserror 18160, "new_lwm"
358 return 1
359 end
360
361 if @new_hwm is not NULL
362 select @use_hwm = @new_hwm
363 if @use_hwm is NULL
364 begin
365 /*
366 ** 18160, "Please specify a non-NULL value for %1,
367 ** since it has not been set previously with a non-NULL value."
368 */
369 raiserror 18160, "new_hwm"
370 return 1
371 end
372
373 if @new_pct is not NULL
374 select @use_pct = @new_pct
375 if @use_pct is NULL
376 begin
377 /*
378 ** 18160, "Please specify a non-NULL value for %1,
379 ** since it has not been set previously with a non-NULL value."
380 */
381 raiserror 18160, "new_pct"
382 return 1
383 end
384
385 /* Validate that LWM is less than or equal to HWM. */
386
387 if @use_lwm > @use_hwm
388 begin
389 /*
390 ** 18163, "The 'lock promotion LWM' value %1 cannot
391 ** be greater than the 'lock promotion HWM' value %2."
392 **
393 */
394 raiserror 18163, @use_lwm, @use_hwm
395 return 1
396 end
397
398 /*
399 ** Assemble the char_value field for sysattributes. This is needed for
400 ** sp_help and sp_helpdb to display all 3 of LWM, HWM and PCT, even though
401 ** we use an umbrella attribute "row lock promote", or "page lock promote".
402 */
403
404 select @use_char_value = "PCT = " + convert(varchar(3), @use_pct) +
405 ", LWM = " + convert(varchar(10), @use_lwm) +
406 ", HWM = " + convert(varchar(10), @use_hwm)
407
408 begin transaction
409
410 /* Validate boundary conditions for the attributes. */
411 select @ret = attrib_valid(5, @attrib, @object_type,
412 @attrib_objid, @use_lwm, @use_hwm, @use_pct, NULL,
413 NULL, NULL, NULL, NULL, NULL, @action)
414
415 if @ret = 0 /* Validation routine returned false. */
416 begin
417 rollback tran
418 return (1)
419 end
420
421 if (@action = 2) /* ATTR_CHANGE */
422 begin
423 update sysattributes
424 set char_value = @use_char_value,
425 object_info1 = @use_lwm,
426 object_info2 = @use_hwm,
427 object_info3 = @use_pct
428 where class = 5
429 and attribute = @attrib
430 and object_type = @object_type
431 and object = @attrib_objid
432
433 /*
434 ** If there was an error, @@error will be non-zero
435 */
436 if @@error != 0
437 begin
438 if @@trancount != 0
439 rollback transaction
440 return (1)
441 end
442 end
443 else /* ADD attribute */
444 begin
445
446 insert into sysattributes(class, attribute, object_type, object,
447 object_info1, object_info2,
448 object_info3, char_value)
449 values (5, @attrib, @object_type, @attrib_objid,
450 @new_lwm, @new_hwm, @new_pct,
451 @use_char_value)
452
453 /*
454 ** If there was an error, @@error will be non-zero
455 */
456 if @@error != 0
457 begin
458 if @@trancount != 0
459 rollback transaction
460 return (1)
461 end
462 end
463
464
465 /* Notify */
466 select @ret = attrib_notify(5, @attrib, @object_type,
467 @attrib_objid, @use_lwm, @use_hwm, @use_pct, NULL,
468 NULL, NULL, NULL, NULL, NULL, @action)
469
470 if @ret = 0 /* Unable to notify ? */
471 begin
472 rollback tran
473 return (1)
474 end
475
476 commit transaction
477
478 /*
479 ** 18165, "The %4! lock promotion attributes of %1, '%2'
480 ** have been changed. The new values are %3."
481 */
482 exec sp_getmessage 18165, @msg output
483 print @msg, @scope, @objname, @use_char_value, @obj_type
484
485 return (0)
486
exec sp_procxmode 'sp_setlockpromote', 'AnyMode'
go
Grant Execute on sp_setlockpromote to public
go
DEFECTS |
MINU 4 Unique Index with nullable columns master..sysconfigures |
master..sysconfigures |
MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes |
sybsystemprocs..sysattributes |
MTYP 4 Assignment type mismatch attribute: smallint = int |
449 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
133 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
136 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
141 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
144 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
329 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
330 |
QTYP 4 Comparison type mismatch smallint = int |
330 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
428 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
429 |
QTYP 4 Comparison type mismatch smallint = int |
429 |
MGTP 3 Grant to public master..sysconfigures |
|
MGTP 3 Grant to public master..sysdatabases |
|
MGTP 3 Grant to public sybsystemprocs..sp_setlockpromote |
|
MGTP 3 Grant to public sybsystemprocs..sysattributes |
|
MGTP 3 Grant to public sybsystemprocs..sysobjects |
|
MNER 3 No Error Check should check return value of exec |
186 |
MNER 3 No Error Check should check return value of exec |
197 |
MNER 3 No Error Check should check return value of exec |
210 |
MNER 3 No Error Check should check return value of exec |
221 |
MNER 3 No Error Check should check return value of exec |
233 |
MNER 3 No Error Check should check return value of exec |
482 |
MUCO 3 Useless Code Useless Brackets |
59 |
MUCO 3 Useless Code Useless Brackets |
74 |
MUCO 3 Useless Code Useless Brackets |
84 |
MUCO 3 Useless Code Useless Brackets |
87 |
MUCO 3 Useless Code Useless Brackets |
95 |
MUCO 3 Useless Code Useless Brackets |
98 |
MUCO 3 Useless Code Useless Brackets |
114 |
MUCO 3 Useless Code Useless Brackets |
190 |
MUCO 3 Useless Code Useless Brackets |
201 |
MUCO 3 Useless Code Useless Brackets |
214 |
MUCO 3 Useless Code Useless Brackets |
225 |
MUCO 3 Useless Code Useless Brackets |
230 |
MUCO 3 Useless Code Useless Brackets |
237 |
MUCO 3 Useless Code Useless Brackets |
241 |
MUCO 3 Useless Code Useless Brackets |
249 |
MUCO 3 Useless Code Useless Brackets |
254 |
MUCO 3 Useless Code Useless Brackets |
258 |
MUCO 3 Useless Code Useless Brackets |
295 |
MUCO 3 Useless Code Useless Brackets |
319 |
MUCO 3 Useless Code Useless Brackets |
418 |
MUCO 3 Useless Code Useless Brackets |
421 |
MUCO 3 Useless Code Useless Brackets |
440 |
MUCO 3 Useless Code Useless Brackets |
460 |
MUCO 3 Useless Code Useless Brackets |
473 |
MUCO 3 Useless Code Useless Brackets |
485 |
MUPK 3 Update column which is part of a PK or unique index object_info1 |
424 |
QAFM 3 Var Assignment from potentially many rows |
132 |
QAFM 3 Var Assignment from potentially many rows |
135 |
QAFM 3 Var Assignment from potentially many rows |
140 |
QAFM 3 Var Assignment from potentially many rows |
143 |
QAFM 3 Var Assignment from potentially many rows |
326 |
QISO 3 Set isolation level |
66 |
QIWC 3 Insert with not all columns specified missing 7 columns out of 15 |
446 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} |
329 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object, attribute, class} |
428 |
MTR1 2 Metrics: Comments Ratio Comments: 43% |
25 |
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 34 = 58dec - 26exi + 2 |
25 |
MTR3 2 Metrics: Query Complexity Complexity: 236 |
25 |