question

manoj123 avatar image
manoj123 asked

unable to run powershell script from xp_cmdshell

Hi I am very new to powershell but when I run the below command in power shell it executes successfully and gives the required output but when I run the same command in xp_cmdshell it is not giving any output. declare @sql varchar(2000) set @sql= 'powershell.exe -c "$strComputer = Get-Content C:\Users\admin\Desktop\ servername.txt foreach ( $computer in $strComputer) { Get-WmiObject -Class win32_service -computername $computer | where {$_.name -like ''MSSQLServer'' -OR $_.name -like ''MSSQL$*'' -OR $_.name -like ''SQLAgent$*'' -OR $_.name -like ''SQLSERVERAGENT'' } | select-object Name,state,systemname,startmode,startname}"' print @sql EXEC xp_cmdshell @sql Can somebody help me on this ? I am stuck with this part. Thanks On advance
powershellxp_cmdshell
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
xp_cmdshell is not the same as PowerShell. xp_cmdshell is a way to get to the command prompt. You would then need to launch PowerShell from there in order to run a PowerShell script. Since you're setting this up to do monitoring of SQL Agent jobs, you can do one of two things, run it externally as a PowerShell script automated by some other process. Or, run it as a PowerShell command directly from Agent. You can create PowerShell jobs there that will let you run this script as is.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

manoj123 avatar image manoj123 commented ·
Thanks Fritchey for your responce but What I am trying to do in this block of code is just trying to run from query editor at this point . I am stuck is when I print the @sql and run the command got from print @sql it works fine in powershell but from Query Editor it run but not giving any output. What I am curious here is there anything I am missing. Command or sth ? Thanks
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Because if you run it from powershell, you're in powershell, but when you run it from the xp_cmdshell, you are not in powershell, you're at the disk operating system command line (DOS). They are two different things.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.