sp_configure: Preserve values of 'xp_cmdshell' and 'Show Advanced Options' I dont want to disturb the server values of xp_CMDShell &amp; Show Advanced options. The criteria is to use SQL to run a batch file.

This is the script i am trying to solve it with:

DECLARE @advanced_options int
DECLARE @xp_cmdshell int

--CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SELECT @advanced_options = value_in_use 
FROM sys.configurations
WHERE name = 'show advanced options'

SELECT @xp_cmdshell = value_in_use 
FROM sys.configurations
WHERE name = 'xp_cmdshell'

select @advanced_options as advOptions, @xp_cmdshell as cmdShell

exec sp_configure 'show advanced options', 1;
Reconfigure;

EXEC sp_configure 'xp_CMDShell', 1;
Reconfigure;

DECLARE @CMDSQL VARCHAR(1000)
SET @CMDSQL = 'F:\Adress\FillDBWithMSTabFiles'--Keep in mind, path needs to be correct.
EXEC master..xp_CMDShell @CMDSQL

EXEC sp_configure 'xp_CMDShell', @xp_cmdshell;
Reconfigure;

exec sp_configure 'show advanced options', @advanced_options;
Reconfigure;

select @advanced_options as advOptions, @xp_cmdshell as cmdShell


Results in error:

Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.
Msg 257, Level 16, State 3, Line 8
Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.
Error is at below select statements. If you do ALT+F1 on sys.confiugrations you'll notice that value_in_use column is of type sql_variant and you are trying to assign it to int variables. To fix this, use below statements in your script and it should work fine.

SELECT @advanced_options = cast(value_in_use as int)
FROM sys.configurations
WHERE name = 'show advanced options'

SELECT @xp_cmdshell = cast(value_in_use  as int)
FROM sys.configurations
WHERE name = 'xp_cmdshell'