Global Configuration Settings
-
Comments posted to this topic are about the item Global Configuration Settings
-
Interesting question,but what would be the use of this ??
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Lesson is use the complete name. For example, rather than using "advanced options" use "show advanced options". If MS ever introduces "hide advanced options" then you may need to rewrite some code. Essentially, just be aware of how it works. Just an FYI. 🙂
Just curious, did you get it right or wrong?
-
Sean Smith-776614 (8/29/2013)
Lesson is use the complete name. For example, rather than using "advanced options" use "show advanced options". If MS ever introduces "hide advanced options" then you may need to rewrite some code. Essentially, just be aware of how it works. Just an FYI. 🙂Just curious, did you get it right or wrong?
Fair enough....
I got it right ... but just couldn't think of a really practically use but maybe its the head cold creeping....
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
As I said, it's just more of an FYI as to how it works.
-
Interesting question to close the week, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
This was removed by the editor as SPAM
-
Here is the code from sp_configure:
-- Use @configname and try to find the right option.-- If there isn't just one, print appropriate diagnostics and return.
select @configcount = count(*)
from sys.configurations
where lower(name) like '%' + @configname + '%'
and (is_advanced = 0 or @show_advance = 1)
-- If no option, print an error message.
if @configcount = 0
begin
raiserror (15123,-1,-1,@confignameIn)
return (1)
end
-- If more than one option like @configname, show the duplicates and return.
if @configcount > 1
begin
raiserror (15124,-1,-1,@confignameIn)
print ' '
select duplicate_options = name
from sys.configurations
where lower(name) like '%' + @configname + '%'
and (is_advanced = 0 or @show_advance = 1)
return (1)
end
else
-- There must be exactly one, so get the full name.
select @configname = name
from sys.configurations
where lower(name) like '%' + @configname + '%'
and (is_advanced = 0 or @show_advance = 1)
-
Here is sp_configure with a simple optimization:
-- Use @configname and try to find the right option.-- If there isn't just one, print appropriate diagnostics and return.
select @configcount = count(*)
,@confignameFullName = MAX(name)
from sys.configurations
where lower(name) like '%' + @configname + '%'
and (is_advanced = 0 or @show_advance = 1)
-- If no option, print an error message.
if @configcount = 0
begin
raiserror (15123,-1,-1,@confignameIn)
return (1)
end
-- If more than one option like @configname, show the duplicates and return.
if @configcount > 1
begin
raiserror (15124,-1,-1,@confignameIn)
print ' '
select duplicate_options = name
from sys.configurations
where lower(name) like '%' + @configname + '%'
and (is_advanced = 0 or @show_advance = 1)
return (1)
end
else
-- There must be exactly one, so get the full name.
SET @configname = @confignameFullName
/*
select @configname = name
from sys.configurations
where lower(name) like '%' + @configname + '%'
and (is_advanced = 0 or @show_advance = 1)
*/
-
"The SQL Server Database Engine recognizes any unique string that is part of the configuration name."
Wow, I did not know that. Good question, I always like it when I learn something new.
-
Interesting (although I can't imagine why it's been done that way).
It would have been nice if you had used one from the top of the list of options (maybe "uck") rather than the very last one, to save us having to trawl the entire list to see if any of them contained "dsh" (although with hindsight, it is a fairly obvious one).
-
Stewart "Arturius" Campbell (8/30/2013)
Interesting question, thanks Sean+1 🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!! -
Wow, did not expect that at all--why on earth would Microsoft make a system-level configuration procedure allow any old random garbage to work so long as it happens to be contained as a string in one of the real configuration names? :blink:
-
alex.d.garland (8/30/2013)
"The SQL Server Database Engine recognizes any unique string that is part of the configuration name."Wow, I did not know that. Good question, I always like it when I learn something new.
+1
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/ -
paul.knibbs (8/30/2013)
Wow, did not expect that at all--why on earth would Microsoft make a system-level configuration procedure allow any old random garbage to work so long as it happens to be contained as a string in one of the real configuration names? :blink:Probably because someone thought it would be useful to allow people to type only 'show advanced' (or even 'show adv') instead of the full 'show advanced options'. And just 'ad hoc' instead of 'optimize for ad hoc workloads'. And they never really considered the "less intuitive" uses such as in this question.
I do agree with Sean's warning though. It's not like you have to type commands like this many times per day, and I'd rather type the full option name and be safe from future surprises.
(BTW, I got the question correct, but only because I figured that there has to be a reason for this question. I had at first not even recogised dsh as a substring of the option name; to me it was just a random string of three letters).
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply