Escape double quotes in command parameter-CodePudding

I need to run a PS command on SQL Server. Running a simple command without double-quotes works without a problem.

How to run below command?

DECLARE @sql nvarchar(4000);
 SET @sql = 
 '
 powershell.exe -command "
 $messageBody = "ds"
 Write-Host $messageBody
 "';
EXEC xp_cmdshell @sql;

I tried this ` and backslash but nothing seems to be working.

Running it from ps1 file is not an option.

CodePudding user response：

Instead of "ds", use ''ds'' to use single quotes withing the ps script to enclose the literal. Furthermore, use the call operator (&) withing the command parameter to call the script block:

SET @sql = 'powershell.exe -command "& { $messageBody = ''ds''; Write-Host $messageBody }';