Sample Header Ad - 728x90

How to avoid writing to the error log when changing instance configuration?

0 votes
2 answers
116 views
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: enter image description here Is there a way to avoid writing changes of settings to the error log?
Asked by Marcello Miorelli (17274 rep)
Jan 22, 2025, 10:28 AM
Last activity: Jan 22, 2025, 04:11 PM