Unable to extract CSV from SSMS using SQLPS or PowerShell in T-SQL script with xp_cmdshell

Question

I had originally created a set of SQL scripts to extract CSV data w/ headers via a system of dynamic queries using SQLCMD. However, due to large text fields in the datasets the formatting codes made the CSV files unwieldly. I then switched to using SQLPS or PowerShell, which did a great job of formatting the output files. Unfortunately, I am unable to get my extract CSV files to save when running the T-SQL file manually from SSMS. Once this is working, I will also need to create a job for running this on an automated basis for some of our implementations, but at least need to be able to run manually. I can paste the same output code from the SSMS 'Messages' tab into the SQLPS command window, and it works fine using PowerShell.exe or SQLPS.exe creating the CSV's. However, the following code will not create the CSV files when run directly from within SSMS:

SET @Cmd = 'sqlps 
     $FromDate = "' + @FromDate + '";
     $ToDate = "' + @ToDate + '";
     $IncidentTypeName = "' + @IncidentTypeName + '";
     $DateField = "' + @DateField + '";
     $QueryType = "' + @QueryType + '";
     $PersonTypeID = "' + @PersonTypeID + '";
     $var = "FromDate=$FromDate", "ToDate=$ToDate", "IncidentTypeName=$IncidentTypeName", "DateField=$DateField", "QueryType=$QueryType", "PersonTypeID=$PersonTypeID";

invoke-sqlcmd -ServerInstance ' +@Server+ ' -Database ' +@Database+ ' -Username '+@Login+' -Password '+@Password + ' -QueryTimeout 0 ' +
    ' -InputFile "'+@SqlScriptFolder+@InputFileType+'" -Variable $var | export-csv -Delimiter "," -NoTypeInformation -Path "'+@ExtractFolder+@OutputFile+'.csv"'

print @Cmd
EXEC master..xp_cmdshell @Cmd

I checked file permissions on the extract folder, tried different options for SQLPS and PowerShell, and reviewed the execution in Process Monitor, but I cannot figure out why this will not create the CSV from within SSMS. There are no error messages. As much as I would like to switch completely to PowerShell, I am trying to leverage the large amount of T-SQL code I put together earlier in the project when when exporting to CSV using SQLCMD.

Any ideas much appreciated,
Tom

Answer 1

Well, after much research into my crazy process, which I should definitely rewrite to be a PowerShell script (and not use xp_cmdshell), I found the solution, which is to

1) escape the " (double-quotes) with a \ (backslash)
2) replace the carriage returns and newlines using SET @Cmd = REPLACE(REPLACE(@Cmd, NCHAR(13), N''), NCHAR(10), N' ');

from invoke-sqlcmd-doesnt-work-when-used-with-xp-cmdshell

Answer 2

Did you try capturing the output of the command into a text file and verified the result of the execution? Also, are you sure that running xp_cmdshell is even allowed?

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15

Answer 3

Why involve xp_cmdshell at all? Why not run the powershell script directly from a command-line window? And once you get it going through a Powershell job step?