DatabaseProcApplicationCreatedLinks
sybsystemprocssp_configure  31 Aug 14Defects Dependencies

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
RESULT SETS
sp_configure_rset_006
sp_configure_rset_005
sp_configure_rset_004
sp_configure_rset_003
sp_configure_rset_002
sp_configure_rset_001

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

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

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