Trouble executing SQLPS or PowerShell from T-SQL script using xp_cmdshell

Question

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?

"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
– Alex, Commented Jan 20, 2022 at 1: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
– Charlieface, Commented Jan 20, 2022 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
– Tom, Commented Jan 20, 2022 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
– Tom, Commented Jan 20, 2022 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
– Charlieface, Commented Jan 20, 2022 at 15:31

Tom · Accepted Answer · 2022-06-09 18:34:47Z

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

escape the " (double-quotes) with a \ (backslash)
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

answered Jun 9, 2022 at 18:34

Tom

313 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trouble executing SQLPS or PowerShell from T-SQL script using xp_cmdshell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related