DatabaseProcApplicationCreatedLinks
sybsystemprocssp_setlockpromote  31 Aug 14Defects Dependencies

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

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..sysattributes  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_configure  
   read_writes table tempdb..#optlevel (1) 
   reads table master..spt_values (1)  
   reads table master..syscurconfigs (1)  
   reads table master..syscharsets (1)  
   reads table master..sysconfigures (1)  
   reads table master..syslanguages (1)  
   writes table tempdb..#temptable1 (1) 
   calls proc sybsystemprocs..sp_autoformat  
      reads table master..systypes (1)  
      calls proc sybsystemprocs..sp_namecrack  
      calls proc sybsystemprocs..sp_autoformat  
      reads table master..syscolumns (1)  
      reads table tempdb..systypes (1)  
      read_writes table tempdb..#colinfo_af (1) 
      reads table tempdb..syscolumns (1)  
   calls proc sybsystemprocs..sp_configure  
   writes table tempdb..#temptab (1) 
   calls proc sybsystemprocs..sp_aux_getsize  
   reads table master..sysattributes (1)  
   writes table tempdb..#temptable (1) 
   read_writes table tempdb..#configure_temp (1) 
   writes table tempdb..#temptable3 (1) 
   calls proc sybsystemprocs..sp_getmessage  
      reads table master..syslanguages (1)  
      reads table master..sysmessages (1)  
      reads table sybsystemprocs..sysusermessages  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
   reads table master..sysdevices (1)  
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
reads table master..sysconfigures (1)  

CALLERS
called by proc sybsystemprocs..sp_setrowlockpromote  
called by proc sybsystemprocs..sp_setpglockpromote