Suppose that I wish to run the following

/* Check for any pending configurations, just in case. */
SELECT *
FROM sys.configurations
WHERE value  value_in_use;
GO
 
EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO

Is there any risk, such as clearing the plan cache or blocking queries, to running this during a busy day? I know that the remote admin connection is safe. I'm concerned about sp_configure.

If you look at https://dba.stackexchange.com/q/150863 there is a way to save SQL Server settings before enabling them, so you can disable them if needs be. Here is the code (partially copied):

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;

CREATE TABLE #Settings
(
    Setting VARCHAR(100),
    Val INT
)
INSERT #Settings (Setting, Val)
SELECT 'show advanced options', cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
UNION
SELECT 'xp_cmdshell', cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
UNION
SELECT 'Ad Hoc Distributed Queries', cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'

SELECT * FROM #Settings;

That works fine, if you run one procedure at a time; however, it writes to the SQL Server error log as you can see below: Is there a way to avoid writing changes of settings to the error log?

We are upgrading our test environment with a new server and updated version of Microsoft SQL Server and have run into an issue. On the new server, our old code will get "operation is not allowed when the object is closed" when executing some stored procedures. This message never appeared on the old server. When we tracked it down, the issue can be resolved by adding SET NOCOUNT ON; to the stored procedure. I looked at the defaults on the database and saw no settings that were different (SQL Server 2008 vs SQL Server 2014) related to defaults. What setting should I be looking at to resolve this globally without needing to add SET NOCOUNT ON to a thousand stored procs?

when I run the script below all at once I get this error message: > Msg 15281, Level 16, State 1, Line 58 SQL Server blocked access to > STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed > Queries' because this component is turned off as part of the security > configuration for this server. A system administrator can enable the > use of 'Ad Hoc Distributed Queries' by using sp_configure. For more > information about enabling 'Ad Hoc Distributed Queries', search for > 'Ad Hoc Distributed Queries' in SQL Server Books Online. But when I run this first and then run the full script then it is all fine. exec sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure The impression that I got is that sql-server will look at the configured values in memory, and does not seem to realise that they have changed. How can I alter this behaviour? here is the full script: SET NOCOUNT ON; declare @prevAdvancedOptions int declare @prevXpCmdshell int declare @adhocdistque int ------------------------------------------------------------------------------------------- --SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' -- because this component is turned off as part of the security configuration for this server. --A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. --For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options' select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell' select @adhocdistque = cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries' PRINT @prevAdvancedOptions PRINT @prevXpCmdshell print @adhocdistque if (@prevAdvancedOptions = 0) begin exec sp_configure 'show advanced options', 1 reconfigure end if (@prevXpCmdshell = 0) begin exec sp_configure 'xp_cmdshell', 1 reconfigure end if (@adhocdistque = 0) begin exec sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure end /* ----------------------------------------------------------------- do work - begin */ SELECT * FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes' , 'set fmtonly off exec msdb.dbo.sp_help_job') --where name in -- ( 'WebFeed UKProductOffer Offers' -- ,'WebFeed USProductOffer Offers' -- ,'WebFeed DEProductOffer Offers' -- ,'WebFeed ATProductOffer Offers' -- ,'WebFeed FRProductOffer Offers' -- ,'WebFeed EUProductOffer Offers' -- ,'WebFeed AUProductOffer Offers') /* ----------------------------------------------------------------- do work - end */ --------------------------------------------------- -- restore the settings as they were previously --------------------------------------------------- if (@prevXpCmdshell = 0) begin exec sp_configure 'xp_cmdshell', 0 reconfigure end if (@prevAdvancedOptions = 0) begin exec sp_configure 'show advanced options', 0 reconfigure end if (@adhocdistque = 0) begin exec sp_configure 'Ad Hoc Distributed Queries', 0 reconfigure end

Today I got an error ( DTSER_FAILURE ) but I could fix it by editing the maintenance plan ( this error is due to the maintenance plan searching for a database that was deleted, and I was using "selected databases" in the setup ). But over the internet, I read some posts saying that I should SP_CONFIGURE 'ALLOW UPDATES',0. What effects has this option? I read on the Microsoft page that this option has no effect and it is deprecated.