SQL Server using xp_cmdshell to call powershell will not run using a variable powershell not recognized

Question

I am making a call from a stored procedure to powershell using xp_cmdshell. The string passed to xp_cmdshell is declared and made up of a concatenation of text and other variables. The t-sql code is: DECLARE @cmdstr VARCHAR(1000) SET @cmdstr = '''powershell.exe "' + @scriptdir + @scriptname + '" -MsgSubject "' + @MsgSubject + '" -MsgText "' + @MsgText + '" -MsgTo ' + @MsgTo + ' -MsgAttachments "' + @MsgAttachments + '"''' PRINT @cmdstrEXEC xp_cmdshell @cmdstr When running this I get the error ''powershell.exe' is not recognized as an internal or external command, operable program or batch file. If it take the actual string that is in the variable @cmdstr and feed it into xp_cmdshell it runs without issue. This is what it looks like. EXEC xp_cmdshell 'powershell.exe "C:\Scripts\Email_Send.ps1" -MsgSubject "VEW Extract 2023-04-20 13:14:23" -MsgText "VEW Extract file is attached" -MsgTo @email.com,@email.com -MsgAttachments "C:\Scripts\Extract\VEWExtract.csv"' So why would one execute correctly and the other not??

Answer 1

Hi @David Stark

Syntax: xp_cmdshell { 'command_string' } [ , no_output ]

command_string is varchar(8000) or nvarchar(4000), with no default.

''powershell.exe' is not recognized as an internal or external command, operable program or batch file.

This error mostly because xp_cmdshell cannot contain more than one set of double quotation marks. See: xp_cmdshell (Transact-SQL)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.