{"id":9744,"date":"2020-08-29T07:42:22","date_gmt":"2020-08-28T21:42:22","guid":{"rendered":"https:\/\/database.guide\/?p=9744"},"modified":"2020-08-29T07:42:25","modified_gmt":"2020-08-28T21:42:25","slug":"show-advanced-server-configuration-options-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/show-advanced-server-configuration-options-in-sql-server-t-sql\/","title":{"rendered":"Show Advanced Server Configuration Options in SQL Server (T-SQL)"},"content":{"rendered":"\n<p>If you&#8217;re trying to check an advanced configuration option in <a href=\"https:\/\/database.guide\/what-is-sql-server\/\" title=\"What is SQL Server?\">SQL Server<\/a>, but you get an error, you might find the following helpful. <\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>For example, if when running the following code to check the Database Mail XPs configuration values:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXEC sp_configure 'Database Mail XPs'<\/code><\/pre>\n\n\n\n<p>You get the following error:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62\nThe configuration option 'Database Mail XPs' does not exist, or it may be an advanced option.<\/pre>\n\n\n\n<p>You need to set the <code>show advanced options<\/code> server configuration option to <code>1<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Set <code>show advanced options<\/code> to <code>1<\/code><\/h2>\n\n\n\n<p>Here&#8217;s how to set <code>show advanced options<\/code> to <code>1<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sp_configure 'show advanced options', 1;  \nGO\nRECONFIGURE;  \nGO<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.\nCommands completed successfully.\nCommands completed successfully.<\/pre>\n\n\n\n<p>That&#8217;s all. You have now set <code>show advanced options<\/code> to <code>1<\/code>.<\/p>\n\n\n\n<p>The <code>RECONFIGURE<\/code> statement updates the value, and specifies that if the configuration setting does not require a server restart, the currently running value should be updated.<\/p>\n\n\n\n<p>Fortunately, the <code>show advanced options<\/code> doesn&#8217;t require a server restart.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Test the Result<\/h2>\n\n\n\n<p>Now I can successfully check the Database Mail XPs configuration values:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXEC sp_configure 'Database Mail XPs'<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+-------------------+-----------+-----------+----------------+-------------+\n| name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | minimum &nbsp; | maximum &nbsp; | config_value &nbsp; | run_value &nbsp; |\n|-------------------+-----------+-----------+----------------+-------------|\n| Database Mail XPs | 0 &nbsp; &nbsp; &nbsp; &nbsp; | 1 &nbsp; &nbsp; &nbsp; &nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |\n+-------------------+-----------+-----------+----------------+-------------+<\/pre>\n\n\n\n<p>In this case, the <code>Database Mail XPs<\/code> option is disabled (set to <code>0<\/code>). It would need to be set to <code>1<\/code> in order for it to be enabled.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Hide <code>show advanced options<\/code><\/h2>\n\n\n\n<p>Once you&#8217;ve done what you need to do, you might want to set <code>show advanced options<\/code> back to its default value of <code>0<\/code> (which hides the advanced options):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sp_configure 'show advanced options', 0;  \nGO\nRECONFIGURE;  \nGO<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Caution<\/h2>\n\n\n\n<p>Microsoft recommends that advanced options should be changed only by an experienced database administrator or certified SQL Server technician.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you&#8217;re trying to check an advanced configuration option in SQL Server, but you get an error, you might find the following helpful.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[10,77,104,61],"class_list":["post-9744","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-how-to","tag-mssql","tag-send-email","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/9744","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=9744"}],"version-history":[{"count":8,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/9744\/revisions"}],"predecessor-version":[{"id":9837,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/9744\/revisions\/9837"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=9744"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=9744"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=9744"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}