0

I had originally created a set of scripts to extract the results from a system of dynamic queries to CSV using SQLCMD. However, due to large text fields in the datasets containing a large amount of formatting codes, the CSV files became 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. I can paste the same output code into SQLPS and it works fine using PowerShell.exe or SQLPS.exe.

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 work.

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

My requirements are to extract a standard CSV file, and unfortunately I am trying to leverage the large amount of T-SQL code I put together earlier in the project when working with SQLCMD. It will also need to be an automated process, which is why I was trying to leverage PowerShell or SQLPS. I am using SQL Server 2019 on a dev machine running Windows 10.

Any ideas on this one?

Tom
  • 31
  • 2
  • 1
    "Unfortunately, I am unable to get my extract CSV files to save when running the T-SQL file manually from SSMS" - what happens? Do you get any error messages? – Alex Jan 20 '22 at 01:29
  • `xp_cmdshell` can only execute batch `cmd` commands, not Powershell, unless you execute via `powershell.exe`. Why would you use T-SQL to execute a batch script to execute a Powershell script to execute SQL, why not just execute the Powershell directly? T-SQL is not a generalized scripting language, don't use it as such – Charlieface Jan 20 '22 at 14:07
  • @Alex I get no extract files created. If I take the same output from the Print command and paste in SQLPS window, the extract files get created without any problem. – Tom Jan 20 '22 at 14:44
  • @Charlieface This was an existing project started in T-SQL using SQLCMD. There is a large amount of T-SQL code that would need to be converted to PS. The bulk of the "heavy lifting" (upfront processing) is T-SQL. One option I have contemplated is saving to a PS file and then executing the PS file. Its just I would need to either output and run a bunch of PS files, or string the commands together so they can all run in one batch. For example, there are about 10 files created for each incident type and can be 10 to 20 types. – Tom Jan 20 '22 at 14:50
  • You could create a SQL Server Agent job (which can be in pure Powershell) then kick it off using `sp_start_job` – Charlieface Jan 20 '22 at 15:31

1 Answers1

0

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

Tom
  • 31
  • 2