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):
Is there a way to avoid writing changes of settings to the error log?
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:

Asked by Marcello Miorelli
(17274 rep)
Jan 22, 2025, 10:28 AM
Last activity: Jan 22, 2025, 04:11 PM
Last activity: Jan 22, 2025, 04:11 PM