Call Powershell command string from Sql Query Publish profile script

Question

Is there a way to call powershell in SSMS Sql query window? I know Command prompt can be invoked with xp_cmdshell. Does similar method for powershell exist ? (Note, I know about powershell window , but want to pass a script string). want to call powershell script in our Post Deployment script publish profile.

https://learn.microsoft.com/en-us/sql/powershell/run-windows-powershell-from-sql-server-management-studio?view=sql-server-2017

Answer 1

If you turn on SQLCMD mode (from the SSMS Query menu), you can use SQLCMD commands within an SSMS query window. A line beginning with !! will execute the text that follows as an OS command on the client. For example:

!! Powershell -Command "Write-Host ('This message is from PowerShell running on machine ' + $env:COMPUTERNAME)"

The command will similarly be executed when the script is run with the SQLCMD command-line utility with this technique.

Note that the "script string" mentioned in your question will need to be hard-coded in the T-SQL script.