Passing variable into xp_cmdshell on SQL Server 2016 RRS feed

  • Question

  • User1151703306 posted

    Hi all, I need your help.

    I need passing variable into xp_cmdshell.

    This is my code :

    DECLARE @cmd VARCHAR (100)
    SET @cmd = 'D:\inetpub\wwwroot\myFile\myFile_' + CONVERT (VARCHAR, YEAR(GETDATE()), 23) + '.txt' PRINT @cmd

    EXEC master.dbo.sp_configure 'show advanced options',
    1 RECONFIGURE EXEC master.dbo.sp_configure 'xp_cmdshell',
    1 RECONFIGURE EXEC xp_cmdshell 'bcp "SELECT * FROM mytable WHERE YEAR (myDate) = YEAR (GETDATE());" queryout @cmd -T -c -t;'

    But I have error :

    Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

    How to do resolve this ?

    Please can you help me ?

    Thank you in advance for any help.

    Wednesday, January 22, 2020 10:21 AM