1
2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3 /* 4.8 1.1 06/14/90 sproc/src/configure */
4
5 /*
6 ** Messages for "sp_configure" 17410
7 ** Must use "langid" when referencing spt_values
8 **
9 ** 17260, "Can't run %1! from within a transaction."
10 ** 17410, "Configuration option doesn't exist."
11 ** 17411, "Configuration option is not unique."
12 ** 17413, "The value of the 'number of devices' must not be less than the number of active devices '%1!'
13 ** 17414, "You can't set the default language to a language ID that is not defined in Syslanguages."
14 ** 17415, "Configuration option value is not legal."
15 ** 17418, "'%1!' is an invalid file command. The valid commands are 'verify', 'read', 'write', and 'restore'."
16 ** 17419, "Configuration option changed. The SQL Server need not be rebooted since the option is dynamic.
17 ** 18123, "Configuration option changed. The SQL Server must be rebooted before the change in effect since the option is static."
18 ** 18124, "No matching configuration options. Here is a listing of groups:"
19 ** 18125, "Must provide the parameter 'filename'."
20 ** 18133, "The character set, '%1!', is invalid since it is not defined in Syscharsets."
21 ** 18134, "The sortorder, '%1!', is invalid since it is not defined in Syscharsets."
22 ** 18397, "Changing the value of '%1!' does not increase the amount of
23 ** memory Adaptive Server uses.
24 **
25 ** 18549, "Invalid third argument supplied: '%1!'. Valid choices are
26 ** 'with truncate' or 'default'."
27 **
28 ** 18915, "An additional %1! K bytes of memory is available for
29 ** reconfiguration. This is the difference between 'max memory'
30 ** and 'total logical memory'."
31 **
32 ** 18916, "Changing the value of '%1!' to '%2!' increases the amount of
33 ** memory ASE uses by %3! K."
34 **
35 ** 18917, "Changing the value of '%1!' to '%2!' reduces the amount of
36 ** memory ASE uses by %3! K. The reduced memory may be reused
37 ** when this configure value changes, but will not be released
38 ** until ASE restarts."
39 **
40 ** 18932, "Resulting configuration value and memory use have not changed
41 ** from previous values: new configuration value %1!, previous %2!."
42 **
43 ** 19106, "Cannot change the value of configuration parameter 'global cache
44 ** partition number' when the server is in recovery."
45 **
46 ** 19107, "Cannot change the value of configuration parameter 'max concurrently
47 ** recovered dbs' because the server is still in recovery setup."
48 **
49 ** 19108, "Cannot change the configuration parameter 'max concurrently
50 ** recovered dbs' to %1 when the server is in recovery. The only value
51 ** allowed is 1.
52 **
53 ** 19109, "Cannot change the value of configuration parameter 'global async
54 ** prefetch limit' when the server is in recovery."
55 **
56 ** 19416, "Provider class '%1!' is not a recognized messaging provider class."
57 **
58 ** 19519, "Cannot run sp_configure for server '%1!' since you are not
59 ** connecting to it."
60 **
61 ** 19520, "Cannot configure '%1!' for an instance, since it is a strictly
62 ** cluster-wide option."
63 **
64 ** 19521, "Cannot configure '%1!' to cluster-wide since all active instances
65 ** have instance-specific setting on this option."
66 **
67 ** 19570, "Cannot drop the instance-specific configuration for configuration
68 ** parameter '%1!' since the configuration setting does not exist."
69 **
70 ** 19574, "An instance name needs to be provided. "
71 **
72 ** 19654, "Warning: In Shared Disk Cluster, all instances share the same
73 ** configuration file. Please run sp_configure to read the configuration
74 ** file for all other instances to keep the configuration consistent."
75 ** 19816, "You cannot set configuration values from inside a local temporary
76 ** database."
77 ** 19874, "Cannot configure '%1!' to cluster-wide since it is an
78 ** instance-specific-only option."
79 ** 19953, "Cannot change the value of configuration parameter 'config file
80 ** version' because it is server generated."
81 **
82 ** 19961, "WARNING: Compatibility mode will not be used when 'abstract plan dump/load/replace' is on."
83 ** 19962, "WARNING: Compatibility mode may not be used when statement cache and literal autoparam are enabled."
84 ** 19966, "WARNING: Enabling compatibility mode will not affect the query plans already stored in the procedure cache."
85 ** 19967, "WARNING: The configuration option 'statement cache size' is configured with value '%1!'. Enabling compatibility mode will not affect the query plans already stored in the statement cache."
86 */
87
88 create procedure sp_configure
89 @configname varchar(255) = NULL, /* configure option name */
90 @configvalue int = NULL, /* configure value */
91 @configvalue2 varchar(255) = NULL, /* config file command/charset info */
92 @configvalue3 varchar(255) = NULL /* physical name of file */
93 as
94
95 declare @confignum int /* number of option to be configured */
96 declare @configcount int /* number of options like @configname */
97 declare @whichone int /* using english or default lang ? */
98 declare @cmd smallint /* configuration file command */
99 declare @status int /* return status for misc calls */
100 declare @children int /* number of children in a group */
101 declare @parent int /* config number of parent group */
102 declare @msg varchar(1024) /* temp buffer for messages */
103 declare @sysconfig smallint /* contents of sysconfigures.config */
104 declare @sysname varchar(255) /* contents of sysconfigures.comment */
105 declare @sysparent smallint /* contents of sysconfigures.parent */
106 declare @sysstatus int /* contents of sysconfigures.status */
107 declare @value int /* default charset/sort order id */
108 declare @user_displaylevel int /* user display level */
109 declare @numdevices int /* number of active devices */
110 declare @sorder_chset_id int /* current sortorder or character set id */
111 declare @use_wildcard tinyint /* use wildcard to search option name or not */
112 declare @match_count int /* number of option found by name match */
113 declare @cmpstate int /* Local NODE state in companionship */
114 declare @additional_free_memory int /* Additional Free memory */
115 declare @logical_memory int /* total logical memory before
116 change*/
117 declare @lmemconfignum int /* confignum for 'total logical memory' */
118 declare @additional_memory int /* increase in logical memory due
119 to change*/
120 declare @oldcfgvalue int /*
121 ** previous config value for parameter
122 ** with integer type.
123 */
124 declare @oldcfgvalue_char varchar(255)
125 /*
126 ** previous config value for parameter
127 ** with character type.
128 */
129 declare @defvalue varchar(255) /* default config value */
130 declare @fullconfigname varchar(255) /* configure option name */
131 declare @bvalue int /* base value after unit
132 converting*/
133 declare @unit varchar(20)
134 declare @nocase tinyint /* case-sensitive sort order flag */
135 declare @rec_state varchar(30) /* the server recovery state */
136 declare @new_class smallint /* for valid messaging provider
137 ** class
138 */
139 declare @is_equal int /*
140 ** flag to check whether configuration
141 ** value and run value are same or not.
142 */
143 declare @configcount2 int /* number of options for an instance. */
144 declare @configcount3 int /* number of options for an instance. */
145 declare @match_count2 int /* number of option found by name and
146 ** instance match.
147 */
148 declare @instanceid tinyint /* instance id */
149 declare @use_cluster int /* if cluster-wide value is used. */
150 declare @remote_instance_name varchar(255)
151 /* remote instance name */
152 declare @remote_instance_id tinyint
153 /* remote instance id */
154 declare @retstat int
155 declare @sqlbuf varchar(255)
156
157 declare @non_default_options int /* this option is set when nondefault
158 settings are to be displayed*/
159 declare @tmp_rtms_value int /* to store the value of 'enable real time messaging'*/
160 declare @all_rtms_provider_set int /* to store bit map while all rtms provider enabled*/
161 declare @cfg_rtms_provider_mask int /* to check if the required rtms provider enabled */
162 declare @cfg_rtms_all_mask int /* to check if all options of rtms enabled */
163 declare @optlevel_def varchar(20) /* default value for optlevel */
164 declare @optlevel_run varchar(20) /* run value for optlevel */
165 declare @optlevel_curr varchar(20) /* current value for optlevel */
166
167 declare @config_value_to_check int /* value of config option to be
168 ** checked with compatibility mode.
169 */
170 declare @app_config int /* config number if application functionality */
171
172 /*
173 ** Disallow running sp_configure within a transaction since it might make
174 ** recovery impossible.
175 ** Do the @@trancount check before initializing any local variables,
176 ** because "select" statement itself will start a transaction
177 ** if chained mode is on.
178 */
179 if @@trancount > 0
180 begin
181 /*
182 ** 17260, "Can't run %1! from within a transaction."
183 */
184 raiserror 17260, "sp_configure"
185 return (1)
186 end
187 else
188 begin
189 set chained off
190 end
191
192 select @whichone = 0
193 select @status = 0
194 select @cmd = 1
195 select @value = NULL
196 select @user_displaylevel = NULL
197 select @sorder_chset_id = 0
198 select @is_equal = 0
199 select @use_wildcard = 1
200 select @config_value_to_check = NULL
201 select @use_cluster = 0
202 select @instanceid = NULL
203
204 /*
205 ** Check if the default sort order is case-insensitive.
206 */
207 if ("A" = "a")
208 select @nocase = 1
209 else
210 select @nocase = 0
211
212 set transaction isolation level 1
213 set nocount on /* Adaptive Server has expanded all '*' elements in the following statement */
214
215 /*
216 ** Prepare values for'optimizer level'.
217 */
218 select master.dbo.spt_values.name, master.dbo.spt_values.number, master.dbo.spt_values.type, master.dbo.spt_values.ansi_w, master.dbo.spt_values.low, master.dbo.spt_values.high, master.dbo.spt_values.msgnum into #optlevel
219 from master.dbo.spt_values where type = 'OL'
220 and name like 'ase%'
221
222 select @optlevel_def = name
223 from master.dbo.syscurconfigs, #optlevel
224 where config = 507
225 and number = convert(int, defvalue)
226
227 select @optlevel_run = name
228 from master.dbo.syscurconfigs, #optlevel
229 where config = 507
230 and number = convert(int, value2)
231
232 select @optlevel_curr = name
233 from master.dbo.syscurconfigs, #optlevel
234 where config = 507
235 and number = convert(int, value)
236
237 /*
238 ** If the "default sortorder" is case insensitive dictionary sort order,
239 ** the procedure will just print out all the options and their values
240 ** without grouping if no option name is given.
241 */
242 if (@nocase = 1 and @configname is NULL)
243 begin
244 /* Display all config parameters for specified instance. */
245
246
247 select "Parameter Name" = convert(char(30), name),
248 "Default" = CASE WHEN b.config = 507 THEN @optlevel_def
249 ELSE convert(char(11),
250 space(11 - char_length(
251 convert(varchar(11), defvalue))) +
252 convert(varchar(11), defvalue))
253 END,
254 "Memory Used" = convert(char(11),
255 space(11 - char_length(
256 convert(varchar(11), c.comment))) +
257 convert(varchar(11), c.comment)),
258 "Config Value" = CASE WHEN b.config = 507 THEN @optlevel_curr
259 ELSE convert(char(12),
260 space(12 - char_length(
261 isnull(b.value2,
262 convert(char(32), b.value)))) +
263 isnull(b.value2, convert(char(32), b.value)))
264 END,
265 "Run Value" = CASE WHEN b.config = 507 THEN @optlevel_run
266 ELSE convert(char(12),
267 space(12 - char_length(isnull(c.value2,
268 convert(char(32), c.value)))) +
269 isnull(c.value2, convert(char(32), c.value)))
270 END,
271 "Unit" = convert(char(20), c.unit),
272
273 "Type" = convert(char(20), c.type)
274
275 from master.dbo.sysconfigures b,
276 master.dbo.syscurconfigs c
277 where
278
279 b.config *= c.config
280 and b.config != 19
281 and parent != 19
282
283
284 return (0)
285
286
287 end
288
289
290
291
292 /* check whether nondefault settings are to be displyed.*/
293 if (@configname is not NULL)
294 begin
295
296 if "display nondefault settings" like "%" + @configname + "%"
297 begin
298 declare @countno int
299
300 /*check if configuration option is unique*/
301
302 select @countno = count(*)
303 from master.dbo.sysconfigures
304 where name like "%" + @configname + "%"
305 and parent != 19
306
307
308 /*
309 ** if configuration option is not unique, display the duplicates
310 ** and return
311 */
312 if @countno > 0
313 begin
314
315 select name
316 into #temptab
317 from master.dbo.sysconfigures a
318 where name like "%" + @configname + "%"
319 and parent != 19
320
321 insert into #temptab values
322 ("display nondefault settings")
323
324 raiserror 17411
325 print ""
326 print "Choose option from the following"
327 print ""
328 exec sp_autoformat #temptab
329 return (1)
330 end
331 /*
332 ** if configuration option is unique set non_default_options=1,
333 ** and make configname=NULL so that next block is bypassed
334 */
335 else
336 begin
337 select @non_default_options = 1
338 select @configname = NULL
339 end
340
341 end
342 end
343
344 /* Validate the configname if it not NULL */
345 if @configname is not NULL
346 begin
347 /* Check the duplicate rows for cluster-wide settings. */
348 select @configcount = count(*)
349 from master.dbo.sysconfigures
350 where name like "%" + @configname + "%"
351 and parent != 19
352
353
354 /*
355 ** If configure option is not unique and case-insensitive
356 ** dictionary sort order is used, check if unique option found
357 ** by exact name match, if so, then disable wildcard match
358 ** for searching option name.
359 */
360 if ((@configcount > 1
361
362 )
363 and @nocase = 1)
364
365
366 begin
367 /* check if unique option found by exact name match */
368 select @match_count = count(*)
369 from master.dbo.sysconfigures
370 where name = @configname
371 and parent != 19
372
373
374 if (@match_count = 1
375
376 )
377 begin
378 select @use_wildcard = 0 /* don't use wildcard */
379
380 if @match_count = 1
381 begin
382 select @configcount = @match_count
383 end
384
385
386 end
387 end
388
389 /*
390 ** If more than one option like @configname,
391 ** show the duplicates and return.
392 */
393 if @configcount > 1
394
395 begin
396 /*
397 ** 17411, "Configuration option is not unique."
398 */
399 raiserror 17411
400 print ""
401
402 select "Parameter Name" = convert(char(30), name),
403 "Default" = CASE WHEN a.config = 507 THEN @optlevel_def
404 ELSE convert(char(11), space(11 - char_length(
405 convert(varchar(11), defvalue))) +
406 convert(varchar(11), defvalue))
407 END,
408 "Memory Used" = convert(char(11), space(11 - char_length(
409 convert(varchar(11), b.comment))) +
410 convert(varchar(11), b.comment)),
411 "Config Value" = CASE WHEN a.config = 507 THEN @optlevel_curr
412 ELSE convert(char(12), space(12 - char_length(
413 isnull(a.value2, convert(char(32), a.value)))) +
414 isnull(a.value2, convert(char(32), a.value)))
415 END,
416 "Run Value" = CASE WHEN a.config = 507 THEN @optlevel_run
417 ELSE convert(char(12), space(12 - char_length(
418 isnull(b.value2, convert(char(32), b.value)))) +
419 isnull(b.value2, convert(char(32), b.value)))
420 END,
421 "Unit" = convert(char(20), b.unit),
422
423 "Type" = convert(char(10), b.type)
424
425
426 from master.dbo.sysconfigures a,
427 master.dbo.syscurconfigs b
428 where
429
430 a.config *= b.config
431 and name like "%" + @configname + "%"
432 and parent != 19
433 and a.config != 19
434
435 order by name
436
437 /*
438 ** If @configname like '%memory%' print message.
439 ** 18915, "An additional %1! K bytes of memory is available
440 ** for reconfiguration. This is the difference between
441 ** 'max memory' and 'total logical memory'."
442 **
443 */
444 if (lower(@configname) like '%memory%')
445 begin
446 select @additional_free_memory =
447 (max(b.value) - min(b.value)) * 2
448 from master.dbo.sysconfigures a,
449 master.dbo.syscurconfigs b
450 where a.name in ('max memory',
451 'total logical memory')
452
453 and a.config = b.config
454
455
456 exec sp_getmessage 18915, @msg output
457 print @msg, @additional_free_memory
458 end
459 return (1)
460 end
461
462 /*
463 ** if it is a valid option and the @configvalue is not NULL,
464 ** set the option
465 */
466 if (@configcount != 0) and (@configvalue is not NULL)
467 begin
468 /* set @confignum */
469 select @confignum = config,
470 @sysstatus = status,
471 @fullconfigname = name
472 from master.dbo.sysconfigures
473 where name like "%" + @configname + "%"
474 and parent != 19
475 and config != 19
476
477 /* Disallow running sp_configure on "config file version". */
478 if (@confignum = 504)
479 begin
480 /*
481 ** 19953, "Cannot change the value of configuration
482 ** parameter 'config file version' because it
483 ** is server generated."
484 */
485 raiserror 19953
486 return (1)
487 end
488
489
490
491 /*
492 ** Later, we will want to test whether the config value is
493 ** actually changing. Obtain the current and default values
494 ** for this config so we can ignore requests that result in
495 ** no change. Here, if the parameter datatype is (var)char,
496 ** @oldcfgvalue will be 0; if it's an int, @oldcfgvalue_char
497 ** will be NULL.
498 */
499 select @oldcfgvalue = b.value,
500 @oldcfgvalue_char = b.value2,
501 @defvalue = c.defvalue,
502 @parent = b.parent
503 from master.dbo.sysconfigures b,
504 master.dbo.syscurconfigs c
505 where b.config = @confignum
506
507 and b.config *= c.config
508
509
510 /*
511 ** The @oldcfgvalue could be NULL if this is the
512 ** first time configuration of an instance value.
513 ** If this is the case, get the @oldcfgvalue from
514 ** the cluster-wide settings.
515 */
516 if @oldcfgvalue is NULL
517 begin
518 select @oldcfgvalue = b.value,
519 @oldcfgvalue_char = b.value2,
520 @defvalue = c.defvalue
521 from master.dbo.sysconfigures b,
522 master.dbo.syscurconfigs c
523 where b.config = @confignum
524
525 and b.config *= c.config
526
527 end
528
529
530 if (@configvalue2 = "default")
531 begin
532 select @value = 1
533 if @oldcfgvalue_char is null
534 begin
535 /*
536 ** For config options in group 'Application
537 ** Functionality', the "default" is the current
538 ** value of option 'enable functionality group'.
539 ** That means if 'enable functionality group' is
540 ** 1, the 'default' value for individual feature
541 ** is 1.
542 */
543 if (@confignum != 543 and @parent = 49)
544 begin
545 select @configvalue = value
546 from master.dbo.sysconfigures
547 where config = 543
548 end
549 else
550 begin
551 select @configvalue = convert(int, @defvalue)
552 end
553 end
554 end
555 else
556 select @value = 0
557
558 /*
559 ** If the option name is "configuration file",
560 ** take action, then return.
561 */
562 if @confignum = 114
563 begin
564 /*
565 ** if the file command is not one of the valid
566 ** commands, complain and then quit.
567 */
568 if @configvalue2 not in ("read", "write", "restore",
569 "verify")
570 begin
571 /*
572 ** print the message to show the valid
573 ** file command
574 */
575 raiserror 17418, @configvalue2
576 return (1)
577 end
578
579 /*
580 ** if filename is NULL
581 */
582 if (@configvalue3 is NULL)
583 begin
584 /* 18125, "Must provide the parameter 'filename'." */
585 raiserror 18125
586 return (1)
587 end
588
589 /*
590 ** Must have sa_role to run these commands
591 */
592 if (proc_role("sa_role") < 1)
593 begin
594 return (1)
595 end
596
597 select @cmd = case
598 when (@configvalue2 = "verify") then 2
599 when (@configvalue2 = "read") then 3
600 when (@configvalue2 = "write") then 4
601 else 5 -- "restore"
602 end
603
604
605 select @status = config_admin(@cmd, 0, 0, 0, NULL,
606 @configvalue3)
607
608 if (@status = 1)
609 begin
610 return (0)
611 end
612
613 return (1)
614 end
615
616 if @confignum = 123
617 begin
618 /* get current default charset id */
619 select @value = value from
620 master.dbo.sysconfigures
621 where config = 131
622
623 select @sorder_chset_id = @value
624 if @configvalue2 is not NULL
625 begin
626 /*
627 ** Get default charset id from name and
628 ** validate the charset id.
629 */
630 select @value = id
631 from master..syscharsets
632 where name = @configvalue2
633 and type between 1000 and 1999
634
635 if @value is null
636 begin
637 /* 18133, "The character set, '%1!', is invalid since it
638 ** is not defined in Syscharsets."
639 */
640 raiserror 18133, @configvalue2
641 return (1)
642 end
643 end
644 end
645
646 else
647 if @confignum = 131
648 begin
649 /* get current default sortord id */
650 select @value = value from
651 master.dbo.sysconfigures
652 where config = 123
653
654 select @sorder_chset_id = @value
655 if @configvalue2 is not NULL
656 begin
657 /*
658 ** Get default sortorder id from name and
659 ** validate the sortord id.
660 */
661 select @value = id
662 from master..syscharsets
663 where name = @configvalue2
664 and type between 2000 and 2999
665
666 if @value is null
667 begin
668 /* 18134, "The sortorder, '%1!', is invalid since it
669 ** is not defined in Syscharsets."
670 */
671 raiserror 18134, @configvalue2
672 return (1)
673 end
674 end
675 end
676
677 /*
678 ** If an attempt to enable a disk mirroring is made, and
679 ** if this happens to be a server with HA services turned
680 ** on, we disallow. Currently we do not support ASE HA
681 ** services along with sybase mirroring.
682 */
683 if (@confignum = 140 and @configvalue = 0)
684 begin
685 select @cmpstate = @@cmpstate
686 if @cmpstate >= 0
687 begin
688 /* 18816 Mirroring not allowed in ASE HA */
689 raiserror 18816
690 return (1)
691 end
692 end
693
694 /*
695 ** If an attempt to disable disk mirroring is being made,
696 ** ensure that there are no devices that are currently
697 ** being mirrored.
698 */
699 else
700 if (@confignum = 140 and @configvalue = 1)
701 begin
702 if (select count(*) from master.dbo.sysdevices
703 where status & 512 = 512) > 0
704 begin
705
706 /* 18750, Unable to disable disk mirroring
707 ** because some devices are currently
708 ** mirrored. Use 'disk unmirror' to
709 ** unmirror these devices and then
710 ** re-run this sp_configure command.
711 */
712
713 raiserror 18570
714 return (1)
715 end
716 end
717
718 /*
719 ** If this is the number of current audit table we want
720 ** to make sure that if "with truncate" option is not
721 ** provided new table is empty other wise fail.
722 */
723 else
724 if @confignum = 260
725 begin
726 if @configvalue2 is not NULL
727 begin
728 if (@configvalue2 not in ("with truncate",
729 "default"))
730 begin
731 /*
732 ** 18549, "Invalid third argument
733 ** supplied: '%1!'. Valid
734 ** choices are 'with truncate'
735 ** or 'default'."
736 */
737 raiserror 18549, @configvalue2
738 return (1)
739 end
740 end
741 else
742 begin
743 select @value = 2
744 end
745 end
746
747 /*
748 ** global cache partition number
749 */
750 else
751 if @confignum = 337
752 and @configvalue is not NULL
753 begin
754 select @rec_state = @@recovery_state
755 if (@rec_state not like "NOT_IN_RECOVERY%")
756 begin
757 raiserror 19106
758 return (1)
759 end
760
761 /*
762 ** Partition number must be a power of 2
763 ** between 1 and 128.
764 */
765 if (@configvalue not between 1 and 128)
766 or (@configvalue & (@configvalue - 1) != 0)
767 begin
768 raiserror 18611
769 return (1)
770 end
771 end
772
773 /*
774 ** If this is to change the number of maximum concurrently
775 ** recovered dbs, check to make sure that the server is
776 ** not currently in the tuning process. We don't allow
777 ** change to this config parameter if the server is in
778 ** tuning process.
779 ** If the server is still in recovery process, the only value
780 ** that the config parameter may be changed to is 1, which is
781 ** to change back to do serial recovery.
782 */
783 else
784 if @confignum = 415
785 begin
786 select @rec_state = @@recovery_state
787 if (@rec_state like "RECOVERY_TUNING%")
788 begin
789 raiserror 19107
790 return (1)
791 end
792 else if (@rec_state not like "NOT_IN_RECOVERY%" and
793 @configvalue != 1)
794 begin
795 raiserror 19108, @configvalue
796 return (1)
797 end
798
799 end
800
801 /*
802 ** If the config parameter is "global async prefetch limit",
803 ** do not allow this change if server is in recovery process.
804 */
805 else
806 if @confignum = 303
807 begin
808 select @rec_state = @@recovery_state
809 if (@rec_state not like "NOT_IN_RECOVERY%")
810 begin
811 raiserror 19109
812 return (1)
813 end
814 end
815
816 /*
817 ** If configure parameter is "enable real time messaging",
818 ** check @configvalue2
819 **
820 ** - null means enable for all supported messaging
821 ** on that platform.
822 ** - 'TIB_JMS' means enable/disable for TIBJMS only.
823 ** - 'IBM_MQ' means enable/disable for IBM MQ only.
824 ** - 'EAS_JMS' means enable/disable for EASJMS only.
825 ** - 'SONICMQ_JMS' means enable for SONICMQ_JMS only.
826 **
827 ** @configvalue will be changed to a bit mask.
828 */
829 if (@value != 3) and (@confignum = 429)
830 begin
831 if (@configvalue2 is not null)
832 begin
833 /* Get the class number */
834 select @new_class = number
835 from master.dbo.spt_values
836 where lower(name) = lower(@configvalue2)
837 and type = 'X'
838
839 /* Unrecognized provider class */
840 if @@rowcount = 0
841 begin
842 raiserror 19416, @configvalue2
843 return (1)
844 end
845
846 /* Not a supported provider class */
847 if (@new_class != 12) and (@new_class != 13)
848 and (@new_class != 14) and (@new_class != 15)
849 begin
850 raiserror 19416, @configvalue2
851 return (1)
852 end
853 end
854
855 /* Get the rtms provider mask */
856 select @cfg_rtms_all_mask = number
857 from master.dbo.spt_values
858 where lower(name) = 'all providers'
859 and type = 'RT'
860
861 select @all_rtms_provider_set = sum(number)
862 from master.dbo.spt_values
863 where type = 'RT'
864 and low = 1
865 select @all_rtms_provider_set = @all_rtms_provider_set
866 + @cfg_rtms_all_mask
867
868 if (@configvalue2 is not null)
869 begin
870 select @cfg_rtms_provider_mask = number
871 from master.dbo.spt_values
872 where lower(name) = lower(@configvalue2)
873 and type = 'RT'
874 and low = 1
875 /* Not a supported RTMS provider */
876 if (@cfg_rtms_provider_mask is null)
877 begin
878 raiserror 19416, @configvalue2
879 return (1)
880 end
881 end
882 else if (@configvalue != 0)
883 begin
884 select @cfg_rtms_provider_mask = @cfg_rtms_all_mask
885 end
886 else
887 begin
888 select @cfg_rtms_provider_mask = @all_rtms_provider_set
889 end
890 end
891
892 /*
893 ** Raise warning messages if abstract plan
894 ** dump/load/replace, literal autoparam or
895 ** statement cache are already on when
896 ** compatibility mode is being enabled.
897 */
898 if (@confignum = 502 and @configvalue = 1)
899 begin
900 /* Raise warning message 19966 */
901 exec sp_getmessage 19966, @msg output
902 print @msg
903
904 /*
905 ** Raise warning message 19961 if abstract
906 ** plan dump/load/replace is already on.
907 */
908 if exists (select *
909 from master.dbo.sysconfigures
910 where config in (383, 384, 385)
911 and value = 1)
912 begin
913 exec sp_getmessage 19961, @msg output
914 print @msg
915 end
916
917 /*
918 ** Raise warning message 19967 if
919 ** statement cache is already on.
920 */
921 select @config_value_to_check = value
922 from master.dbo.sysconfigures
923 where config = 414
924
925 if (@config_value_to_check is not NULL
926 and @config_value_to_check != 0)
927 begin
928 exec sp_getmessage 19967, @msg output
929 print @msg, @config_value_to_check
930
931 /*
932 ** Raise warning message 19962 if
933 ** literal autoparam is already on.
934 */
935 if exists (select *
936 from master.dbo.sysconfigures
937 where config = 462
938 and value = 1)
939 begin
940 exec sp_getmessage 19962, @msg output
941 print @msg
942 end
943 end
944
945 /*
946 ** Raise warning message if the value of
947 ** histogram tuning factor is not 1, the
948 ** default value in ASE 12.5.
949 */
950 select @config_value_to_check = value
951 from master.dbo.sysconfigures
952 where config = 433
953
954 if (@config_value_to_check is not NULL
955 and @config_value_to_check != 1)
956 begin
957 exec sp_getmessage 19965, @msg output
958 print @msg, @config_value_to_check
959 end
960
961 end
962
963 /*
964 ** Raise warning messages if compatibility mode
965 ** is already on when abstract plan dump/load/replace
966 ** or literal autoparam is being enabled.
967 */
968 if exists (select *
969 from master.dbo.sysconfigures
970 where config = 502 and value = 1)
971 begin
972 /*
973 ** Check if abstract plan dump/load/replace
974 ** is being enabled.
975 */
976 if ((@confignum = 383 or
977 @confignum = 384 or
978 @confignum = 385) and
979 @configvalue = 1)
980 begin
981 exec sp_getmessage 19961, @msg output
982 print @msg
983 end
984
985 /*
986 ** Check if literal autoparam is being enabled.
987 ** Note that literal autoparam itself is not
988 ** a problem but the combination of both
989 ** statement cache and literal autoparam will
990 ** have some effect.
991 */
992 if (@confignum = 462 and @configvalue = 1)
993 begin
994 /* if statement cache is also on */
995 if exists (select *
996 from master.dbo.sysconfigures
997 where config = 414
998 and value != 0)
999 begin
1000 exec sp_getmessage 19962, @msg output
1001 print @msg
1002 end
1003 end
1004
1005 /*
1006 ** Check if statement cache is being enabled
1007 ** because we may have literal autoparam
1008 ** enabled already. In that case, we need to
1009 ** raise warning message for literal autoparam
1010 ** as above too.
1011 */
1012 if (@confignum = 414 and @configvalue != 0)
1013 begin
1014 /* if literal autoparam is already on */
1015 if exists (select *
1016 from master.dbo.sysconfigures
1017 where config = 462
1018 and value = 1)
1019 begin
1020 exec sp_getmessage 19962, @msg output
1021 print @msg
1022 end
1023 end
1024 end
1025
1026 /* get @logical_memory */
1027 select @lmemconfignum = config
1028 from master.dbo.sysconfigures
1029 where name = 'total logical memory'
1030
1031
1032 select @logical_memory = value
1033 from master.dbo.syscurconfigs
1034 where config = @lmemconfignum
1035
1036
1037 select @unit = unit
1038 from master.dbo.syscurconfigs
1039 where config = @confignum
1040
1041
1042 /* optimizer level */
1043 if (@confignum = 507)
1044 begin
1045 select @configvalue = number from master.dbo.spt_values
1046 where type = 'OL' and name = @configvalue2
1047 end
1048 /*
1049 ** If configure value is 0, looking for the value
1050 ** in configvalue2. As "default character set id" and
1051 ** "default sortorder_id" can be changed together, if
1052 ** @configvalue is 0 in this case, we will get syntax
1053 ** error. So for such parameters where @unit is "id",
1054 ** we will not check value of configvalue2.
1055 */
1056 if (@configvalue = 0
1057 and @configvalue2 is not NULL
1058 and @confignum != 507
1059 and @configvalue2 not in ("default", "read", "write", "restore",
1060 "verify", "with truncate"
1061
1062 )
1063 and @unit not in ("name", "not applicable", "switch")
1064 and (@unit != "id" or @confignum in (124, 168)))
1065 begin
1066 /* convert "pPkKmMgG" to equivalent "k" units */
1067 exec @status = sp_aux_getsize @configvalue2, @bvalue output
1068
1069 if @status = 0
1070 begin
1071 /* Invalid syntax */
1072 return (1)
1073 end
1074
1075 /* sp_aux_getsize returns value in K unit. */
1076 /*
1077 ** If we are updating max memory, do the conversion
1078 ** to 2k-pages ourselves
1079 */
1080 if @confignum = 396
1081 begin
1082 select @configvalue = @bvalue / 2
1083 end
1084 else
1085 begin
1086 select @bvalue = @bvalue * 1024
1087
1088 /*
1089 ** normalize it according to its unit and put
1090 ** back to @configvalue.
1091 */
1092 select @configvalue = config_admin(20,
1093 @confignum, @bvalue, 0, NULL, NULL)
1094
1095 end
1096 end
1097
1098 /*
1099 ** If this is the number of default language, we want
1100 ** to make sure that the new value is a valid language
1101 ** ID in Syslanguages.
1102 */
1103 if @confignum = 124
1104 begin
1105 if not exists (select *
1106 from master.dbo.syslanguages
1107 where langid = @configvalue)
1108 begin
1109 /* 0 is default language, us_english */
1110 if @configvalue != 0
1111 begin
1112 /* 17414, "You can't set the default
1113 ** language to a language ID that is
1114 ** not defined in Syslanguages."
1115 */
1116 raiserror 17414
1117 return (1)
1118 end
1119 end
1120 end
1121
1122
1123 /*
1124 ** If this is the number of devices configuration
1125 ** parameter, we want to make sure that it's not being
1126 ** set to lower than the number of devices in sysdevices.
1127 */
1128 if @confignum = 116
1129 begin
1130 /*
1131 ** Get the number of devices.
1132 */
1133 select @numdevices = count(*)
1134 from master.dbo.sysdevices
1135 where status & 2 = 2
1136
1137 if (@configvalue < @numdevices)
1138 begin
1139 /* 17413, "The value of the 'number of
1140 ** devices' must not be less than the number
1141 ** of active devices '%1!'
1142 */
1143 raiserror 17413, @numdevices
1144 return (1)
1145 end
1146 end
1147
1148 /*
1149 ** Before changing the config value, if this parameter
1150 ** is "number of open databases", make sure it cannot
1151 ** be reduced during recovery.
1152 */
1153 if @confignum = 105
1154 begin
1155 select @rec_state = @@recovery_state
1156 if ((@rec_state not like "NOT_IN_RECOVERY%") and
1157 (@configvalue < @oldcfgvalue))
1158 begin
1159 raiserror 19114
1160 return (1)
1161 end
1162 end
1163
1164 /*
1165 ** Now we're done checking for @configvalue2 = "default", so
1166 ** we can modify it. If this is a char param and @configvalue2
1167 ** is "default", reset it to be the actual default.
1168 */
1169 if @configvalue2 = "default"
1170 and @oldcfgvalue_char is not null
1171 begin
1172 select @configvalue2 = @defvalue
1173 end
1174
1175 /*
1176 ** Check for parameter with integer and character datatype
1177 ** that if the new and old values for the configuration
1178 ** parameter are same, then the value of configuration
1179 ** parameter will not be changed.
1180 */
1181 if ((@oldcfgvalue_char is NULL AND @configvalue = @oldcfgvalue)
1182 OR (@oldcfgvalue_char is not NULL AND @configvalue2 = @oldcfgvalue_char))
1183 begin
1184 /*
1185 ** Now the old and new values for the first parameter
1186 ** are same. But as "default sortorder id" and "default
1187 ** character set id" both can be changed in sp_configure
1188 ** together, check if the first parameter in sp_configure
1189 ** is one of these and if the new and old values for the
1190 ** second parameter are same, then value of both the
1191 ** parameters should not be changed. The condition
1192 ** mentioned below will always be true for other
1193 ** configuration parameters or if only one configuration
1194 ** parameter is given in sp_configure.
1195 */
1196
1197 if (@confignum not in (123, 131)) OR
1198 (@value = @sorder_chset_id)
1199 begin
1200 /* Set the @is_equal flag to true */
1201 select @is_equal = 1
1202
1203 end
1204
1205 end
1206
1207
1208
1209 /*
1210 **If for 'enable real time messaging',
1211 **We need to check the bitmap
1212 */
1213 if (@value != 3) and (@confignum = 429)
1214 begin
1215 if (@oldcfgvalue = @cfg_rtms_all_mask)
1216 select @tmp_rtms_value = @all_rtms_provider_set
1217 else
1218 select @tmp_rtms_value = @oldcfgvalue
1219
1220 select @tmp_rtms_value = @tmp_rtms_value & @cfg_rtms_provider_mask
1221 /*
1222 ** If @configvalue != 0, it means to enable RTDS.
1223 ** Set it as 1
1224 */
1225 if (@configvalue != 0) and (@configvalue != 1)
1226 select @configvalue = 1
1227
1228 if (@configvalue != 0) and (@tmp_rtms_value != 0)
1229 select @is_equal = 1
1230 else if (@configvalue = 0) and (@tmp_rtms_value = 0)
1231 select @is_equal = 1
1232 else
1233 select @is_equal = 0
1234
1235 if (@is_equal = 1)
1236 select @configvalue = @oldcfgvalue
1237 end
1238
1239
1240
1241
1242 /*
1243 ** If the new and old values are not the same, call
1244 ** config_admin() to set the new value. Otherwise just
1245 ** set @status = 1 (success).
1246 */
1247 if (@confignum != 543)
1248 begin
1249 select @status = case
1250 when (@is_equal = 1) then 1
1251 else config_admin(@cmd, @confignum,
1252 @configvalue, @value,
1253
1254 NULL,
1255
1256 @configvalue2) end
1257 end
1258 else
1259 begin
1260 if (@is_equal = 1)
1261 begin
1262 select @status = 1
1263 end
1264 else
1265 begin
1266 /*
1267 ** If the config option is 'enable functionality group',
1268 ** do not dump the new config file at this point.
1269 */
1270 select @cmd = 23
1271
1272 /*
1273 ** For each config option in group "Application
1274 ** Functionality", call config_admin() to turn
1275 ** each feature ON/OFF.
1276 */
1277 declare appgroup_cursor cursor for
1278 select config from master.dbo.sysconfigures
1279 where parent = 49
1280 and config != 543
1281
1282 open appgroup_cursor
1283 fetch appgroup_cursor into @app_config
1284
1285 while (@@sqlstatus = 0)
1286 begin
1287 select @status = config_admin(@cmd, @app_config,
1288 @configvalue,
1289 @value,
1290
1291 NULL,
1292
1293 @configvalue2)
1294
1295 fetch appgroup_cursor into @app_config
1296 end
1297
1298 close appgroup_cursor
1299 deallocate cursor appgroup_cursor
1300
1301 /*
1302 ** We have configured each feature in 'Application
1303 ** Functionality' group, now configure 'enable
1304 ** functionality group' with @cmd set to 1 so that
1305 ** we will dump a new config file.
1306 */
1307 select @cmd = 1
1308 select @status = config_admin(@cmd, @confignum,
1309 @configvalue, @value,
1310
1311 NULL,
1312
1313 @configvalue2)
1314 end
1315 end
1316
1317 /* if successful */
1318 if (@status = 1)
1319 begin
1320 if (@confignum = 507)
1321 begin
1322 /* default value display data */
1323 if (@configvalue = 999999)
1324 select @optlevel_curr = @optlevel_def,
1325 @optlevel_run = @optlevel_def
1326 else
1327 select @optlevel_curr = @configvalue2,
1328 @optlevel_run = @configvalue2
1329 end
1330
1331 /* Display the new value */
1332 select "Parameter Name" = convert(char(30), name),
1333 "Default" = CASE WHEN b.config = 507 THEN @optlevel_def
1334 ELSE convert(char(11), space(11 - char_length(
1335 convert(varchar(11), defvalue))) +
1336 convert(varchar(11), defvalue))
1337 END,
1338 "Memory Used" = convert(char(11), space(11 - char_length(
1339 convert(varchar(11), c.comment))) +
1340 convert(varchar(11), c.comment)),
1341 "Config Value" = CASE WHEN b.config = 507 THEN @optlevel_curr
1342 ELSE convert(char(12), space(12 - char_length(
1343 isnull(b.value2, convert(char(32), b.value)))) +
1344 isnull(b.value2, convert(char(32), b.value)))
1345 END,
1346 "Run Value" = CASE WHEN b.config = 507 THEN @optlevel_run
1347 ELSE convert(char(12), space(12 - char_length(
1348 isnull(c.value2, convert(char(32), c.value)))) +
1349 isnull(c.value2, convert(char(32), c.value)))
1350 END,
1351 "Unit" = convert(char(20), c.unit),
1352 "Type" = convert(char(20), c.type)
1353
1354 from master.dbo.sysconfigures b,
1355 master.dbo.syscurconfigs c
1356 where
1357 b.config = @confignum and
1358
1359 b.config *= c.config
1360
1361
1362 /*
1363 ** If the configuration value and run value are equal
1364 ** then display a message that there will not be any
1365 ** change in configuration parameter and exit.
1366 */
1367
1368 if (@is_equal = 1)
1369 begin
1370 if (@confignum = 507)
1371 begin
1372 select @configvalue2 = @optlevel_curr,
1373 @oldcfgvalue_char = @optlevel_curr
1374 end
1375 /*
1376 ** 18932, Resulting configuration value
1377 ** and memory use have not changed from
1378 ** previous values: new configuration
1379 ** value %1!, previous configuration %2!
1380 */
1381 exec sp_getmessage 18932, @msg output
1382 /* for integer datatype. */
1383 if (@oldcfgvalue_char is NULL)
1384 begin
1385 print @msg, @configvalue, @oldcfgvalue
1386 end
1387 else
1388 begin
1389 /* for character datatype. */
1390 print @msg, @configvalue2, @oldcfgvalue_char
1391 end
1392 return (0)
1393 end
1394
1395 /*
1396 ** print reboot message if this option is not
1397 ** dynamic.
1398 */
1399 if ((@sysstatus & 8) = 8)
1400 begin
1401 exec sp_getmessage 17419, @msg output
1402 print @msg
1403 end
1404 else
1405 begin
1406 exec sp_getmessage 18123, @msg output
1407 print @msg
1408 end
1409 /*
1410 ** Lets us calculate @additional_memory i.e. the
1411 ** increase in 'total logical memory'
1412 */
1413 select @additional_memory =
1414 (value - @logical_memory) * 2
1415 from master.dbo.syscurconfigs
1416 where config = @lmemconfignum
1417
1418
1419 /*
1420 ** print additional memory message
1421 */
1422 if (@additional_memory > 0)
1423 begin
1424 /*
1425 ** 18916, Changing the value of '%1!' to '%2!'
1426 ** increases the amount of memory ASE uses
1427 ** by %3! K.
1428 */
1429 if (@unit != "name")
1430 begin
1431 exec sp_getmessage 18916, @msg output
1432 print @msg, @fullconfigname,
1433 @configvalue, @additional_memory
1434 end
1435 else
1436 begin
1437 /*
1438 ** For configure options with name
1439 ** as their units, they use
1440 ** configvalue2 (type of varchar)
1441 ** to specify the intended value
1442 ** insteading of configvalue (type
1443 ** of int).
1444 */
1445 exec sp_getmessage 18916, @msg output
1446 print @msg, @fullconfigname,
1447 @configvalue2, @additional_memory
1448 end
1449 end
1450 else if (@additional_memory < 0)
1451 begin
1452 /*
1453 ** 18917, Changing the value of '%1!' to '%2!'
1454 ** reduces the amount of memory ASE uses
1455 ** by %3! K. The reduced memory may be
1456 ** reused when this configure value changes,
1457 ** but will not be released until ASE restarts.
1458 */
1459 select @additional_memory = @additional_memory * - 1
1460 if (@unit != "name")
1461 begin
1462 exec sp_getmessage 18917, @msg output
1463 print @msg, @fullconfigname,
1464 @configvalue, @additional_memory
1465 end
1466 else
1467 begin
1468 exec sp_getmessage 18917, @msg output
1469 print @msg, @fullconfigname,
1470 @configvalue2, @additional_memory
1471 end
1472 end
1473 else
1474 begin
1475 /*
1476 ** 18397, Changing the value of '%1!'
1477 ** does not increase the amount of
1478 ** memory Adaptive Server uses.
1479 */
1480 exec sp_getmessage 18397, @msg output
1481 print @msg, @fullconfigname
1482 end
1483
1484 return (0)
1485 end
1486 else
1487 return (1)
1488 end
1489
1490 end
1491
1492 /*
1493 ** @configcount=0 implies @configname is not valid
1494 ** @configname=NULL implies displaying all the parameters except for
1495 ** the parameters with the config number equal to 19 or the parent equal
1496 ** to 19 since those parameters are displayed by sp_cacheconfig.
1497 */
1498 if (@configcount = 0)
1499 begin
1500 /* 18124, "No matching configuration options.
1501 ** Here is a listing of groups:"
1502 */
1503 raiserror 18124
1504 select distinct convert(char(50), name)
1505 from master.dbo.sysconfigures
1506 where config < 100
1507 and parent != 19
1508 and config != 19
1509 order by name
1510 return (1)
1511 end
1512 else if (@configname is NULL and @non_default_options is NULL)
1513 begin
1514
1515 select @configname = "Config"
1516
1517 end
1518
1519 /*
1520 ** retrieve the display level from sysattributes
1521 */
1522 select @user_displaylevel = int_value from master.dbo.sysattributes where
1523 class = 4 AND
1524 attribute = 0 AND
1525 object_type = 'L' AND
1526 object = suser_id()
1527
1528 /*
1529 ** set the default display level to 10 if it is not defined in sysattributes
1530 */
1531 if (@user_displaylevel is NULL)
1532 select @user_displaylevel = 10
1533
1534 /*
1535 ** If non_default_options is set, display only nondefault settings depending
1536 ** on current display level and return
1537 */
1538 if (@non_default_options = 1)
1539 begin
1540 /* Display all config parameters for specified instance. */
1541 if @configvalue3 is not NULL
1542 begin
1543 if @configvalue3 != @@instancename
1544 begin
1545 raiserror 19519, @configvalue3
1546 return (1)
1547 end
1548 else
1549 begin
1550 select Parameter_Name = convert(char(30), name),
1551 Default_Value = convert(varchar(11), defvalue),
1552 Memory_Used = convert(varchar(11), c.comment),
1553 Config_Value = isnull(b.value2,
1554 convert(char(32), b.value)),
1555 Run_Value = isnull(c.value2,
1556 convert(char(32), c.value)),
1557 Unit = convert(char(20), c.unit),
1558 Type_ = convert(char(10), c.type)
1559
1560 into #temptable1
1561 from master.dbo.sysconfigures b,
1562 master.dbo.syscurconfigs c
1563 where
1564
1565 b.config = c.config
1566 and (c.defvalue != isnull(b.value2,
1567 convert(char(32), b.value))
1568 or c.defvalue != isnull(c.value2,
1569 convert(char(32), c.value)))
1570 and c.config != 114
1571 /* Exclude option 'configuration file' */
1572 and c.type != "read-only"
1573 and display_level <= @user_displaylevel
1574
1575 exec sp_autoformat #temptable1,
1576 @selectlist = "'Parameter Name' = Parameter_Name,
1577 'Default'= Default_Value,'Memory Used' = Memory_Used,
1578 'Config Value '= Config_Value,'Run Value'= Run_Value,
1579
1580 'Unit' = Unit,'Type'= Type_",
1581
1582 @orderby = "order by Parameter_Name"
1583 return (0)
1584 end
1585 end
1586
1587 if @@system_view = "instance"
1588 begin
1589 /*
1590 ** Display the information of the config parameter
1591 ** for the current instance as well as the cluster-wide
1592 ** one.
1593 */
1594 select Parameter_Name = convert(char(30), name),
1595 Default_Value = convert(varchar(11), defvalue),
1596 Memory_Used = convert(varchar(11), c.comment),
1597 Config_Value = isnull(b.value2, convert(char(32), b.value)),
1598 Run_Value = isnull(c.value2, convert(char(32), c.value)),
1599 Unit = convert(char(20), c.unit),
1600 Type_ = convert(char(10), c.type)
1601
1602 into #temptable3
1603 from master.dbo.sysconfigures b,
1604 master.dbo.syscurconfigs c
1605 where
1606
1607 b.config = c.config
1608 and (c.defvalue != isnull(b.value2, convert(char(32), b.value))
1609 or c.defvalue != isnull(c.value2, convert(char(32), c.value)))
1610 and c.config != 114
1611 /* Exclude option 'configuration file' */
1612 and c.type != "read-only"
1613 and display_level <= @user_displaylevel
1614
1615 exec sp_autoformat #temptable3,
1616 @selectlist = "'Parameter Name' = Parameter_Name,
1617 'Default'= Default_Value,'Memory Used' = Memory_Used,
1618 'Config Value '= Config_Value,'Run Value'= Run_Value,
1619
1620 'Unit' = Unit,'Type'= Type_",
1621
1622 @orderby = "order by Parameter_Name"
1623 end
1624 else
1625 begin
1626 /*
1627 ** When system_view is set to 'cluster', we need to
1628 ** display the config info across all instances. This
1629 ** includes two part: one is the cluster-wide config
1630 ** setting whose instanceid is NULL; the other
1631 ** part is the info for each instance who has a valid
1632 ** instanceid.
1633 */
1634 select distinct
1635 Parameter_Name = convert(char(30), name),
1636 Default_Value = convert(varchar(11), defvalue),
1637 Memory_Used = convert(varchar(11), c.comment),
1638 Config_Value = isnull(b.value2, convert(char(32), b.value)),
1639 Run_Value = isnull(c.value2, convert(char(32), c.value)),
1640 Unit = convert(char(20), c.unit),
1641 Type_ = convert(char(10), c.type)
1642
1643 into #temptable
1644 from master.dbo.sysconfigures b, master.dbo.syscurconfigs c
1645 where
1646
1647 b.config = c.config
1648 and (c.defvalue != isnull(b.value2,
1649 convert(char(32), b.value))
1650 or c.defvalue != isnull(c.value2,
1651 convert(char(32), c.value)))
1652 and c.config != 114
1653 /* Exclude option 'configuration file' */
1654 and c.type != "read-only"
1655 and display_level <= @user_displaylevel
1656
1657
1658
1659 exec sp_autoformat #temptable,
1660 @selectlist = "'Parameter Name' = Parameter_Name,
1661 'Default'= Default_Value,'Memory Used' = Memory_Used,
1662 'Config Value '= Config_Value,'Run Value'= Run_Value,
1663
1664 'Unit' = Unit,'Type'=Type_",
1665
1666 @orderby = "order by Parameter_Name"
1667 end
1668 return (0)
1669
1670 end
1671
1672 /*
1673 ** If @use_wildcard = 0 and the default sortorder is case-insensitive
1674 ** dictionary sort order, use exact match: name = @configname to get row,
1675 ** otherwise use wildcard match: name like "%" + @configname + "%".
1676 */
1677
1678 if (@use_wildcard = 0 and @nocase = 1)
1679 begin
1680 select @confignum = config,
1681 @parent = config,
1682 @sysname = name,
1683 @sysstatus = status
1684 from master.dbo.sysconfigures
1685 where name = @configname
1686 and config != 19
1687 end
1688 else
1689 begin
1690 select @confignum = config,
1691 @parent = config,
1692 @sysname = name,
1693 @sysstatus = status
1694 from master.dbo.sysconfigures
1695 where name like "%" + @configname + "%"
1696 and config != 19
1697 end
1698
1699 select @children = count(*)
1700 from master.dbo.sysconfigures
1701 where parent = @confignum
1702
1703 if @children = 0
1704 begin
1705 /* @@nestlevel is problem area if a sproc calls sp_configure */
1706 /* could pass in another param when recursing */
1707 if @@nestlevel > 1
1708 begin
1709 /* reached a leaf, notify parent */
1710 return (1)
1711 end
1712 else
1713 begin
1714
1715 begin
1716 /* Display the information of the config parameter
1717 ** for the current server as well as the cluster-wide
1718 ** one. Instanceid is NULL meaning this is the
1719 ** cluster-wide configuration.
1720 */
1721 select "Parameter Name" = convert(char(30), name),
1722 "Default" = CASE WHEN b.config = 507 THEN @optlevel_def
1723 ELSE convert(char(11), space(11 - char_length(
1724 convert(varchar(11), defvalue))) +
1725 convert(varchar(11), defvalue))
1726 END,
1727 "Memory Used" = convert(char(11), space(11 - char_length(
1728 convert(varchar(11), c.comment))) +
1729 convert(varchar(11), c.comment)),
1730 "Config Value" = CASE WHEN b.config = 507 THEN @optlevel_curr
1731 ELSE convert(char(12), space(12 - char_length(
1732 isnull(b.value2, convert(char(32), b.value)))) +
1733 isnull(b.value2, convert(char(32), b.value)))
1734 END,
1735 "Run Value" = CASE WHEN b.config = 507 THEN @optlevel_run
1736 ELSE convert(char(12), space(12 - char_length(
1737 isnull(c.value2, convert(char(32), c.value)))) +
1738 isnull(c.value2, convert(char(32), c.value)))
1739 END,
1740 "Unit" = convert(char(20), c.unit),
1741 "Type" = convert(char(20), c.type)
1742
1743 from master.dbo.sysconfigures b,
1744 master.dbo.syscurconfigs c
1745 where
1746
1747 b.config *= c.config
1748 and name like "%" + @configname + "%"
1749 and b.config != 19
1750 and parent != 19
1751 end
1752
1753 end
1754
1755 return (0)
1756 end
1757 else
1758 begin
1759 select @msg = "Group: " + @sysname
1760 print ""
1761 print @msg
1762 print ""
1763
1764 /* this poor guy has kids, so recurse to leaves */
1765 declare config_curs cursor for
1766 select config, name, parent
1767 from master.dbo.sysconfigures
1768 where parent = @parent
1769 order by name
1770
1771 open config_curs
1772
1773 fetch config_curs into @sysconfig, @sysname, @sysparent
1774
1775 while (@@sqlstatus = 0)
1776 begin
1777
1778 execute @status = sp_configure @sysname
1779
1780 if (@status = 1)
1781 begin
1782 /*
1783 ** this guy has leaves as kids,
1784 ** so print out the leaves with
1785 ** display level <= @user_displaylevel
1786 ** Note: If a config parameter has more than one
1787 ** parent, the extra parents are stored in
1788 ** 'sysattribures'.
1789 */
1790 create table #configure_temp(config int)
1791
1792 insert into #configure_temp
1793 select distinct a.config
1794 from master.dbo.sysconfigures a,
1795 master.dbo.syscurconfigs b
1796 where
1797 display_level <= @user_displaylevel
1798 and parent = @parent
1799 and a.config != 19
1800 and a.config = b.config
1801
1802 union
1803 select distinct config
1804 from master.dbo.syscurconfigs,
1805 master.dbo.sysattributes
1806 where
1807 display_level <= @user_displaylevel
1808 and class = 4
1809 and attribute = 1
1810 and object_type = 'CP'
1811 and int_value = @parent
1812 and object = config
1813 and config != 19
1814
1815
1816 if exists (select * from #configure_temp)
1817 begin
1818
1819 /*
1820 ** If system_view is 'instance', display
1821 ** the config info for the current instance
1822 ** as well as the cluster-wide one.
1823 */
1824
1825 begin
1826 select
1827 "Parameter Name" = convert(char(30), name),
1828 "Default" = CASE WHEN b.config = 507
1829 THEN @optlevel_def
1830 ELSE convert(char(11),
1831 space(11 - char_length(
1832 convert(varchar(11), defvalue))) +
1833 convert(varchar(11), defvalue))
1834 END,
1835 "Memory Used" =
1836 convert(char(11),
1837 space(11 - char_length(
1838 convert(varchar(11), c.comment))) +
1839 convert(varchar(11), c.comment)),
1840 "Config Value" = CASE WHEN b.config = 507
1841 THEN @optlevel_curr
1842 ELSE convert(char(12),
1843 space(12 - char_length(
1844 isnull(b.value2,
1845 convert(char(32), b.value)))) +
1846 isnull(b.value2,
1847 convert(char(32), b.value)))
1848 END,
1849 "Run Value" = CASE WHEN b.config = 507
1850 THEN @optlevel_run
1851 ELSE convert(char(12),
1852 space(12 - char_length(
1853 isnull(c.value2,
1854 convert(char(32), c.value)))) +
1855 isnull(c.value2,
1856 convert(char(32), c.value)))
1857 END,
1858 "Unit" = convert(char(20), c.unit),
1859 "Type" = convert(char(20), c.type)
1860
1861 from master.dbo.sysconfigures b,
1862 master.dbo.syscurconfigs c
1863 where b.config in
1864 (select config
1865 from #configure_temp)
1866 and b.config = c.config
1867
1868 order by name
1869 end
1870
1871 end
1872
1873 drop table #configure_temp
1874
1875 close config_curs
1876 deallocate cursor config_curs
1877
1878 return (0)
1879 end
1880 else
1881 begin
1882 /*
1883 ** this lucky guy has grandkids, so, continue
1884 */
1885 fetch config_curs into
1886 @sysconfig, @sysname, @sysparent
1887
1888 end
1889 end
1890
1891 close config_curs
1892 deallocate cursor config_curs
1893
1894 return (0)
1895 end
1896
1897
exec sp_procxmode 'sp_configure', 'AnyMode'
go
Grant Execute on sp_configure to public
go
DEFECTS |
QJWI 5 Join or Sarg Without Index |
279 |
QJWI 5 Join or Sarg Without Index |
430 |
QJWI 5 Join or Sarg Without Index |
507 |
QJWI 5 Join or Sarg Without Index |
525 |
QJWI 5 Join or Sarg Without Index |
1359 |
QJWI 5 Join or Sarg Without Index |
1747 |
MEST 4 Empty String will be replaced by Single Space |
325 |
MEST 4 Empty String will be replaced by Single Space |
327 |
MEST 4 Empty String will be replaced by Single Space |
400 |
MEST 4 Empty String will be replaced by Single Space |
1760 |
MEST 4 Empty String will be replaced by Single Space |
1762 |
MINU 4 Unique Index with nullable columns master..sysattributes |
master..sysattributes |
MINU 4 Unique Index with nullable columns master..sysconfigures |
master..sysconfigures |
MINU 4 Unique Index with nullable columns master..syslanguages |
master..syslanguages |
MTYP 4 Assignment type mismatch @optlevel_def: varchar(20) = varchar(28) |
222 |
MTYP 4 Assignment type mismatch @optlevel_run: varchar(20) = varchar(28) |
227 |
MTYP 4 Assignment type mismatch @optlevel_curr: varchar(20) = varchar(28) |
232 |
MTYP 4 Assignment type mismatch @rec_state: varchar(30) = int |
754 |
MTYP 4 Assignment type mismatch @rec_state: varchar(30) = int |
786 |
MTYP 4 Assignment type mismatch @rec_state: varchar(30) = int |
808 |
MTYP 4 Assignment type mismatch @new_class: smallint = int |
834 |
MTYP 4 Assignment type mismatch @size_str: varchar(30) = varchar(255) |
1067 |
MTYP 4 Assignment type mismatch @rec_state: varchar(30) = int |
1155 |
MTYP 4 Assignment type mismatch @optlevel_curr: varchar(20) = varchar(255) |
1327 |
MTYP 4 Assignment type mismatch @optlevel_run: varchar(20) = varchar(255) |
1328 |
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
328 |
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
1575 |
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
1615 |
MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 |
1659 |
QCSC 4 Costly 'select count()', use 'exists()' |
702 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} |
219 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} |
836 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} |
858 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} |
863 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} |
872 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type} |
1046 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered (name, parent, config) Intersection: {parent} |
1701 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered (name, parent, config) Intersection: {parent} |
1768 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object} Uncovered: [object_info1, object_info2, object_info3, object_cinfo] |
1812 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
224 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
229 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
234 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
248 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
258 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
265 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
280 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
281 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
305 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
319 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
351 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
371 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
403 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
411 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
416 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
432 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
433 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
474 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
475 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
505 |
QTYP 4 Comparison type mismatch smallint = int |
505 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
523 |
QTYP 4 Comparison type mismatch smallint = int |
523 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
547 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
621 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
652 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
923 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
937 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
952 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
970 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
997 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1017 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1034 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1039 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1107 |
QTYP 4 Comparison type mismatch smallint = int |
1107 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1279 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1280 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1333 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1341 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1346 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1357 |
QTYP 4 Comparison type mismatch smallint = int |
1357 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1416 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1506 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1507 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1508 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1523 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1524 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1570 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1610 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1652 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1686 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1696 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1701 |
QTYP 4 Comparison type mismatch smallint = int |
1701 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1722 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1730 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1735 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1749 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1750 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1768 |
QTYP 4 Comparison type mismatch smallint = int |
1768 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1798 |
QTYP 4 Comparison type mismatch smallint = int |
1798 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1799 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1808 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1809 |
QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
1812 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1813 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1828 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1840 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
1849 |
TNOI 4 Table with no index master..syscurconfigs |
master..syscurconfigs |
TNOU 4 Table with no unique index master..spt_values |
master..spt_values |
CUNU 3 Cursor not updated: cursor should contain 'for read only' clause appgroup_cursor |
1278 |
CUNU 3 Cursor not updated: cursor should contain 'for read only' clause config_curs |
1766 |
MGTP 3 Grant to public master..spt_values |
|
MGTP 3 Grant to public master..sysattributes |
|
MGTP 3 Grant to public master..syscharsets |
|
MGTP 3 Grant to public master..sysconfigures |
|
MGTP 3 Grant to public master..syscurconfigs |
|
MGTP 3 Grant to public master..sysdevices |
|
MGTP 3 Grant to public master..syslanguages |
|
MGTP 3 Grant to public sybsystemprocs..sp_configure |
|
MLCH 3 Char type with length>30 char(32) |
262 |
MLCH 3 Char type with length>30 char(32) |
263 |
MLCH 3 Char type with length>30 char(32) |
268 |
MLCH 3 Char type with length>30 char(32) |
269 |
MLCH 3 Char type with length>30 char(32) |
413 |
MLCH 3 Char type with length>30 char(32) |
414 |
MLCH 3 Char type with length>30 char(32) |
418 |
MLCH 3 Char type with length>30 char(32) |
419 |
MLCH 3 Char type with length>30 char(32) |
1343 |
MLCH 3 Char type with length>30 char(32) |
1344 |
MLCH 3 Char type with length>30 char(32) |
1348 |
MLCH 3 Char type with length>30 char(32) |
1349 |
MLCH 3 Char type with length>30 char(50) |
1504 |
MLCH 3 Char type with length>30 char(32) |
1554 |
MLCH 3 Char type with length>30 char(32) |
1556 |
MLCH 3 Char type with length>30 char(32) |
1567 |
MLCH 3 Char type with length>30 char(32) |
1569 |
MLCH 3 Char type with length>30 char(32) |
1597 |
MLCH 3 Char type with length>30 char(32) |
1598 |
MLCH 3 Char type with length>30 char(32) |
1608 |
MLCH 3 Char type with length>30 char(32) |
1609 |
MLCH 3 Char type with length>30 char(32) |
1638 |
MLCH 3 Char type with length>30 char(32) |
1639 |
MLCH 3 Char type with length>30 char(32) |
1649 |
MLCH 3 Char type with length>30 char(32) |
1651 |
MLCH 3 Char type with length>30 char(32) |
1732 |
MLCH 3 Char type with length>30 char(32) |
1733 |
MLCH 3 Char type with length>30 char(32) |
1737 |
MLCH 3 Char type with length>30 char(32) |
1738 |
MLCH 3 Char type with length>30 char(32) |
1845 |
MLCH 3 Char type with length>30 char(32) |
1847 |
MLCH 3 Char type with length>30 char(32) |
1854 |
MLCH 3 Char type with length>30 char(32) |
1856 |
MNER 3 No Error Check should check @@error after select into |
218 |
MNER 3 No Error Check should check @@error after select into |
315 |
MNER 3 No Error Check should check @@error after insert |
321 |
MNER 3 No Error Check should check return value of exec |
328 |
MNER 3 No Error Check should check return value of exec |
456 |
MNER 3 No Error Check should check return value of exec |
901 |
MNER 3 No Error Check should check return value of exec |
913 |
MNER 3 No Error Check should check return value of exec |
928 |
MNER 3 No Error Check should check return value of exec |
940 |
MNER 3 No Error Check should check return value of exec |
957 |
MNER 3 No Error Check should check return value of exec |
981 |
MNER 3 No Error Check should check return value of exec |
1000 |
MNER 3 No Error Check should check return value of exec |
1020 |
MNER 3 No Error Check should check return value of exec |
1381 |
MNER 3 No Error Check should check return value of exec |
1401 |
MNER 3 No Error Check should check return value of exec |
1406 |
MNER 3 No Error Check should check return value of exec |
1431 |
MNER 3 No Error Check should check return value of exec |
1445 |
MNER 3 No Error Check should check return value of exec |
1462 |
MNER 3 No Error Check should check return value of exec |
1468 |
MNER 3 No Error Check should check return value of exec |
1480 |
MNER 3 No Error Check should check @@error after select into |
1550 |
MNER 3 No Error Check should check return value of exec |
1575 |
MNER 3 No Error Check should check @@error after select into |
1594 |
MNER 3 No Error Check should check return value of exec |
1615 |
MNER 3 No Error Check should check @@error after select into |
1634 |
MNER 3 No Error Check should check return value of exec |
1659 |
MNER 3 No Error Check should check @@error after insert |
1792 |
MUCO 3 Useless Code Useless Brackets |
185 |
MUCO 3 Useless Code Useless Brackets |
207 |
MUCO 3 Useless Code Useless Brackets |
242 |
MUCO 3 Useless Code Useless Brackets |
284 |
MUCO 3 Useless Code Useless Brackets |
293 |
MUCO 3 Useless Code Useless Brackets |
329 |
MUCO 3 Useless Code Useless Brackets |
360 |
MUCO 3 Useless Code Useless Brackets |
374 |
MUCO 3 Useless Code Useless Brackets |
444 |
MUCO 3 Useless Code Useless Brackets |
459 |
MUCO 3 Useless Code Useless Brackets |
478 |
MUCO 3 Useless Code Useless Brackets |
486 |
MUCO 3 Useless Code Useless Brackets |
530 |
MUCO 3 Useless Code Useless Brackets |
543 |
MUCO 3 Useless Code Useless Brackets |
576 |
MUCO 3 Useless Code Useless Brackets |
582 |
MUCO 3 Useless Code Useless Brackets |
586 |
MUCO 3 Useless Code Useless Brackets |
592 |
MUCO 3 Useless Code Useless Brackets |
594 |
MUCO 3 Useless Code Useless Brackets |
598 |
MUCO 3 Useless Code Useless Brackets |
599 |
MUCO 3 Useless Code Useless Brackets |
600 |
MUCO 3 Useless Code Useless Brackets |
608 |
MUCO 3 Useless Code Useless Brackets |
610 |
MUCO 3 Useless Code Useless Brackets |
613 |
MUCO 3 Useless Code Useless Brackets |
641 |
MUCO 3 Useless Code Useless Brackets |
672 |
MUCO 3 Useless Code Useless Brackets |
683 |
MUCO 3 Useless Code Useless Brackets |
690 |
MUCO 3 Useless Code Useless Brackets |
700 |
MUCO 3 Useless Code Useless Brackets |
714 |
MUCO 3 Useless Code Useless Brackets |
728 |
MUCO 3 Useless Code Useless Brackets |
738 |
MUCO 3 Useless Code Useless Brackets |
755 |
MUCO 3 Useless Code Useless Brackets |
758 |
MUCO 3 Useless Code Useless Brackets |
769 |
MUCO 3 Useless Code Useless Brackets |
787 |
MUCO 3 Useless Code Useless Brackets |
790 |
MUCO 3 Useless Code Useless Brackets |
792 |
MUCO 3 Useless Code Useless Brackets |
796 |
MUCO 3 Useless Code Useless Brackets |
809 |
MUCO 3 Useless Code Useless Brackets |
812 |
MUCO 3 Useless Code Useless Brackets |
831 |
MUCO 3 Useless Code Useless Brackets |
843 |
MUCO 3 Useless Code Useless Brackets |
851 |
MUCO 3 Useless Code Useless Brackets |
868 |
MUCO 3 Useless Code Useless Brackets |
876 |
MUCO 3 Useless Code Useless Brackets |
879 |
MUCO 3 Useless Code Useless Brackets |
882 |
MUCO 3 Useless Code Useless Brackets |
898 |
MUCO 3 Useless Code Useless Brackets |
925 |
MUCO 3 Useless Code Useless Brackets |
954 |
MUCO 3 Useless Code Useless Brackets |
976 |
MUCO 3 Useless Code Useless Brackets |
992 |
MUCO 3 Useless Code Useless Brackets |
1012 |
MUCO 3 Useless Code Useless Brackets |
1043 |
MUCO 3 Useless Code Useless Brackets |
1056 |
MUCO 3 Useless Code Useless Brackets |
1072 |
MUCO 3 Useless Code Useless Brackets |
1117 |
MUCO 3 Useless Code Useless Brackets |
1137 |
MUCO 3 Useless Code Useless Brackets |
1144 |
MUCO 3 Useless Code Useless Brackets |
1156 |
MUCO 3 Useless Code Useless Brackets |
1160 |
MUCO 3 Useless Code Useless Brackets |
1181 |
MUCO 3 Useless Code Useless Brackets |
1215 |
MUCO 3 Useless Code Useless Brackets |
1235 |
MUCO 3 Useless Code Useless Brackets |
1247 |
MUCO 3 Useless Code Useless Brackets |
1250 |
MUCO 3 Useless Code Useless Brackets |
1260 |
MUCO 3 Useless Code Useless Brackets |
1285 |
MUCO 3 Useless Code Useless Brackets |
1318 |
MUCO 3 Useless Code Useless Brackets |
1320 |
MUCO 3 Useless Code Useless Brackets |
1323 |
MUCO 3 Useless Code Useless Brackets |
1368 |
MUCO 3 Useless Code Useless Brackets |
1370 |
MUCO 3 Useless Code Useless Brackets |
1383 |
MUCO 3 Useless Code Useless Brackets |
1392 |
MUCO 3 Useless Code Useless Brackets |
1399 |
MUCO 3 Useless Code Useless Brackets |
1422 |
MUCO 3 Useless Code Useless Brackets |
1429 |
MUCO 3 Useless Code Useless Brackets |
1450 |
MUCO 3 Useless Code Useless Brackets |
1460 |
MUCO 3 Useless Code Useless Brackets |
1484 |
MUCO 3 Useless Code Useless Brackets |
1487 |
MUCO 3 Useless Code Useless Brackets |
1498 |
MUCO 3 Useless Code Useless Brackets |
1510 |
MUCO 3 Useless Code Useless Brackets |
1512 |
MUCO 3 Useless Code Useless Brackets |
1531 |
MUCO 3 Useless Code Useless Brackets |
1538 |
MUCO 3 Useless Code Useless Brackets |
1546 |
MUCO 3 Useless Code Useless Brackets |
1583 |
MUCO 3 Useless Code Useless Brackets |
1668 |
MUCO 3 Useless Code Useless Brackets |
1678 |
MUCO 3 Useless Code Useless Brackets |
1710 |
MUCO 3 Useless Code Useless Brackets |
1755 |
MUCO 3 Useless Code Useless Brackets |
1775 |
MUCO 3 Useless Code Useless Brackets |
1780 |
MUCO 3 Useless Code Useless Brackets |
1878 |
MUCO 3 Useless Code Useless Brackets |
1894 |
MUIN 3 Column created using implicit nullability |
1790 |
QAFM 3 Var Assignment from potentially many rows |
222 |
QAFM 3 Var Assignment from potentially many rows |
227 |
QAFM 3 Var Assignment from potentially many rows |
232 |
QAFM 3 Var Assignment from potentially many rows |
469 |
QAFM 3 Var Assignment from potentially many rows |
499 |
QAFM 3 Var Assignment from potentially many rows |
518 |
QAFM 3 Var Assignment from potentially many rows |
545 |
QAFM 3 Var Assignment from potentially many rows |
619 |
QAFM 3 Var Assignment from potentially many rows |
650 |
QAFM 3 Var Assignment from potentially many rows |
834 |
QAFM 3 Var Assignment from potentially many rows |
856 |
QAFM 3 Var Assignment from potentially many rows |
870 |
QAFM 3 Var Assignment from potentially many rows |
921 |
QAFM 3 Var Assignment from potentially many rows |
950 |
QAFM 3 Var Assignment from potentially many rows |
1027 |
QAFM 3 Var Assignment from potentially many rows |
1032 |
QAFM 3 Var Assignment from potentially many rows |
1037 |
QAFM 3 Var Assignment from potentially many rows |
1045 |
QAFM 3 Var Assignment from potentially many rows |
1413 |
QAFM 3 Var Assignment from potentially many rows |
1522 |
QAFM 3 Var Assignment from potentially many rows |
1680 |
QAFM 3 Var Assignment from potentially many rows |
1690 |
QCRS 3 Conditional Result Set |
247 |
QCRS 3 Conditional Result Set |
402 |
QCRS 3 Conditional Result Set |
1332 |
QCRS 3 Conditional Result Set |
1504 |
QCRS 3 Conditional Result Set |
1721 |
QCRS 3 Conditional Result Set |
1826 |
QCTC 3 Conditional Table Creation |
315 |
QCTC 3 Conditional Table Creation |
1550 |
QCTC 3 Conditional Table Creation |
1594 |
QCTC 3 Conditional Table Creation |
1634 |
QCTC 3 Conditional Table Creation |
1790 |
QDIS 3 Check correct use of 'select distinct' |
1634 |
QDIS 3 Check correct use of 'select distinct' |
1793 |
QDIS 3 Check correct use of 'select distinct' |
1803 |
QGWO 3 Group by/Distinct/Union without order by |
1634 |
QGWO 3 Group by/Distinct/Union without order by |
1793 |
QGWO 3 Group by/Distinct/Union without order by |
1803 |
QISO 3 Set isolation level |
212 |
QJWT 3 Join or Sarg Without Index on temp table |
225 |
QJWT 3 Join or Sarg Without Index on temp table |
230 |
QJWT 3 Join or Sarg Without Index on temp table |
235 |
QJWT 3 Join or Sarg Without Index on temp table |
1863 |
QNAJ 3 Not using ANSI Inner Join |
223 |
QNAJ 3 Not using ANSI Inner Join |
228 |
QNAJ 3 Not using ANSI Inner Join |
233 |
QNAJ 3 Not using ANSI Inner Join |
448 |
QNAJ 3 Not using ANSI Inner Join |
1561 |
QNAJ 3 Not using ANSI Inner Join |
1603 |
QNAJ 3 Not using ANSI Inner Join |
1644 |
QNAJ 3 Not using ANSI Inner Join |
1794 |
QNAJ 3 Not using ANSI Inner Join |
1804 |
QNAJ 3 Not using ANSI Inner Join |
1861 |
QNAM 3 Select expression has no name convert(char(50), name) |
1504 |
QNAO 3 Not using ANSI Outer Join |
275 |
QNAO 3 Not using ANSI Outer Join |
426 |
QNAO 3 Not using ANSI Outer Join |
503 |
QNAO 3 Not using ANSI Outer Join |
521 |
QNAO 3 Not using ANSI Outer Join |
1354 |
QNAO 3 Not using ANSI Outer Join |
1743 |
QNUA 3 Should use Alias: Column name should use alias #optlevel |
222 |
QNUA 3 Should use Alias: Table #optlevel |
223 |
QNUA 3 Should use Alias: Table master..syscurconfigs |
223 |
QNUA 3 Should use Alias: Column config should use alias syscurconfigs |
224 |
QNUA 3 Should use Alias: Column defvalue should use alias syscurconfigs |
225 |
QNUA 3 Should use Alias: Column number should use alias #optlevel |
225 |
QNUA 3 Should use Alias: Column name should use alias #optlevel |
227 |
QNUA 3 Should use Alias: Table #optlevel |
228 |
QNUA 3 Should use Alias: Table master..syscurconfigs |
228 |
QNUA 3 Should use Alias: Column config should use alias syscurconfigs |
229 |
QNUA 3 Should use Alias: Column number should use alias #optlevel |
230 |
QNUA 3 Should use Alias: Column value2 should use alias syscurconfigs |
230 |
QNUA 3 Should use Alias: Column name should use alias #optlevel |
232 |
QNUA 3 Should use Alias: Table #optlevel |
233 |
QNUA 3 Should use Alias: Table master..syscurconfigs |
233 |
QNUA 3 Should use Alias: Column config should use alias syscurconfigs |
234 |
QNUA 3 Should use Alias: Column number should use alias #optlevel |
235 |
QNUA 3 Should use Alias: Column value should use alias syscurconfigs |
235 |
QNUA 3 Should use Alias: Column name should use alias b |
247 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
251 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
252 |
QNUA 3 Should use Alias: Column parent should use alias b |
281 |
QNUA 3 Should use Alias: Column name should use alias a |
402 |
QNUA 3 Should use Alias: Column defvalue should use alias b |
405 |
QNUA 3 Should use Alias: Column defvalue should use alias b |
406 |
QNUA 3 Should use Alias: Column name should use alias a |
431 |
QNUA 3 Should use Alias: Column parent should use alias a |
432 |
QNUA 3 Should use Alias: Column name should use alias b |
1332 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
1335 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
1336 |
QNUA 3 Should use Alias: Column name should use alias b |
1550 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
1551 |
QNUA 3 Should use Alias: Column display_level should use alias c |
1573 |
QNUA 3 Should use Alias: Column name should use alias b |
1594 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
1595 |
QNUA 3 Should use Alias: Column display_level should use alias c |
1613 |
QNUA 3 Should use Alias: Column name should use alias b |
1635 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
1636 |
QNUA 3 Should use Alias: Column display_level should use alias c |
1655 |
QNUA 3 Should use Alias: Column name should use alias b |
1721 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
1724 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
1725 |
QNUA 3 Should use Alias: Column name should use alias b |
1748 |
QNUA 3 Should use Alias: Column parent should use alias b |
1750 |
QNUA 3 Should use Alias: Column display_level should use alias b |
1797 |
QNUA 3 Should use Alias: Column parent should use alias a |
1798 |
QNUA 3 Should use Alias: Column config should use alias syscurconfigs |
1803 |
QNUA 3 Should use Alias: Table master..syscurconfigs |
1804 |
QNUA 3 Should use Alias: Table master..sysattributes |
1805 |
QNUA 3 Should use Alias: Column display_level should use alias syscurconfigs |
1807 |
QNUA 3 Should use Alias: Column class should use alias sysattributes |
1808 |
QNUA 3 Should use Alias: Column attribute should use alias sysattributes |
1809 |
QNUA 3 Should use Alias: Column object_type should use alias sysattributes |
1810 |
QNUA 3 Should use Alias: Column int_value should use alias sysattributes |
1811 |
QNUA 3 Should use Alias: Column config should use alias syscurconfigs |
1812 |
QNUA 3 Should use Alias: Column object should use alias sysattributes |
1812 |
QNUA 3 Should use Alias: Column config should use alias syscurconfigs |
1813 |
QNUA 3 Should use Alias: Column name should use alias b |
1827 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
1832 |
QNUA 3 Should use Alias: Column defvalue should use alias c |
1833 |
QNUA 3 Should use Alias: Table #configure_temp |
1865 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered (name, parent, config) Intersection: {name, parent} |
304 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered (name, parent, config) Intersection: {name, parent} |
318 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered (name, parent, config) Intersection: {name, parent} |
350 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered (name, parent, config) Intersection: {name, parent} |
370 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered (name, parent, config) Intersection: {name} |
450 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysconfigures.csysconfigures unique clustered (name, parent, config) Intersection: {name} |
1029 |
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} |
1523 |
QSWV 3 Sarg with variable @confignum, Candidate Index: sysconfigures.nc2sysconfigures(config) F |
505 |
QSWV 3 Sarg with variable @confignum, Candidate Index: sysconfigures.nc2sysconfigures(config) F |
523 |
QSWV 3 Sarg with variable @confignum, Candidate Index: sysconfigures.nc2sysconfigures(config) F |
1357 |
QSWV 3 Sarg with variable @parent, Candidate Index: sysconfigures.nc1sysconfigures(parent, config) F |
1798 |
QUNI 3 Check Use of 'union' vs 'union all' |
1793 |
VNRD 3 Variable is not read @whichone |
192 |
VNRD 3 Variable is not read @use_cluster |
201 |
VNRD 3 Variable is not read @instanceid |
202 |
VNRD 3 Variable is not read @sysstatus |
1693 |
VUNU 3 Variable is not used @configcount2 |
143 |
VUNU 3 Variable is not used @configcount3 |
144 |
VUNU 3 Variable is not used @match_count2 |
145 |
VUNU 3 Variable is not used @remote_instance_name |
150 |
VUNU 3 Variable is not used @remote_instance_id |
152 |
VUNU 3 Variable is not used @retstat |
154 |
VUNU 3 Variable is not used @sqlbuf |
155 |
CRDO 2 Read Only Cursor Marker (has an 'order by' clause) |
1766 |
CUPD 2 Updatable Cursor Marker (updatable by default) |
1278 |
MRST 2 Result Set Marker |
247 |
MRST 2 Result Set Marker |
402 |
MRST 2 Result Set Marker |
1332 |
MRST 2 Result Set Marker |
1504 |
MRST 2 Result Set Marker |
1721 |
MRST 2 Result Set Marker |
1826 |
MSUB 2 Subquery Marker |
702 |
MSUB 2 Subquery Marker |
908 |
MSUB 2 Subquery Marker |
935 |
MSUB 2 Subquery Marker |
968 |
MSUB 2 Subquery Marker |
995 |
MSUB 2 Subquery Marker |
1015 |
MSUB 2 Subquery Marker |
1105 |
MTR1 2 Metrics: Comments Ratio Comments: 38% |
88 |
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 161 = 198dec - 39exi + 2 |
88 |
MTR3 2 Metrics: Query Complexity Complexity: 762 |
88 |
PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 |
446 |
PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 |
1550 |
PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 |
1594 |
PRED_QUERY_COLLECTION 2 {c=master..sysconfigures, c2=master..syscurconfigs} 0 |
1634 |
PRED_QUERY_COLLECTION 2 {c=master..syscurconfigs, c2=master..sysconfigures} 0 |
1793 |
PRED_QUERY_COLLECTION 2 {a=master..sysattributes, c=master..syscurconfigs} 0 |
1803 |