Executing xp_cmdshell to Generate a CSV -- Doesn't Like 'WHERE' Clause in Query-CodePudding

I have the following code:

USE MyFakeDB
GO

DECLARE @month VARCHAR(16) 
SET @month = 'October2021'
;

DECLARE @cmd VARCHAR(8000)
    , @sql NVARCHAR(max)
    , @return INT
    , @filepath VARCHAR(512)
    , @servername VARCHAR(255) = @@ServerName
    , @username VARCHAR(255) = 'me'
    , @password VARCHAR(255) = 'password1234'
    , @dbname VARCHAR(255) = 'MyFakeDB'
    , @tablename VARCHAR(255) = 'Monthly_Insert'
    , @folderpath VARCHAR(255) = 'E:\SomeData\Monthly'  
    , @filename VARCHAR(255) = ''  @month  '_Inserts.csv'
    , @datadate NVARCHAR(255) = 'WHERE DataMonth = '''  @month  ''''
;

SET @cmd = 'Invoke-Sqlcmd -Query ''SELECT Medium, RunDate, RunTime, UTCDate, UTCTime FROM ' @dbname '.dbo.' @tablename ' ' @datadate  ';'' -Database ' @dbname ' -Server "' @servername '" -Username ' @username ' -Password ' @password 
        ' | ConvertTo-Csv -NoTypeInformation | Set-Content -Path ' @folderpath '\' @filename ' -Encoding UTF8'
SET @cmd = 'powershell.exe -noprofile -command "' @cmd '"'
EXEC @return = xp_cmdshell @cmd, no_output
    IF @return <> 0
    BEGIN
        PRINT 'ERROR: ' @cmd
    END
;
GO

When I try running it, I get an

ERROR: powershell.exe -noprofile -command blah blah blah

error. If I remove the @datadate variable (which is effectively just the WHERE clause), it works just fine.

I've also tried to do it without a variable for the WHERE clause, and just writing the literal string and it still doesn't work, which leads me to believe it doesn't like to have a WHERE clause.

Any ideas why?

Thanks,

CodePudding user response：

Passing powershell scripts via the -command argument is notoriously tricky.

Instead use -command - which instructs Powershell to read the script from stdin, which you can provide in cmd.exe with the < redirection operator.

SET @cmd = 'powershell.exe -noprofile -command - < ' @cmd

And this also allows you to send multi-line powershell scripts, which are easier to read, eg

SET @cmd = 'Invoke-Sqlcmd -Query ''SELECT Medium, RunDate, RunTime, UTCDate, UTCTime FROM ' @dbname '.dbo.' @tablename ' ' @datadate  ';'' -Database ' @dbname ' -Server "' @servername '" -Username ' @username ' -Password ' @password  '
          | ConvertTo-Csv -NoTypeInformation 
          | Set-Content -Path ' @folderpath '\' @filename ' -Encoding UTF8'

And when debugging always print your cmd before running it, and don't suprress the output.

SET @cmd = 'powershell.exe -noprofile -command - < ' @cmd

print @cmd

EXEC @return = xp_cmdshell @cmd
IF @return <> 0
BEGIN
    PRINT 'ERROR: ' @cmd
END
;